SQL Server 2008中增强的汇总技巧 .

最新推荐文章于 2024-08-26 10:40:16 发布
最新推荐文章于 2024-08-26 10:40:16 发布
阅读量754 收藏
点赞数
分类专栏： sql 文章标签： sql server null sql table delphi object


SQL Server　2008中对汇总有明显的增强，有点像Oracle的语法了。请看下面五个例子：

假定场景如下：某几位员工在不同时间参加了不同的项目，获取了相应的收入，现在需要按各种分类进行统计。

基本表如下：

[sql] view plaincopyprint?
01.USE testDb2  
02.GO  
03.  
04.IF NOT OBJECT_ID('tb_Income') IS NULL  
05.DROP TABLE [tb_Income]  
06.  
07./****** Object: Table [dbo].[tb_Income] Script Date: 2012/4/5 8:19:21 ******/  
08.  
09.CREATE TABLE [dbo].[tb_Income](  
10.[TeamID] int not null,  
11.[PName] [Nvarchar](20) NOT NULL,  
12.[CYear] Smallint NOT NULL,  
13.[CMonth] TinyInt NOT NULL,  
14.[CMoney] Decimal (10,2) Not Null  
15.  
16.)  
17.  
18.GO  
19.INSERT [dbo].[tb_Income]  
20.SELECT 1,'胡一刀',2011,2,5600  
21.union ALL SELECT 1,'胡一刀',2011,1,5678  
22.union ALL SELECT 1,'胡一刀',2011,3,6798  
23.union ALL SELECT 2,'胡一刀',2011,4,7800  
24.union ALL SELECT 2,'胡一刀',2011,5,8899  
25.union ALL SELECT 3,'胡一刀',2012,8,8877  
26.  
27.union ALL SELECT 1,'苗人凤',2011,1,3455  
28.union ALL SELECT 1,'苗人凤',2011,2,4567  
29.union ALL SELECT 2,'苗人凤',2011,3,5676  
30.union ALL SELECT 3,'苗人凤',2011,4,5600  
31.union ALL SELECT 2,'苗人凤',2011,5,6788  
32.union ALL SELECT 2,'苗人凤',2012,6,5679  
33.union ALL SELECT 2,'苗人凤',2012,7,6785  
34.  
35.union ALL SELECT 2,'张无忌',2011,2,5600  
36.union ALL SELECT 2,'张无忌',2011,3,2345  
37.union ALL SELECT 2,'张无忌',2011,5,12000  
38.union ALL SELECT 3,'张无忌',2011,4,23456  
39.union ALL SELECT 3,'张无忌',2011,6,4567  
40.union ALL SELECT 1,'张无忌',2012,7,6789  
41.union ALL SELECT 1,'张无忌',2012,8,9998  
42.  
43.union ALL SELECT 3,'赵半山',2011,7,6798  
44.union ALL SELECT 3,'赵半山',2011,10,10000  
45.union ALL SELECT 3,'赵半山',2011,9,12021  
46.union ALL SELECT 2,'赵半山',2012,11,8799  
47.union ALL SELECT 1,'赵半山',2012,12,10002  
48.  
49.union ALL SELECT 3,'令狐冲',2011,8,7896  
50.union ALL SELECT 3,'令狐冲',2011,9,7890  
51.union ALL SELECT 2,'令狐冲',2011,10,7799  
52.union ALL SELECT 2,'令狐冲',2011,11,9988  
53.union ALL SELECT 2,'令狐冲',2012,9,34567  
54.union ALL SELECT 3,'令狐冲',2012,12,5609  
55.  
56.GO  
57.  
58.


 

数据如下：

