SQL产生总计

create table A( studentName varchar(20) primary key, studentClass varchar(20), studentScore int)delete Ainsert into A values ('张三','数学',50)insert into A values ('张三','语文',60)insert into A values ('李四','数学',60)insert into A values ('李四','语文',70)insert into A values ('王五','数学',70)insert into A values ('王五','语文',80)select case when(grouping(studentName)=1) then '全部' else isnull(studentName,'未知') end as studentName,case when(grouping(studentClass)=1) then '总计' else isnull(studentClass,'未知') end as studentClass,sum(studentScore) as studentScore from A group by studentName,studentClass with rollup

 

GROUPING

是一个聚合函数,它产生一个附加的列，当用 CUBE 或 ROLLUP 运算符添加行时，附加的列输出值为1，当所添加的行不是由 CUBE 或 ROLLUP 产生时，附加列值为0。

仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相联系的选择列表中才允许分组。