关于分组统计的问题

Question

怎样实现这样的查询统计呀ID   NAME    LEIBIE 
1    A       学生
2    B       学生
3    C       老师
4    D       学生
返回的结果为
ID    NAME   LEIBIE
1     A      学生
2     B       学生
4     D       学生
              3
3     C       老师
              1

Answer 1

用 ROLLUP 汇总数据

Answer 2

例如，简单表 Inventory 中包含：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 ROLLUPItem                 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                     (7 row(s) affected)

Answer 3

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
*/