[sql] view plaincopyprint?
01.SELECT * FROM tb_Income  
02.  
03./*  
04.  
05.TeamID PName CYear CMonth CMoney  
06.1 胡一刀 2011 2 5600.00  
07.1 胡一刀 2011 1 5678.00  
08.1 胡一刀 2011 3 6798.00  
09.2 胡一刀 2011 4 7800.00  
10.2 胡一刀 2011 5 8899.00  
11.3 胡一刀 2012 8 8877.00  
12.1 苗人凤 2011 1 3455.00  
13.1 苗人凤 2011 2 4567.00  
14.2 苗人凤 2011 3 5676.00  
15.3 苗人凤 2011 4 5600.00  
16.2 苗人凤 2011 5 6788.00  
17.2 苗人凤 2012 6 5679.00  
18.2 苗人凤 2012 7 6785.00  
19.2 张无忌 2011 2 5600.00  
20.2 张无忌 2011 3 2345.00  
21.2 张无忌 2011 5 12000.00  
22.3 张无忌 2011 4 23456.00  
23.3 张无忌 2011 6 4567.00  
24.1 张无忌 2012 7 6789.00  
25.1 张无忌 2012 8 9998.00  
26.3 赵半山 2011 7 6798.00  
27.3 赵半山 2011 10 10000.00  
28.3 赵半山 2011 9 12021.00  
29.2 赵半山 2012 11 8799.00  
30.1 赵半山 2012 12 10002.00  
31.3 令狐冲 2011 8 7896.00  
32.3 令狐冲 2011 9 7890.00  
33.2 令狐冲 2011 10 7799.00  
34.2 令狐冲 2011 11 9988.00  
35.2 令狐冲 2012 9 34567.00  
36.3 令狐冲 2012 12 5609.00  
37.*/  
38.  
39.


 

一、使用CUBE汇总数据（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）

小试牛刀，

[sql] view plaincopyprint?
01./*********使用CUBE汇总数据***************/  
02.  
03./********* 3w@live.cn 邀月***************/  
04.SELECT TeamID as 小组ID,  
05.SUM(CMoney) 总收入  
06.FROM tb_Income  
07.GROUP BY CUBE (TeamID)  
08.----ORDER BY TeamID desc


 

改进查询：

[sql] view plaincopyprint?
01.SELECT TeamID as 小组ID,PName as 姓名,  
02.SUM(CMoney) 总收入  
03.FROM tb_Income  
04.GROUP BY CUBE (TeamID,PName)


 

二、使用ROLLUP汇总数据（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）

 

[sql] view plaincopyprint?
01./*********使用ROLLUP汇总数据***************/  
02.  
03./********* 3w@live.cn 邀月***************/  
04.  
05.SELECT TeamID as 小组ID,PName as 姓名,  
06.SUM(CMoney) 总收入  
07.FROM tb_Income  
08.GROUP BY ROLLUP (TeamID,PName)


 

三、使用Grouping Sets创建自定义汇总数据（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）

除了Cube和Rollup，还有更加灵活强大的自定义集合汇总－－Grouping Sets

[delphi] view plaincopyprint?
01./*********使用Grouping Sets创建自定义汇总数据***************/  
02.  
03./********* 3w@live.cn 邀月***************/  
04.  
05.SELECT TeamID as 小组ID,PName as 姓名,CYear as 年份,----min(CMonth) as 月份,  
06.SUM(CMoney) 总收入  
07.FROM tb_Income  
08.Where CMonth=2  
09.GROUP BY grouping SETS ((TeamID),(TeamID,PName),(CYear,PName))


 

四、使用Grouping标识汇总行（http://technet.microsoft.com/zh-cn/library/ms178544.aspx）

 细心的朋友可能会注意到，如果Cube后有两个以上的汇总列时，可能会有一些列是Null,那么这些Null值究竟本身就是Null,还是由于聚合产生的Null呢，此时，Grouping函数大显身手的机会来了。

[sql] view plaincopyprint?
01./*********使用Grouping标识汇总行***************/  
02.  
03./********* 3w@live.cn 邀月***************/  
04.SELECT TeamID as 小组ID,CYear as 年份,  
05.CASE WHEN grouping(TeamID)=0 AND grouping(CYear)=1 THEN '小组汇总'  
06.WHEN grouping(TeamID)=1 AND grouping(CYear)=0 THEN '年份汇总'  
07.WHEN grouping(TeamID)=1 AND grouping(CYear)=1 THEN '所有汇总'  
08.else '正常行' END as 行类别,  
09.SUM(CMoney) 总收入  
10.FROM tb_Income  
11.GROUP BY CUBE (TeamID,CYear)


 

至此，如果还有美中不足的话，那就是分组还是有点凌乱，下面我们将隆重推出终极武器－－Grouping_ID，它与Grouping类似，但提供更为精细的颗粒度，以确认分组级别，当然使用也更为复杂，请看下面的示例：

五、使用Grouping_ID标识分组级别（http://technet.microsoft.com/zh-cn/library/bb510624.aspx）

