declare @t table(ID int,NAME varchar(10),LEIBIE varchar(10))
insert into @t select 1,'A','学生'
insert into @t select 2,'B','学生'
insert into @t select 3,'C','老师'
insert into @t select 4,'D','学生'
select
ID,NAME,(case when NAME is null then rtrim(count(*)) else LEIBIE end) as LEIBIE
from
@t
group by
LEIBIE,ID,NAME with rollup
having
grouping(NAME)=0 or (grouping(ID)=1 and grouping(LEIBIE)=0)
/*
ID NAME LEIBIE
----------- ---------- ------------
3 C 老师
NULL NULL 1
1 A 学生
2 B 学生
4 D 学生
NULL NULL 3
*/
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
下列查询将生成小计报表:
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP
Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair ALL 311.00
Table Blue 124.00
Table Red 223.00
Table ALL 347.00
ALL ALL 658.00