sql求合计值(rollup函数应用) [数据库教程]

Z时代
2024-01-10
分类：综合

database

CREATETABLE `test1` (
  `id` varchar(11) DEFAULTNULL,
  `name` varchar(255) DEFAULTNULL,
  `count` varchar(255) DEFAULTNULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATETABLE `test2` (
  `id` varchar(11) DEFAULTNULL,
  `name` varchar(255) DEFAULTNULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- oracle、mysql通用
selectcasewhen t2.id =‘0‘then‘合计‘else t2.id end id, sum(count) countfrom test1 t1,test2 t2 
where t1.id = t2.name groupby t2.id;

-- mysql
selectcasewhen id isnullthen‘合计‘else id end id, sum(count) from test1 GROUPBY id with ROLLUP;
-- oracle
select id,sum(count) from test1 groupby rollup(id);