GROUP BY 子句用来为结果集中的每一行产生聚合值。如果聚合函数没有使用 GROUP BY 子句,则只为 SELECT 语句报告一个聚合值。
以下示例返回分类 2 中每种产品已销售的单位数量:
USE Northwind
SELECT OrdD.ProductID AS ProdID,
SUM(OrdD.Quantity) AS AmountSold
FROM [Order Details] AS OrdD JOIN Products as Prd
ON OrdD.ProductID = Prd.ProductID
AND Prd.CategoryID = 2
GROUP BY OrdD.ProductID下面是结果集:
ProdID AmountSold
----------- -----------
3 328
4 453
5 298
6 301
8 372
15 122
44 601
61 603
63 445
65 745
66 239
77 791
(12 row(s) affected)GROUP BY 关键字后面跟着列的列表,称为分组列。GROUP BY 子句限制结果集中的行;对于分组列中的每个非重复值只有一行。每个结果集行都包含与其分组列中特定值相关的汇总数据。
当 SELECT 语句中包含 GROUP BY 关键字时,对可以在选择列表中指定的项有一些限制。在该选择列表中所允许的项目是:
分组列。
为分组列中的每个值只返回一个值的表达式,例如将列名作为其中一个参数的聚合函数。这些函数称为矢量聚合。
例如,TableX 包含:
ColumnA ColumnB ColumnC
------- ------- -------
1 abc 5
1 def 4
1 ghi 9
2 jkl 8
2 mno 3
如果 ColumnA 是分组列,则结果集中将有两行,其中一行汇总值 1 的信息,而另一行汇总值 2 的信息。
如果 ColumnA 是分组列,要引用 ColumnB 或 ColumnC,这两列必须能为 ColumnA 中的每个值返回单个值的聚合函数中的参数。选择列表中包含诸如 MAX (ColumnB)、SUM (ColumnC) 或 AVG (ColumnC) 之类的表达式是合法的:
SELECT ColumnA,
MAX(ColumnB) AS MaxB,
SUM(ColumnC) AS SumC
FROM TableX
GROUP BY ColumnA该选择语句返回两行,为 ColumnA 中的每个唯一值各返回一行:
ColumnA MaxB SumC
----------- ---- -----------
1 ghi 18
2 mno 11
(2 row(s) affected)但是,选择列表中只包含 ColumnB 表达式是不合法的:
SELECT ColumnA,
ColumnB,
SUM(ColumnC) AS SumC
FROM TableX
GROUP BY ColumnA由于 GROUP BY 关键字只能返回一行,该行 ColumnA 中的值为 1,因此无法返回与 ColumnA 中的值 1 关联的 ColumnB 的三个值(abc、def 和 ghi)。
不能对 ntext、text、image 或 bit 列使用 GROUP BY 或 HAVING 子句,除非它们所在的函数返回的值具有其它数据类型。这样的函数包括 SUBSTRING 和 CAST。
使用 HAVING 子句选择行
HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用;而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似,但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。
下面的查询得到本年度截止到目前的销售额超过 $40,000 的出版商:
USE pubs
SELECT pub_id, total = SUM(ytd_sales)
FROM titles
GROUP BY pub_id
HAVING SUM(ytd_sales) > 40000下面是结果集:
pub_id total
------ -----------
0877 44219
(1 row(s) affected)为了确保对每个出版商的计算中至少包含六本书,下面示例使用 HAVING COUNT(*) > 5 消除返回的总数小于六本书的出版商:
USE pubs
SELECT pub_id, total = SUM(ytd_sales)
FROM titles
GROUP BY pub_id
HAVING COUNT(*) > 5下面是结果集:
pub_id total
------ -----------
0877 44219
1389 24941
(2 row(s) affected)理解应用 WHERE、GROUP BY 和 HAVING 子句的正确序列对编写高效的查询代码会有所帮助:
WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
GROUP BY 子句用来分组 WHERE 子句的输出。
HAVING 子句用来从分组的结果中筛选行。
对于可以在分组操作之前或之后应用的搜索条件,在 WHERE 子句中指定它们更有效。这样可以减少必须分组的行数。应当在 HAVING 子句中指定的搜索条件只是那些必须在执行分组操作之后应用的搜索条件。
Microsoft® SQL Server™ 2000 查询优化器可处理这些条件中的大多数。如果查询优化器确定 HAVING 搜索条件可以在分组操作之前应用,那么它就会在分组之前应用。查询优化器可能无法识别所有可以在分组操作之前应用的 HAVING 搜索条件。建议将所有这些搜索条件放在 WHERE 子句中而不是 HAVING 子句中。
以下查询显示包含聚合函数的 HAVING 子句。该子句按类型分组 titles 表中的行,并且消除只包含一本书的组:
USE pubs
SELECT type
FROM titles
GROUP BY type
HAVING COUNT(*) > 1下面是结果集:
type
------------------
business
mod_cook
popular_comp
psychology
trad_cook
(5 row(s) affected)以下是没有聚合函数的 HAVING 子句的示例。该子句按类型分组 titles 表中的行,并且消除不是以字母 p 开头的那些类型。
USE pubs
SELECT type
FROM titles
GROUP BY type
HAVING type LIKE 'p%'下面是结果集:
type
------------------
popular_comp
psychology
(2 row(s) affected)如果 HAVING 中包含多个条件,那么这些条件将通过 AND、OR 或 NOT 组合在一起。以下示例显示如何按出版商分组 titles,只包括那些标识号大于 0800、支付的总预付款已超过 $15,000 且销售书籍的平均价格小于 $20 的出版商。
SELECT pub_id, SUM(advance) AS AmountAdvanced,
AVG(price) AS AveragePrice
FROM pubs.dbo.titles
WHERE pub_id > '0800'
GROUP BY pub_id
HAVING SUM(advance) > $15000
AND AVG(price) < $20ORDER BY 可以用来为 GROUP BY 子句的输出排序。下面的示例显示使用 ORDER BY 子句以定义返回 GROUP BY 子句中的行的顺序:
SELECT pub_id, SUM(advance) AS AmountAdvanced,
AVG(price) AS AveragePrice
FROM pubs.dbo.titles
WHERE pub_id > '0800'
AND price >= $5
GROUP BY pub_id
HAVING SUM(advance) > $15000
AND AVG(price) < $20
ORDER BY pub_id DESC
以下示例返回分类 2 中每种产品已销售的单位数量:
USE Northwind
SELECT OrdD.ProductID AS ProdID,
SUM(OrdD.Quantity) AS AmountSold
FROM [Order Details] AS OrdD JOIN Products as Prd
ON OrdD.ProductID = Prd.ProductID
AND Prd.CategoryID = 2
GROUP BY OrdD.ProductID下面是结果集:
ProdID AmountSold
----------- -----------
3 328
4 453
5 298
6 301
8 372
15 122
44 601
61 603
63 445
65 745
66 239
77 791
(12 row(s) affected)GROUP BY 关键字后面跟着列的列表,称为分组列。GROUP BY 子句限制结果集中的行;对于分组列中的每个非重复值只有一行。每个结果集行都包含与其分组列中特定值相关的汇总数据。
当 SELECT 语句中包含 GROUP BY 关键字时,对可以在选择列表中指定的项有一些限制。在该选择列表中所允许的项目是:
分组列。
为分组列中的每个值只返回一个值的表达式,例如将列名作为其中一个参数的聚合函数。这些函数称为矢量聚合。
例如,TableX 包含:
ColumnA ColumnB ColumnC
------- ------- -------
1 abc 5
1 def 4
1 ghi 9
2 jkl 8
2 mno 3
如果 ColumnA 是分组列,则结果集中将有两行,其中一行汇总值 1 的信息,而另一行汇总值 2 的信息。
如果 ColumnA 是分组列,要引用 ColumnB 或 ColumnC,这两列必须能为 ColumnA 中的每个值返回单个值的聚合函数中的参数。选择列表中包含诸如 MAX (ColumnB)、SUM (ColumnC) 或 AVG (ColumnC) 之类的表达式是合法的:
SELECT ColumnA,
MAX(ColumnB) AS MaxB,
SUM(ColumnC) AS SumC
FROM TableX
GROUP BY ColumnA该选择语句返回两行,为 ColumnA 中的每个唯一值各返回一行:
ColumnA MaxB SumC
----------- ---- -----------
1 ghi 18
2 mno 11
(2 row(s) affected)但是,选择列表中只包含 ColumnB 表达式是不合法的:
SELECT ColumnA,
ColumnB,
SUM(ColumnC) AS SumC
FROM TableX
GROUP BY ColumnA由于 GROUP BY 关键字只能返回一行,该行 ColumnA 中的值为 1,因此无法返回与 ColumnA 中的值 1 关联的 ColumnB 的三个值(abc、def 和 ghi)。
不能对 ntext、text、image 或 bit 列使用 GROUP BY 或 HAVING 子句,除非它们所在的函数返回的值具有其它数据类型。这样的函数包括 SUBSTRING 和 CAST。
使用 HAVING 子句选择行
HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用;而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似,但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。
下面的查询得到本年度截止到目前的销售额超过 $40,000 的出版商:
USE pubs
SELECT pub_id, total = SUM(ytd_sales)
FROM titles
GROUP BY pub_id
HAVING SUM(ytd_sales) > 40000下面是结果集:
pub_id total
------ -----------
0877 44219
(1 row(s) affected)为了确保对每个出版商的计算中至少包含六本书,下面示例使用 HAVING COUNT(*) > 5 消除返回的总数小于六本书的出版商:
USE pubs
SELECT pub_id, total = SUM(ytd_sales)
FROM titles
GROUP BY pub_id
HAVING COUNT(*) > 5下面是结果集:
pub_id total
------ -----------
0877 44219
1389 24941
(2 row(s) affected)理解应用 WHERE、GROUP BY 和 HAVING 子句的正确序列对编写高效的查询代码会有所帮助:
WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
GROUP BY 子句用来分组 WHERE 子句的输出。
HAVING 子句用来从分组的结果中筛选行。
对于可以在分组操作之前或之后应用的搜索条件,在 WHERE 子句中指定它们更有效。这样可以减少必须分组的行数。应当在 HAVING 子句中指定的搜索条件只是那些必须在执行分组操作之后应用的搜索条件。
Microsoft® SQL Server™ 2000 查询优化器可处理这些条件中的大多数。如果查询优化器确定 HAVING 搜索条件可以在分组操作之前应用,那么它就会在分组之前应用。查询优化器可能无法识别所有可以在分组操作之前应用的 HAVING 搜索条件。建议将所有这些搜索条件放在 WHERE 子句中而不是 HAVING 子句中。
以下查询显示包含聚合函数的 HAVING 子句。该子句按类型分组 titles 表中的行,并且消除只包含一本书的组:
USE pubs
SELECT type
FROM titles
GROUP BY type
HAVING COUNT(*) > 1下面是结果集:
type
------------------
business
mod_cook
popular_comp
psychology
trad_cook
(5 row(s) affected)以下是没有聚合函数的 HAVING 子句的示例。该子句按类型分组 titles 表中的行,并且消除不是以字母 p 开头的那些类型。
USE pubs
SELECT type
FROM titles
GROUP BY type
HAVING type LIKE 'p%'下面是结果集:
type
------------------
popular_comp
psychology
(2 row(s) affected)如果 HAVING 中包含多个条件,那么这些条件将通过 AND、OR 或 NOT 组合在一起。以下示例显示如何按出版商分组 titles,只包括那些标识号大于 0800、支付的总预付款已超过 $15,000 且销售书籍的平均价格小于 $20 的出版商。
SELECT pub_id, SUM(advance) AS AmountAdvanced,
AVG(price) AS AveragePrice
FROM pubs.dbo.titles
WHERE pub_id > '0800'
GROUP BY pub_id
HAVING SUM(advance) > $15000
AND AVG(price) < $20ORDER BY 可以用来为 GROUP BY 子句的输出排序。下面的示例显示使用 ORDER BY 子句以定义返回 GROUP BY 子句中的行的顺序:
SELECT pub_id, SUM(advance) AS AmountAdvanced,
AVG(price) AS AveragePrice
FROM pubs.dbo.titles
WHERE pub_id > '0800'
AND price >= $5
GROUP BY pub_id
HAVING SUM(advance) > $15000
AND AVG(price) < $20
ORDER BY pub_id DESC
发表评论
-
转:避免全表扫描的sql优化
2015-02-05 16:37 1286http://tech.diannaodian.com/dw/ ... -
给表加上聚集索引和非聚集索引
2014-12-11 17:28 661创建简单非聚集索引 以下示例为 Purchasing.Pro ... -
查看表中索引语句
2014-12-11 10:37 648SELECT object_name(object_id) ... -
SQL2008一行转多行的精典写法
2013-08-28 12:06 384--创建测试表 create table t1( id ... -
在sql server中利用with as实现递归功能
2013-06-25 10:48 500在sqlserver2005之前,要实现递归功能比较麻烦,比如 ... -
SQL 2005 with(nolock)详解
2013-06-17 17:03 560大家在写查询时, ... -
用SQL语句查询每门成绩都大于80的学生姓名
2013-05-29 10:39 1684昨天遇到的一个SQL面试题,感觉很有趣。 表名stu 结构 ... -
SqlServer强制断开数据库已有连接的方法
2013-02-22 11:03 1599在master数据库中执行如下代码 declare @i IN ... -
Sql获取第一天、最后一天
2013-01-03 14:56 9613① 本月第一天(--减去今天再加上1天) SELECT DAT ... -
表函数与游标
2012-12-25 16:07 714--建立数据源 create table tb1(zm ch ... -
sql server 2005 输出表的函數用法
2012-12-25 16:02 804view sourceprint? 01 --sql ser ... -
SQL杂谈
2012-12-14 17:33 6771、如何用convert什么的把带有时分秒的日期转为时分秒都0 ... -
用FOR XML PATH将查询结果以XML输出
2012-09-20 17:54 1010本文从此而来 http://www.cnblogs.com/d ... -
left join on and
2012-09-20 10:23 845数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临 ... -
筛选索引简单使用
2012-08-17 11:29 1121对于在强制实施数据完整性时的一种常见需求,也可以用筛选索引来解 ... -
排名函数(ROW_NUMBER、RANK、DENSE_RANK)及OVER子句
2012-08-10 10:36 6591--在部门内新水排名(如果有相同名次,用dense_rank) ... -
SQL SERVER 中行列转换 PIVOT UNPIVO
2012-08-18 15:34 2293PIVOT用于将列值旋转为列名、同时执行聚合运算(即行转列), ... -
Apply简单使用举例
2012-08-09 17:20 984APPLY APPLY运算符把右表表达式应用左输入中 ... -
SQL Server 2008语句大全完整版
2012-08-09 10:39 1361--======================== ... -
Not Exists练习
2012-07-23 14:40 859IF OBJECT_ID(N'a') IS NOT NU ...
相关推荐
李兴华老师ORACLE数据库,分组统计和子查询专题深入详解
举例说明:如果要用到group by 一般用到的就是“每”这个字, 例如现在有一个这样的需求:查询每个部门有多少人。就要用到分组的技术 select DepartmentID as '部门名称',COUNT(*) as '个数' from ...
group by 从英文里理解就是分组。必须有“聚合函数”来配合才能使用,使用时至少需要一个分组标志字段。 什么是“聚合函数”? 像sum()、count()、avg()等都是“聚合函数” 使用group by 的目的就是要将数据分类...
类似于关系型数据库中的 SUM,AVG, GROUP BY 等,Elasticsearch 也提供了丰富的聚合运算方式,可以满足大部分分析和查询场景。 Doc Values 和 Field Data 在学习聚合分析之前,我们先了解一下 Doc Values 和 Field ...
8.3.1 GROUP BY子句创建分组 8.3.2 GROUP BY子句根据多列组合行 8.3.3 ROLLUP运算符和CUBE运算符 8.3.4 GROUP BY子句中的NULL值处理 8.3.5 HAVING子句 8.3.6 HAVING子句与WHERE子句 8.3.7 SELECT...
1.3.3 步骤3:GROUP BY阶段 1.3.4 步骤4:HAVING阶段 1.3.5 步骤5:SELECT阶段 1.3.6 步骤6:排序用的ORDER BY阶段 1.4 逻辑查询处理的深入内容 1.4.1 表运算符 1.4.2 OVER子句 1.4.3 集合运算符 1.5 总结 ...
6.2.4 使用别名 6.3 排序与分组 6.3.1 升序排序与降序排序 6.3.2 多列字段排序 6.3.3 使用GROUPBY子句对查询结果进行分组 6.3.4 HAVING子句的应用 6.4 条件查询 6.4.1 WHERE单条件语句查询 6.4.2 运算符查询 6.4.3 ...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
8.3.1 group by子句创建分组 149 8.3.2 group by子句根据多列组合行 150 8.3.3 rollup运算符和cube运算符 151 8.3.4 group by子句中的null值处理 153 8.3.5 having子句 153 8.3.6 having子句与where子句 154 ...
1.3.3 步骤3:GROUP BY阶段10 1.3.4 步骤4:HAVING阶段11 1.3.5 步骤5:SELECT阶段12 1.3.6 步骤6:排序用的ORDER BY阶段13 1.4 逻辑查询处理的深入内容16 1.4.1 表运算符16 1.4.2 OVER子句23 1.4.3 集合...
聚合类似于SQL的GROUP BY子句,其中用户选择预定义的聚合函数以将多个记录合并为单个结果。但是在TSD中,每个时间戳和组聚合一组记录。 每个聚合器都有两个组件: 功能 - 应用的数学计算,例如对所有值求和,计算...
4.5 DQL之分组查询group by 4.6 DQL之排序查询order by 4.7 DQL之分页查询limit 4.8 DQL之结果保存 第五章:多表复杂分析查询 5.1 多表查询:表与表之间的关系 5.2 多表查询:Join关联 5.3 多表查询:子查询 第六...
4.3.3 空值与GROUP BY和ORDER BY 112 4.3.4 空值与聚合函数 114 4.4 小结 114 第5章 关于问题 116 5.1 问出好的问题 116 5.2 提问的目的 117 5.3 问题的种类 117 5.4 关于问题的问题 119 5.5 关于数据的问题...
3、支持分组分页功能,可以完成Group by、Order By 等复杂的SQL语句所实现的强大功能;4、支持标准的数据库连接;5、同时支持DataGrid、DataList、Repeater三大.Net数据列表;6、进行数据绑定时只需几条语句,大大...
14.4.7 使用GroupBy分组结果 415 14.4.8 使用GroupJoin()实现一对多关系 416 14.4.9 调用Selectmany() 418 14.4.10 更多标准查询操作符 420 14.5 小结 423 第15章 使用查询表达式的LINQ 425 ...
1 SQL基础 1.1 基本概念 结构化查询语言(Structured Query ...select后面出现的列,如果没有使用集合函数,必须出现在group by 中。 select sno,sname,sum(grade) from student group by sno,sname; //合法写法 select...
9.7 分组查询,统计函数(GROUP BY) 9.8 修改数据(INSERT、UPDATE和DELETE) 9.9 创建数据表、数据库和索引 第10章 SQL解决方案 10.1 字符串 10.2 日期和时间 10.3 ENUM和SET数据类型 10.4 变量与...
4.9 groupby和having子句 4.9.1 groupby 4.9.2 having 4.10 子查询:简单子查询和带连接的相关比较 4.10.1 简单子查询 4.10.2 带连接的相关子查询 4.11 集合操作符:union、intersect和minus 4.11.1 union 4.11.2 ...