`

SQL语句:Group By总结(2)

阅读更多

SQL语句:Group By总结
来源:中国自学编程网   发布日期:2009-01-18  
2.3 GROUP BY [Expressions] WITH CUBE | ROLLUP

首先需要说明的是Group By All 语句是不能和CUBE ROLLUP 关键字一起使用的。

首先先说说CUBE关键字,以下是SQL Server 2000联机帮助中的说明:

指定在结果集内不仅包含由 GROUP BY 提供的正常行,还包含汇总行。在结果集内返回每个可能的组和子组组合的 GROUP BY 汇总行。GROUP BY 汇总行在结果中显示为 NULL,但可用来表示所有值。使用 GROUPING 函数确定结果集内的空值是否是 GROUP BY 汇总值。

结果集内的汇总行数取决于 GROUP BY 子句内包含的列数。GROUP BY 子句中的每个操作数(列)绑定在分组 NULL 下,并且分组适用于所有其它操作数(列)。由于 CUBE 返回每个可能的组和子组组合,因此不论指定分组列时所使用的是什么顺序,行数都相同。

我们通常的Group By语句是按照其后所跟的所有字段进行分组,而如果加入了CUBE关键字以后,那么系统将根据所有字段进行分组的基础上,还会通过对所有这些分组字段所有可能存在的组合形成的分组条件进行分组计算。由于上面举的例子过于简单,这里就再适合了,现在我们的数据集将换一个场景,一个表中包含人员的基本信息:员工所在的部门编号(C_EMPLINFO_DEPTID)、员工性别(C_EMPLINFO_SEX)、员工姓名(C_EMPLINFO_NAME)等。那么我现在想知道每个部门各个性别的人数,那么我们可以通过如下语句得到:

SELECT C_EMPLINFO_DEPTID, C_EMPLINFO_SEX, COUNT(*AS C_EMPLINFO_TOTALSTAFFNUM
FROM T_PERSONNEL_EMPLINFO
GROUP BY C_EMPLINFO_DEPTID, C_EMPLINFO_SEX

但是如果我现在希望知道:

1. 所有部门有多少人(这里相当于就不进行分组了,因为这里已经对员工的部门和性别没有做任何限制了,但是这的确也是一种分组条件的组合方式)

2. 每种性别有多人(这里实际上是仅仅根据性别(C_EMPLINFO_SEX)进行分组)

3. 每个部门有多少人(这里仅仅是根据部门(C_EMPLINFO_DEPTID)进行分组);那么我们就可以使用ROLLUP语句了。

SELECT C_EMPLINFO_DEPTID, C_EMPLINFO_SEX, COUNT(*AS C_EMPLINFO_TOTALSTAFFNUM
FROM T_PERSONNEL_EMPLINFO
GROUP BY C_EMPLINFO_DEPTID, C_EMPLINFO_SEX WITH CUBE

那么这里你可以看到结果集中多出了很多行,而且结果集中的某一个字段或者多个字段、甚至全部的字段都为NULL,请仔细看一下你就会发现实际上这些记录就是完成了上面我所列举的所有统计数据的展现。使用过SQL Server 2005或者RDLC的朋友们一定对于矩阵的小计和分组功能有印象吧,是不是都可以通过这个得到答案。我想RDLC中对于分组和小计的计算就是通过Group ByCUBEROLLUP关键字来实现的。(个人意见,未证实)

CUBE关键字还有一个极为相似的兄弟ROLLUP, 同样我们先从这英文入手,ROLL UP向上卷的意思,如果说CUBE的组合是绝对自由的,那么ROLLUP的组合就需要有点约束了。我们先来看看SQL Server 2000的联机中对ROLLUP关键字的定义:

指定在结果集内不仅包含由 GROUP BY 提供的正常行,还包含汇总行。按层次结构顺序,从组内的最低级别到最高级别汇总组。组的层次结构取决于指定分组列时所使用的顺序。更改分组列的顺序会影响在结果集内生成的行数。

那么这个顺序是什么呢?对了就是Group By 后面字段的顺序,排在靠近Group By的分组字段的级别高,然后是依次递减。如:Group By Column1, Column2, Column3。那么分组级别从高到低的顺序是:Column1 > Column2 > Column3。还是看我们前面的例子,SQL语句中我们仅仅将CUBE关键字替换成ROLLUP关键字,如:

SELECT C_EMPLINFO_DEPTID, C_EMPLINFO_SEX, COUNT(*AS C_EMPLINFO_TOTALSTAFFNUM
FROM T_PERSONNEL_EMPLINFO
GROUP BY C_EMPLINFO_DEPTID, C_EMPLINFO_SEX WITH ROLLUP

CUBE相比,返回的数据行数减少了不少。:),仔细看一下,除了正常的Group By语句后,数据中还包含了:

1. 部门员工数;(向上卷了一次,这次先去掉了员工性别的分组限制)

2. 所有部门员工数;(向上又卷了依次,这次去掉了员工所在部门的分组限制)

在现实的应用中,对于报表的一些统计功能是很有帮助的。

这里还有一个问题需要补充说明一下,如果我们使用ROLLUP或者CUBE关键字,那么将产生一些小计的行,这些行中被剔除在分组因素之外的字段将会被设置为NULL,那么还存在一种情况,比如在作为分组依据的列表中存在可空的行,那么NULL也会被作为一个分组表示出来,所以这里我们就不能仅仅通过NULL来判断是不是小计记录了。下面的例子展示了这里说得到的情况。还是我们前面提到的水果例子,现在我们在每种商品后面增加一个折扣列”(Discount),用于显示对应商品的折扣,这个数值是可空的,也就是可以通过NULL来表示没有对应的折扣信息。数据集如下所示:

FruitName

ProductPlace

Price

Discount

Apple

China

$1.1

0.8

Apple

Japan

$2.1

0.9

Apple

USA

$2.5

1.0

Orange

China

$0.8

NULL

Banana

China

$3.1

NULL

Peach

USA

$3.0

NULL

现在我们要统计各种折扣对应有多少种商品,并总计商品的总数。,那么我们可以通过如下的SQL语句来完成:

SELECT     COUNT(*AS ProductCount, Discount
FROM         T_TEST_FRUITINFO
GROUP BY Discount WITH ROLLUP

好了,运行一下,你会发现数据都正常出来了,按照如上的数据集,结果如下所示:

ProductCount

Discount

3

NULL

1

0.8

1

0.9

1

1.0

6

NULL

好了,各种折扣的商品数量都出来了,但是在显示没有折扣商品商品小计的时候判断上确存在问题,因为存在两条DiscountNull的记录。是哪一条呢?通过分析数据我们知道第一条数据(3, Null)应该对应没有折扣商品的数量,而(6,Null)应该对应所有商品的数量。需要判断这两个具有不同意义的Null就需要引入一个聚合函数Grouping。现在我们把语句修改一下,在返回值中使用Grouping函数增加一列返回值,SQL语句如下:

SELECT     COUNT(*AS ProductCount, Discount, GROUPING(Discount) AS Expr1
FROM         T_TEST_FRUITINFO
GROUP BY Discount WITH ROLLUP

这个时候,我们再看看运行的结果:

ProductCount

Discount

Expr1

3

NULL

0

1

0.8

0

1

0.9

0

1

1.0

0

6

NULL

1

对于根据指定字段Grouping中包含的字段进行小计的记录,这里会标记为1,我们就可以通过这个标记值将小计记录从判断那些由于ROLLUP或者CUBE关键字产生的行。Grouping(column_name)可以带一个参数,Grouping就会去判断对应的字段值的NULL是否是由ROLLUP或者CUBE产生的特殊NULL值,如果是那么就在由Grouping聚合函数产生的新列中将值设置为1。注意Grouping只会检查Column_name对应的NULL来决定是否将值设置为1,而不是完全由此列是否是由ROLLUP或者CUBE关键字自动添加来决定的。

2.2  Group By Having, Where ,Order by语句的执行顺序:

最后要说明一下的Group By, Having, Where, Order by几个语句的执行顺序。一个SQL语句往往会产生多个临时视图,那么这些关键字的执行顺序就非常重要了,因为你必须了解这个关键字是在对应视图形成前的字段进行操作还是对形成的临时视图进行操作,这个问题在使用了别名的视图尤其重要。以上列举的关键字是按照如下顺序进行执行的:Where, Group By, Having, Order by。首先where将最原始记录中不满足条件的记录删除(所以应该在where语句中尽量的将不符合条件的记录筛选掉,这样可以减少分组的次数),然后通过Group By关键字后面指定的分组条件将筛选得到的视图进行分组,接着系统根据Having关键字后面指定的筛选条件,将分组视图后不满足条件的记录筛选掉,然后按照Order By语句对视图进行排序,这样最终的结果就产生了。在这四个关键字中,只有在Order By语句中才可以使用最终视图的列名,如:

SELECT     FruitName, ProductPlace, Price, ID AS IDE, Discount
FROM         T_TEST_FRUITINFO
WHERE     (ProductPlace = N'china')
ORDER BY IDE

这里只有在ORDER BY语句中才可以使用IDE,其他条件语句中如果需要引用列名则只能使用ID,而不能使用IDE

 
分享到:
评论

相关推荐

    常用SQL*Plus语句:

    4. GROUP BY语句:用于对查询结果进行分组。 5. HAVING语句:用于对分组结果进行筛选。 例如: SELECT 字段名1, 字段名2, …… FROM 表名 WHERE 条件 GROUP BY 字段名 HAVING 条件; 四、SQL*Plus命令: 1. ...

    SQL语句中Group BY 和Rollup以及cube用法

    ### SQL语句中Group BY 和Rollup以及Cube用法 #### Group BY 子句 `GROUP BY`子句是SQL查询中的一个非常重要的部分,它用于将数据表中的行按照一个或多个列进行分组,使得可以对每个分组执行聚合函数(如SUM、...

    SQL语句练习及答案

    根据给定文件中的标题“SQL语句练习及答案”与描述“一个非常适合在笔试前看的SQL练习题。也可在在平时作为SQL语言练习题来使用”,我们可以看出这份材料主要包含了一系列针对SQL语言的练习题及其解答,适用于考前...

    经典SQL语句大全.

    - SQL语句:`GROUP BY column_name;` - 功能:将数据按某一列或某些列进行分组,常与聚合函数如COUNT、SUM等结合使用,以便对各组进行汇总计算。 以上SQL语句涵盖了数据库管理、表操作、数据检索与更新、高级查询...

    sql语句大全总结

    此外,SQL 语句还可以使用 JOIN 语句、SUBQUERY 语句、GROUP BY 语句、HAVING 语句等等,以满足不同的数据查询和分析需求。 SQL 语句大全总结提供了完整的 SQL 教程,涵盖了基础语句语法、函数教程、高级编程教程...

    数据库系统原理实验报告-SQL查询语句.doc

    SQL 语句:SELECT 课程号, AVG(作业1 成绩 + 作业2 成绩 + 作业3 成绩) AS 平均成绩 FROM 学生作业表 GROUP BY 课程号 HAVING AVG(作业1 成绩 + 作业2 成绩 + 作业3 成绩) > 80; 查询结果截图: 四、实验总结 通过...

    sql 语句的笔试题

    SQL语句:select * from (select name, year b1, lead(year) over (partition by name order by year) b2, lead(m,2) over(partition by name order by year) b3,rank()over( partition by name order by year) rk ...

    SQL语句基础教程

    SQL语句基础教程 SQL(Structured Query Language)是一种特殊目的语言,用于管理关系数据库管理系统(RDBMS)。SQL语句基础教程旨在帮助新手和需要复习SQL的资料仓储业界老将,学习SQL基础知识和语法。 SQL指令 -...

    SQL语句总结

    本文将深入探讨“SQL语句总结”这一主题,特别关注MySQL数据库的常用操作。 首先,我们从基础的数据库操作开始,即CRUD(Create、Read、Update、Delete),它们构成了所有数据库操作的核心。 1. **创建(Create)*...

    SQL GROUP BY 语句

    SQL GROUP BY 语句 SQL GROUP BY 语句

    SQL GROUP BY 语句详解

    SQL GROUP BY 语句是数据库查询中非常重要的一个部分,它允许我们对数据进行分组,以便于统计和分析。在处理大型数据集时,GROUP BY 与聚合函数(如 SUM、COUNT、AVG、MAX 和 MIN)一起使用,能够帮助我们汇总数据,...

    分布式数据库sql语句2022优秀文档.ppt

    * GROUP BY 语句:用于分组数据 * HAVING 语句:用于指定分组条件 * ORDER BY 语句:用于排序数据 * LIMIT 语句:用于限制查询结果的数量 三、 创建数据库结构 SQL 语句可以用来创建数据库结构,包括数据库表、...

    SQL语句详细解释

    在 SQL 语句中,还有许多其他的语句和函数,例如 GROUP BY、HAVING、JOIN 等。这些语句和函数可以帮助我们更好地操作和分析数据。 SQL 语句是一种功能强大且灵活的语言,用于管理和操作关系数据库管理系统。通过...

    2022年SQL语句简单面试题.doc

    本文将对SQL语句简单面试题进行详细的知识点总结,包括Group By语句、Case When语句、日期判断语句等多个知识点。 1. Group By语句: 在第一道面试题中,需要使用Group By语句对表中的数据进行分组统计。Group By...

    oracle的SQL语句的一些经验总结

    以下是对"Oracle的SQL语句的一些经验总结"中可能涉及的关键知识点的详细阐述: 1. **基本查询**:SQL的基础是SELECT语句,用于从表中提取数据。例如,`SELECT column1, column2 FROM table_name;`用于获取指定列的...

    SQL中GROUP BY的用法

    GROUP BY 是 SQL 中的一种分组查询语句,通常与聚合函数配合使用。GROUP BY 语句根据一个或多个列将查询结果分组,并对每组计算聚合函数的值。 在使用 GROUP BY 语句时,需要注意以下几点: 1. select 语句中所有...

    SQL语句练习--数据库

    ### SQL语句练习知识点解析 #### 一、创建数据库与数据表 1. **创建数据库:** - **SQL语句:** `CREATE DATABASE student;` - **知识点解析:** 这条命令用于创建一个新的数据库`student`。在创建数据库时,...

    sqlserver+group by分组查询分页存储过程

    3. **执行分页查询**:使用动态生成的SQL语句执行查询,并通过设置`ROWCOUNT`来限制返回的结果集大小。 ### 综合运用`GROUP BY`、存储过程和分页技术 在给定的存储过程中,`GROUP BY`子句与分页查询的结合使用提供...

    JsonSQL:用SQL语句解析JSON文件

    **JsonSQL: SQL语句解析JSON文件** 在大数据处理和Web应用中,JSON(JavaScript Object Notation)格式已经成为数据交换的常见格式。然而,对于习惯使用SQL查询关系型数据库的人来说,处理JSON数据可能会觉得不太...

    Sql语句收藏 Sql语句收藏

    ### SQL语句一 #### 原始SQL语句: ```sql select *, (select count(0) from [picture] where album_id = [album].id) as piccount From [Album] Where id in (5,6,8,1,3,4) orderby charindex(',' + ltrim(rtrim...

Global site tag (gtag.js) - Google Analytics