(转)sql的group by应用
转载于:http://www.studyofnet.com/news/247.html
本文导读:在实际SQL应用中,经常需要进行分组聚合,即将查询对象按一定条件分组,然后对每一个组进行聚合分析。创建分组是通过GROUP BY子句实现的。与WHERE子句不同,GROUP BY子句用于归纳信息类型,以汇总相关数据。GROUP BY的作用是通过一定的规则将一个数据集划分成若干个小的区域,然后针对若干个小区域进行数据处理。
在SQL Server中使用的分组查询是ORDER BY子句,使用ORDER BY子句要同聚合函数配合使用才能完成分组查询,在SELECT查询的字段中如果字段没有使用聚合函数就必须出现在ORDER BY子句中(即SELECT后边的字段名要么出现在聚合函数中,要么在ORDER BY子句中使用)
在分组查询中还可以配合使用HAVING子句,定义查询条件。
使用group by进行分组查询
在使用group by关键字时,在select列表中可以指定的项目是有限制的,select语句中仅许以下几项:
〉被分组的列
〉为每个分组返回一个值得表达式,例如用一个列名作为参数的聚合函数
group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面(重要)
group by实例
实例一
数据表:
姓名 科目 分数
张三 语文 80
张三 数学 98
张三 英语 65
李四 语文 70
李四 数学 80
李四 英语 90
期望查询结果:
姓名 语文 数学 英语
张三 80 98 65
李四 70 80 90
代码
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
create table testScore
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
(
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
tid int primary key identity(1,1),
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
tname varchar(30) null,
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
ttype varchar(10) null,
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
tscor int null
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
)
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
go
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
---插入数据
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
insert into testScore values ('张三','语文',80)
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
insert into testScore values ('张三','数学',98)
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
insert into testScore values ('张三','英语',65)
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
insert into testScore values ('李四','语文',70)
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
insert into testScore values ('李四','数学',80)
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
insert into testScore values ('李四','英语',90)
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
select tname as '姓名' ,
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
max(case ttype when '语文' then tscor else 0 end) '语文',
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
max(case ttype when '数学' then tscor else 0 end) '数学',
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
max(case ttype when '英语' then tscor else 0 end) '英语'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
from testScore
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
group by tname
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
实例二
有如下数据:(为了看得更清楚,我并没有使用国家代码,而是直接用国家名作为Primary Key)
国家(country) | 人口(population) |
中国 | 600 |
美国 | 100 |
加拿大 | 100 |
英国 | 200 |
法国 | 300 |
日本 | 250 |
德国 | 200 |
墨西哥 | 50 |
印度 | 250 |
根据这个国家人口数据,统计亚洲和北美洲的人口数量。应该得到下面这个结果。
洲 | 人口 |
亚洲 | 1100 |
北美洲 | 250 |
其他 | 700 |
代码
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
SELECT SUM(population),
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
CASE country
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN '中国' THEN '亚洲'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN '印度' THEN '亚洲'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN '日本' THEN '亚洲'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN '美国' THEN '北美洲'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN '加拿大' THEN '北美洲'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN '墨西哥' THEN '北美洲'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
ELSE '其他' END
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
FROM Table_A
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
GROUP BY CASE country
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN '中国' THEN '亚洲'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN '印度' THEN '亚洲'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN '日本' THEN '亚洲'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN '美国' THEN '北美洲'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN '加拿大' THEN '北美洲'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN '墨西哥' THEN '北美洲'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
ELSE '其他' END;
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
同样的,我们也可以用这个方法来判断工资的等级,并统计每一等级的人数。SQL代码如下;
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
SELECT
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
CASE WHEN salary <= 500 THEN '1'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN salary > 500 AND salary <= 600 THEN '2'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN salary > 600 AND salary <= 800 THEN '3'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN salary > 800 AND salary <= 1000 THEN '4'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
ELSE NULL END salary_class,
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
COUNT(*)
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
FROM Table_A
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
GROUP BY
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
CASE WHEN salary <= 500 THEN '1'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN salary > 500 AND salary <= 600 THEN '2'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN salary > 600 AND salary <= 800 THEN '3'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
WHEN salary > 800 AND salary <= 1000 THEN '4'
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
ELSE NULL END;
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
对于groupby后面一般都是跟一个列名,但在该例子中通过case语句使分组变得跟强大了。
实例三
有如下数据
国家(country) | 性别(sex) | 人口(population) |
中国 | 1 | 340 |
中国 | 2 | 260 |
美国 | 1 | 45 |
美国 | 2 | 55 |
加拿大 | 1 | 51 |
加拿大 | 2 | 49 |
英国 | 1 | 40 |
英国 | 2 | 60 |
按照国家和性别进行分组,得出结果如下
国家 | 男 | 女 |
中国 | 340 | 260 |
美国 | 45 | 55 |
加拿大 | 51 | 49 |
英国 | 40 | 60 |
代码
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
SELECT country,
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
SUM( CASE WHEN sex = '1' THEN
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
population ELSE 0 END), --男性人口
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
SUM( CASE WHEN sex = '2' THEN
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
population ELSE 0 END) --女性人口
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
FROM Table_A
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
GROUP BY country;
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
GROUP BY子句中的NULL值处理
当GROUP BY子句中用于分组的列中出现NULL值时,将如何分组呢?SQL中,NULL不等于NULL(在WHERE子句中有过介绍)。然而,在GROUP BY子句中,却将所有的NULL值分在同一组,即认为它们是“相等”的。
HAVING子句
GROUP BY子句分组,只是简单地依据所选列的数据进行分组,将该列具有相同值的行划为一组。而实际应用中,往往还需要删除那些不能满足条件的行组,为了实现这个功能,SQL提供了HAVING子句。语法如下。
SELECT column, SUM(column)
FROM table
GROUP BY column
HAVING SUM(column) condition value
说明:HAVING通常与GROUP BY子句同时使用。当然,语法中的SUM()函数也可以是其他任何聚合函数。DBMS将HAVING子句中的搜索条件应用于GROUP BY子句产生的行组,如果行组不满足搜索条件,就将其从结果表中删除。
HAVING子句的应用
从TEACHER表中查询至少有两位教师的系及教师人数。
实现代码:
SELECT DNAME, COUNT(*) AS num_teacher
FROM TEACHER
GROUP BY DNAME
HAVING COUNT(*)>=2
![](http://www.studyofnet.com/Codefan-Controls/OutliningIndicators/None.gif)
HAVING子句与WHERE子句的区别
HAVING子句和WHERE子句的相似之处在于,它也定义搜索条件。但与WHERE子句不同,HAVING子句与组有关,而不是与单个的行有关。
1、如果指定了GROUP BY子句,那么HAVING子句定义的搜索条件将作用于这个GROUP BY子句创建的那些组。
2、如果指定WHERE子句,而没有指定GROUP BY子句,那么HAVING子句定义的搜索条件将作用于WHERE子句的输出,并把这个输出看作是一个组。
3、如果既没有指定GROUP BY子句也没有指定WHERE子句,那么HAVING子句定义的搜索条件将作用于FROM子句的输出,并把这个输出看作是一个组。
4、在SELECT语句中,WHERE和HAVING子句的执行顺序不同。在本书的5.1.2节介绍的SELECT语句的执行步骤可知,WHERE子句只能接收来自FROM子句的输入,而HAVING子句则可以接收来自GROUP BY子句、WHERE子句和FROM子句的输入。