SQL SERVER 2008的Grouping sets的用法

本文深入探讨了SQL中的SUM、GROUP BY与WITH ROLLUP/CUBE用法,通过具体实例展示了如何进行聚合计算与分组操作,旨在帮助读者掌握高级SQL查询技巧。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)

INSERT Sales VALUES(1, 2005, 12000)

INSERT Sales VALUES(1, 2006, 18000)

INSERT Sales VALUES(1, 2007, 25000)

INSERT Sales VALUES(2, 2005, 15000)

INSERT Sales VALUES(2, 2006, 6000)

INSERT Sales VALUES(3, 2006, 20000)

INSERT Sales VALUES(3, 2007, 24000)

SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY EmpId, Yr WITH ROLLUP

SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY EmpId, Yr WITH CUBE

 

SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), ())

EmpId       Yr          Sales
----------- ----------- ---------------------
1           2005        12000.00
1           2006        18000.00
1           2007        25000.00
1           NULL        55000.00
2           2005        15000.00
2           2006        6000.00
2           NULL        21000.00
3           2006        20000.00
3           2007        24000.00
3           NULL        44000.00
NULL        NULL        120000.00

SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), (Yr), ())

EmpId       Yr          Sales
----------- ----------- ---------------------
1           2005        12000.00
2           2005        15000.00
NULL        2005        27000.00
1           2006        18000.00
2           2006        6000.00
3           2006        20000.00
NULL        2006        44000.00
1           2007        25000.00
3           2007        24000.00
NULL        2007        49000.00
NULL        NULL        120000.00
1           NULL        55000.00
2           NULL        21000.00
3           NULL        44000.00


SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY GROUPING SETS((EmpId, Yr), (EmpId))

EmpId       Yr          Sales
----------- ----------- ---------------------
1           2005        12000.00
1           2006        18000.00
1           2007        25000.00
1           NULL        55000.00
2           2005        15000.00
2           2006        6000.00
2           NULL        21000.00
3           2006        20000.00
3           2007        24000.00
3           NULL        44000.00

SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY GROUPING SETS((EmpId, Yr), ())

EmpId       Yr          Sales
----------- ----------- ---------------------
1           2005        12000.00
1           2006        18000.00
1           2007        25000.00
2           2005        15000.00
2           2006        6000.00
3           2006        20000.00
3           2007        24000.00
NULL        NULL        120000.00

 

SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY GROUPING SETS((EmpId), (Yr))

EmpId       Yr          Sales
----------- ----------- ---------------------
NULL        2005        27000.00
NULL        2006        44000.00
NULL        2007        49000.00
1           NULL        55000.00
2           NULL        21000.00
3           NULL        44000.00

 

SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY GROUPING SETS((EmpId, Yr))

EmpId       Yr          Sales
----------- ----------- ---------------------
1           2005        12000.00
2           2005        15000.00
1           2006        18000.00
2           2006        6000.00
3           2006        20000.00
1           2007        25000.00
3           2007        24000.00


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值