with rollup与 with cube 学习笔记

测试语句：

USE [AIS20140113215649]
GO
/****** Object:  Table [dbo].[t_studet]    Script Date: 10/29/2014 16:35:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from sysobjects where xtype='U' and name = 't_studet')
drop table t_studet
GO
CREATE TABLE [dbo].[t_studet](
    [ID] [int] NOT NULL,
    [sex] [nchar](10) NOT NULL,
    [class] [nchar](10) NOT NULL,
    [score] [int] NOT NULL,
 CONSTRAINT [PK_t_studet] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

select * from t_studet;

insert into t_studet values(1,'男','二年级',1);
insert into t_studet values(2,'男','三年级',4);
insert into t_studet values(3,'女','二年级',10);
insert into t_studet values(4,'女','三年级',16);

查询结果分析

----明细查询
select * from t_studet;

ID          sex        class      score
----------- ---------- ---------- -----------
1           男          二年级        1
2           男          三年级        4
3           女          二年级        10
4           女          三年级        16

----分组查询
select sex,class,COUNT(ID),SUM(score) from t_studet
group by sex,class;

sex        class                 
---------- ---------- ----------- -----------
男          二年级        1           1
女          二年级        1           10
男          三年级        1           4
女          三年级        1           16

----分组查询并合计
select class,sex,COUNT(ID),SUM(score) from t_studet
group by class,sex
with rollup;

class      sex                   
---------- ---------- ----------- -----------
二年级        男          1           1
二年级        女          1           10
二年级        NULL       2           11
三年级        男          1           4
三年级        女          1           16
三年级        NULL       2           20
NULL       NULL       4           31

----分组查询并合计，标记合计列
select 
       case when(grouping(sex)=1 and grouping(class)=1) then '总计' else class end,
       case 
            when(grouping(sex)=1 and grouping(class)=0) then '小计' 
            when(grouping(sex)=1 and grouping(class)=1) then '' 
            else sex 
       end,
       COUNT(ID),SUM(score) 
from t_studet
group by class,sex
with rollup;
-------------左连接式的小计

----grouping(class)=0 代表 有数据
----grouping(class)=1 代表 无数据

---------- ---------- ----------- -----------
二年级        男          1           1
二年级        女          1           10
二年级        小计         2           11
三年级        男          1           4
三年级        女          1           16
三年级        小计         2           20
总计                    4           31

----分组查询并合计，标记合计列,使用条件过滤
select 
       case when(grouping(sex)=1 and grouping(class)=1) then '总计' else class end,
       case 
            when(grouping(sex)=1 and grouping(class)=0) then '小计' 
            when(grouping(sex)=1 and grouping(class)=1) then '' 
            else sex 
       end,
       COUNT(ID),SUM(score) 
from t_studet
group by class,sex 
with rollup having (grouping(sex)=0 and grouping(class)=0)

                                 
---------- ---------- ----------- -----------
二年级        男          1           1
二年级        女          1           10
三年级        男          1           4
三年级        女          1           16

select class,sex,COUNT(ID),SUM(score) 
from t_studet 
group by sex,class with cube 

-------------全连接式的小计

class      sex                   
---------- ---------- ----------- -----------
二年级        男          1           1
二年级        女          1           10
二年级        NULL       2           11
三年级        男          1           4
三年级        女          1           16
三年级        NULL       2           20
NULL       NULL       4           31
NULL       男          2           5
NULL       女          2           26