为了更清楚地说明问题，我们需要修改一下表结构，增加一个字段－－项目所在的地点（AreaID）,如下：

 

[sql] view plaincopyprint?
01./*************修改表结构***************************/  
02.  
03.ALTER table tb_Income   
04.add AreaID int null  
05.  
06.GO  
07.  
08.update tb_Income SET AreaID=TeamID+CMonth%5+CYear%2  
09.GO


此时数据变成这样：

[sql] view plaincopyprint?
01.SELECT * FROM tb_Income  
02.  
03./*  
04.TeamID    PName    CYear    CMonth    CMoney    AreaID  
05.    胡一刀    2011    2    5600.00    4  
06.    胡一刀    2011    1    5678.00    3  
07.    胡一刀    2011    3    6798.00    5  
08.    胡一刀    2011    4    7800.00    7  
09.    胡一刀    2011    5    8899.00    3  
10.    胡一刀    2012    8    8877.00    6  
11.    苗人凤    2011    1    3455.00    3  
12.    苗人凤    2011    2    4567.00    4  
13.    苗人凤    2011    3    5676.00    6  
14.    苗人凤    2011    4    5600.00    8  
15.    苗人凤    2011    5    6788.00    3  
16.    苗人凤    2012    6    5679.00    3  
17.    苗人凤    2012    7    6785.00    4  
18.    张无忌    2011    2    5600.00    5  
19.    张无忌    2011    3    2345.00    6  
20.    张无忌    2011    5    12000.00    3  
21.    张无忌    2011    4    23456.00    8  
22.    张无忌    2011    6    4567.00    5  
23.    张无忌    2012    7    6789.00    3  
24.    张无忌    2012    8    9998.00    4  
25.    赵半山    2011    7    6798.00    6  
26.    赵半山    2011    10    10000.00    4  
27.    赵半山    2011    9    12021.00    8  
28.    赵半山    2012    11    8799.00    3  
29.    赵半山    2012    12    10002.00    3  
30.    令狐冲    2011    8    7896.00    7  
31.    令狐冲    2011    9    7890.00    8  
32.    令狐冲    2011    10    7799.00    3  
33.    令狐冲    2011    11    9988.00    4  
34.    令狐冲    2012    9    34567.00    6  
35.    令狐冲    2012    12    5609.00    5  
36.*/


我们需要统计小组、地区、月份三个维度的汇总数据。

 

[sql] view plaincopyprint?
01./*********使用Grouping_ID标识分组级别***************/  
02.  
03./*********  3w@live.cn  邀月***************/  
04.  
05.SELECT TeamID as 小组ID,AreaID as 地点ID,CMonth as 月份,  
06.SUM(CMoney) 总收入  
07.FROM tb_Income   
08.Where AreaID IN (3,5,6,7,8,9,2,4) AND CYear =2011  AND CMonth=2  
09.GROUP BY CUBE (TeamID,AreaID,CMonth)  
10.----ORDER  BY TeamID,AreaID,CMonth


 

统计结果：

 

我们注意到，由于维度从两个变成三个，此时数据比较凌乱，即使排序也不能有效解决。幸好，我们有Grouping_ID。看下例

[sql] view plaincopyprint?
01.SELECT TeamID as 小组ID,AreaID as 地点ID,CMonth as 月份,  
02.  
03.CASE grouping_ID(TeamID,AreaID,CMonth)  
04. WHEN 1 THEN '小组/地点汇总'  
05. WHEN 2 THEN '小组/月份汇总'  
06. WHEN 3 THEN '小组汇总'  
07. WHEN 4 THEN '地点/月份汇总'  
08. WHEN 5 THEN '地点汇总'  
09. WHEN 6 THEN '月份汇总'  
10. WHEN 7 THEN '所有汇总'  
11. else '正常行' END as 行类别,  
12.  
13.SUM(CMoney) 总收入  
14.FROM tb_Income   
15.Where AreaID IN (3,5,6,7,8,9,2,4) AND CYear =2011  AND CMonth=2  
16.GROUP BY CUBE (TeamID,AreaID,CMonth)  
17.----ORDER  BY TeamID,AreaID,CMonth


 

注意：代码中新增的部分，这里需要稍微解释一下，Grouping_ID接受几个输入列，返回二进制列列表计算的整数值，你可以把这三个维度，看作是（0,1,1）、（0,1,0）这样类似的二进制，而Grouping_ID负责将运算结果以整数形式返回。

