Oracle_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
代码
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164943732-492513430.png)
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164944607-1795808478.png)
create table testScore
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164945029-1467464785.png)
(
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164945326-16992752.png)
tid int primary key identity(1,1),
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164946217-957566359.png)
tname varchar(30) null,
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164947170-196791003.png)
ttype varchar(10) null,
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164947623-878973302.png)
tscor int null
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164948076-1462611222.png)
)
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164948498-132002586.png)
go
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164948873-1189093255.png)
---插入数据insert into testScore values ('张三','语文',80)
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164949904-1440112449.png)
insert into testScore values ('张三','数学',98)
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164950420-1386246598.png)
insert into testScore values ('张三','英语',65)
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164950857-1060700627.png)
insert into testScore values ('李四','语文',70)
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164951170-546697115.png)
insert into testScore values ('李四','数学',80)
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164951467-1472208668.png)
insert into testScore values ('李四','英语',90)
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164951826-516252737.png)
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164952123-1335129015.png)
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164952842-1534962613.png)
select tname as '姓名' ,
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164953170-659543313.png)
max(case ttype when '语文' then tscor else 0 end) '语文',
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164953498-1846723832.png)
max(case ttype when '数学' then tscor else 0 end) '数学',
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164953732-1310542215.png)
max(case ttype when '英语' then tscor else 0 end) '英语'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164954248-1709746610.png)
from testScore
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164954936-1314608437.png)
group by tname
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164955201-149853599.png)
实例二
有如下数据:(为了看得更清楚,我并没有使用国家代码,而是直接用国家名作为Primary Key)
国家(country) | 人口(population) |
中国 | 600 |
美国 | 100 |
加拿大 | 100 |
英国 | 200 |
法国 | 300 |
日本 | 250 |
德国 | 200 |
墨西哥 | 50 |
印度 | 250 |
根据这个国家人口数据,统计亚洲和北美洲的人口数量。应该得到下面这个结果。
洲 | 人口 |
亚洲 | 1100 |
北美洲 | 250 |
其他 | 700 |
代码
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164955467-125155215.png)
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164955748-392034396.png)
SELECT SUM(population),
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164956045-719029060.png)
CASE country
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164956264-199989176.png)
WHEN '中国' THEN '亚洲'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164956529-1987894689.png)
WHEN '印度' THEN '亚洲'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164956795-1664739946.png)
WHEN '日本' THEN '亚洲'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164957045-1929781961.png)
WHEN '美国' THEN '北美洲'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164957342-1760364060.png)
WHEN '加拿大' THEN '北美洲'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164957607-515026499.png)
WHEN '墨西哥' THEN '北美洲'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164957904-1854609535.png)
ELSE '其他' END
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164958436-1121451969.png)
FROM Table_A
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312164959139-2042031267.png)
GROUP BY CASE country
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165000045-1282027100.png)
WHEN '中国' THEN '亚洲'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165000732-1201862874.png)
WHEN '印度' THEN '亚洲'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165001326-226105144.png)
WHEN '日本' THEN '亚洲'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165001936-648831327.png)
WHEN '美国' THEN '北美洲'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165002529-22603504.png)
WHEN '加拿大' THEN '北美洲'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165003076-1199642589.png)
WHEN '墨西哥' THEN '北美洲'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165003451-1530891475.png)
ELSE '其他' END;
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165003842-1520318232.png)
同样的,我们也可以用这个方法来判断工资的等级,并统计每一等级的人数。SQL代码如下;
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165004139-1990415519.png)
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165004436-1550978825.png)
SELECT
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165004764-63523147.png)
CASE WHEN salary <= 500 THEN '1'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165005092-209185131.png)
WHEN salary > 500 AND salary <= 600 THEN '2'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165005561-2000150800.png)
WHEN salary > 600 AND salary <= 800 THEN '3'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165005811-1784025716.png)
WHEN salary > 800 AND salary <= 1000 THEN '4'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165006045-152618326.png)
ELSE NULL END salary_class,
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165006357-1234478522.png)
COUNT(*)
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165006842-222010944.png)
FROM Table_A
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165007342-928240979.png)
GROUP BY
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165007732-2064954361.png)
CASE WHEN salary <= 500 THEN '1'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165007998-103971962.png)
WHEN salary > 500 AND salary <= 600 THEN '2'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165008264-2014220179.png)
WHEN salary > 600 AND salary <= 800 THEN '3'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165008795-1527136210.png)
WHEN salary > 800 AND salary <= 1000 THEN '4'
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165009076-366121740.png)
ELSE NULL END;
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165009295-977539843.png)
对于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 |
代码
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165010076-1392061469.png)
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165010373-602169666.png)
SELECT country,
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165010748-269354673.png)
SUM( CASE WHEN sex = '1' THEN
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165011545-55008892.png)
population ELSE 0 END), --男性人口SUM( CASE WHEN sex = '2' THEN
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165012045-1980827442.png)
population ELSE 0 END) --女性人口FROM Table_A
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165012826-185830848.png)
GROUP BY country;
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165013217-1082188701.png)
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表中查询至少有两位教师的系及教师人数。
实现代码:
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165013451-2041280989.png)
SELECT DNAME, COUNT(*) AS num_teacher
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165014092-94191728.png)
FROM TEACHER
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165014795-692894933.png)
GROUP BY DNAME
![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165015311-1040515917.png)
HAVING COUNT(*)>=2![](https://images2015.cnblogs.com/blog/1008304/201703/1008304-20170312165016123-1259645226.png)
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子句的输入。