效果：

 

至此，Group By的汇总暂时告一段落，希望您不虚此行，有所斩获！

 

原文章：http://blog.csdn.net/downmoon/article/details/7430645

Microsoft SQL Server 2008技术内幕：T-SQL查询.pdf
08-09
在SQL Server 2008中，T-SQL得到了进一步的增强和优化，提供了更多的功能和性能提升。以下是一些关键的知识点： 1. **基础查询语法**：包括SELECT语句的基本结构，用于从一个或多个表中检索数据。学习如何使用WHERE...
SQL Server 2008中增强的汇总技巧
dfdfadsf3443的博客
04-06 152
SQL Server 2008中SQL应用系列--目录索引 SQL Server　2008中对汇总有明显的增强，有点像Oracle的语法了。请看下面五个例子： 假定场景如下：某几位员工在不同时间参加了不同的项目，获取了相应的收入，现在需要按各种分类进行统计。 基本表如下： View Code USE testDb2GOIF NOT OBJECT_ID('tb_Inc...
SQL 汇总统计及GROUP BY
黑码哥的技术博客
12-03 4728
介绍几个常用的聚集函数及使用方法，使用聚集函数实现分组统计 介绍如何对分组统计的结果进行过滤？， 介绍如何对分组统计的结果进行排序？， 介绍SELECT语句中各个子句的书写顺序
SQL Server 聚合函数与分组学习（group by）
有道无术，术尚可求，有术无道，止于术。
01-01 8203
一、SQLServer里面的常用的五种聚合函数。 1.Sum（和）-—对于NULL值默认为0 2.MAX（最大值）MIN（最小值） 3.AVG （平均值）—不统计空值，例如存在有四个人，但是只有三个人有成绩，平均成绩为：总和/3 4.COUNT(数量) --统计所有人分数总和 select sum(score) from T_table --统计表中一共有多少条记录 sele...
SQL Server 查询数据并汇总相关技巧 23.08.08
zgscwxd的博客
08-08 1649
【代码】SQL Server 查询数据并汇总相关技巧 23.08.08。
sql sum,group by 分组求和后在求总和，with rollup，with cube的区别
the_heart_is_die的博客
10-15 2万+
有表数据如下： create table tt ( name varchar(10), num decimal(18,2), ph int ) insert into tt(name,num,ph) values ('test1',10,1) insert into tt(name,num,ph) values ('test1',30,1) insert...
Microsoft.Press.Inside.Microsoft.SQL.Server.2008.T-SQL.Programming
09-15
本书深入讲解了T-SQL的基础知识及其在SQL Server 2008中的应用。 #### 三、T-SQL查询技巧 - **基本查询**：本书首先介绍了如何使用SELECT语句进行基本的数据检索操作，包括简单的数据选择、过滤和排序。 - **复杂...
利用SQL Server存储过程开发应用程序.pdf
09-19
SQL Server 存储过程是数据库管理中的一种核心功能，它是一组预先编译的SQL语句集合，能够执行复杂的数据库操作。存储过程不仅提高了程序的运行效率，还能增强数据库的安全性，因为它允许限制对特定数据的访问。在...
SQL SERVER 2008R2使用手册
06-26
除此之外，手册还提及了SQL Server 2008 R2中一些不为人知的功能和细节，比如如何在运行中合理分配CPU和内存资源，以及一些常见问题的汇总。这些内容对于数据库管理员来说非常有帮助，尤其是对于优化和故障排除方面...
Microsoft SQL Server 2008技术内幕：T-SQL查询
12-04
在SQL Server 2008中，T-SQL得到了进一步的增强和优化，以提升性能和功能。 本书可能包含以下核心知识点： 1. **T-SQL基础**：包括SELECT语句的使用，用于从数据库中检索数据；INSERT、UPDATE和DELETE语句，用于...
sql server精华技巧集
02-04
网上收集的和SqlServer有关的技术方面的知识。
SqlServer2008实例10汇总数据
复行数十步
06-30 548
1.使用CUBE根据GROUP BY子句中的列来为结果集增加汇总值； CUBE为结果集增加行，根据GROUP BY子句巾的列来汇总总数值。下面示例演示了一个根据产品放置的货架束返回产品总数量的查询： SELECT i.Shelf,SUM(i.Quantity) Total FROM Production.ProductInventory i GROUP BY CUBE(i.Shelf) 在下一个查询中，我们将LocationID增加剑SELECT和GROUP BY子句中： USE Adve.
SQL server聚合函数查询
最新发布
weixin_72431427的博客
08-26 276
函数用于返回满足条件的行数。可以计算特定列的非空值数量，也可以计算表中的总行数。函数用于返回指定列中的最小值。它可以用于数值、日期和字符串类型的列。函数用于计算指定列的平均值。它只能用于数值数据类型的列。函数用于计算指定列的总和。它通常用于数值数据类型的列。子句一起使用，以便对特定列的分组进行聚合计算。这条查询将为每个部门返回员工总数和平均工资。函数用于返回指定列中的最大值。类似，它也可以用于各种数据类型。部门中所有员工工资的总和。部门员工工资的平均值。部门员工的最低工资。部门员工的最高工资。
SQLSERVER 使用 ROLLUP 汇总数据，实现分组统计，总计（合计），小计
程金鹏（程利鹏）
06-17 2万+
在工作中遇到了这样的一个问题，就是需要将查询出来的数据进行分类汇总。这里用到了with rollup。下面是效果图。 为了得到这样的效果，我们该如何做呢，我们来做这个小demo。 数据库字样 需求分析 本实例为demo，没有任何意义。仅仅是为了完成功能。 1、依次按照住址，部门，性别三个条件进行汇总，汇总出他们的月薪总金额，和纳税总金额。 代码实现1——依次按照住址，部门，性别三个...
sql server 小技巧 集锦
qq_18932003的博客
10-20 205
sql server 小技巧(1) 导入csv数据到sql server sql server 小技巧(2) 删除sql server中重复的数据 sql server 小技巧(3) SQL Server 2012 数据库完整导出到SQL Azure （包括数据） sql server 小技巧(4) Sql server 排序时让空值排在最后 sql server 小技巧(5) Sql server 获取指定字符后的所有字符 - 去掉指定字符前的所有字符 sql server 小技巧(6) C
SQL Server中的group by(分组)
热门推荐
秦哥的博客
08-21 3万+
零、码仙励志 很多爱不能重来，我应该释怀，输不起就不要输 一、建库和建表 create database scort use scort create table emp ( empno int primary key, ename nvarchar(10), sal int, deptno int ) insert into emp values (7369,'smith',18...
sqlserver group by [column...] [with cube|with rollup] 层级汇总
hawk_2016的博客
08-08 826
今天在一本书上看到了 group by [column…] [with cube|with rollup]的用法，对于多层级汇总很好，避免了自己拼凑语句（把汇总数据加在最后）。 1、单字段分组 SELECT isnull(bd.bu_name,'合计') as bu_name ,sum(sales_quantity) as sales_quantity ,...
第八章：SQL Server2019数据库之分组统计(GROUP BY)
Amo Xiang的博客
04-21 3856
目录一、分组统计1、使用 GROUP BY 子句创建分组2、使用 GROUP BY 子句创建多列分组3、对表达式进行分组统计4、在统计中使用 ROLLUP 关键字和 CUBE 关键字5、GROUP BY 子句的 NULL 值处理6、使用 HAVING 子句进行过滤分组 学前必备知识 第一章：SQL Server 数据库环境搭建与使用 第二章：SQL Server2019 数据库的基本使用之图形化界面操作 第三章：SQL Server2019数据库 之 开启 SQL 语言之旅 第四章：SQL Server2
sql server 技巧
情随事迁个人博客
11-12 182
str(xxx/60,10,2) 保留2位小数cast( (case xx when '' then '0' else str(xx) end) as decimal(18, 5)) 解决为0 问题。 强制为空的显示(case isnull(c.res,'') when '' then '未核对' else c.res end) 核对 选中一个语句可以单独执行指定语句。。 变量设置方便测试 DEC...
深入探索：微软SQL Server 2008 T-SQL查询
在SQL Server 2008中，T-SQL（Transact-SQL）是用于管理和操作数据库的主要编程语言，它是SQL标准的扩展，增加了事务处理、过程编程和特定于微软数据库系统的功能。本书深入解析了T-SQL查询的各个方面，包括但不限于...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值