sql分组查询 group by having（Oracle 10g宝典第六章读书笔记）

6.2.2、分组查询

在使用group by关键字时，在select列表中可以指定的项目是有限制的，select语句中仅许以下几项：

〉被分组的列
〉为每个分组返回一个值得表达式，例如用一个列名作为参数的聚合函数

group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面

6.2.2.1、组处理函数

组处理函数有时也成为组合函数，作用于查询出得数据组（即多行数据的组），返回一个统计结果。一般情况下，组处理函数与 group by 子句联合使用，否则将查询出的所有数据当成一个组。

MAX      最大，时间最晚

MIN       最小，时间最早

AVG      平均

SUM              总和

COUNT         非NULL行的行数

VARIANCE   列表表达式的方差

STDDEV       列表表达式的标准偏差

1、组处理函数只能出现在选择列表、ORDER BY 子句、having子句中，而不能出现在 where 子句和 group by 子句中。

2、除了 count 以外，其他所有的函数都会忽略 NULL行

3、如果选择列表同时包含  列、表达式、和组处理函数，则这些列、表达式、都必须出现在 group by子句中。

4、在组处理函数中可以指定all 和distinct 选项。其中 all是默认选项，表示统计所有行（包含重复的行），而 distinct 则 只会统计不同的行。

6.2.2.2、 group by 子句

成组地处理数据。

告诉数据库如何将查询出的数据进行分组，然后数据库才知道将组处理函数作用于这些已经分好的组。

1、按单列分组

基于该列不同的值，分成不同的组，然后进行统计。

group by实例

实例一

数据表：

姓名 科目 分数
张三 语文 80
张三 数学 98
张三 英语 65
李四 语文 70
李四 数学 80
李四 英语 90

期望查询结果：

姓名 语文 数学 英语
张三 80 98 65
李四 70 80 90

代码

 

SQL 代码   复制

create table testScore    
(    
   tid int primary key identity(1,1),    
   tname varchar(30) null,    
   ttype varchar(10) null,    
   tscor int null   
)    
go    
---插入数据    
insert into testScore values ('张三','语文',80)    
insert into testScore values ('张三','数学',98)    
insert into testScore values ('张三','英语',65)    
insert into testScore values ('李四','语文',70)    
insert into testScore values ('李四','数学',80)    
insert into testScore values ('李四','英语',90)    


select tname as '姓名' ,     
max(case ttype when '语文' then tscor else 0 end) '语文',     
max(case ttype when '数学' then tscor else 0 end) '数学',     
max(case ttype when '英语' then tscor else 0 end) '英语'     
from testScore     
group by tname

实例二

 

有如下数据:(为了看得更清楚，我并没有使用国家代码，而是直接用国家名作为Primary Key)

 

国家（country）	人口（population）
中国	600
美国	100
加拿大	100
英国	200
法国	300
日本	250
德国	200
墨西哥	50
印度	250

根据这个国家人口数据，统计亚洲和北美洲的人口数量。应该得到下面这个结果。
 

洲	人口
亚洲	1100
北美洲	250
其他	700

 

代码

 

SQL 代码   复制

SELECT  SUM(population),
        CASE country
                WHEN '中国'     THEN '亚洲'
                WHEN '印度'     THEN '亚洲'
                WHEN '日本'     THEN '亚洲'
                WHEN '美国'     THEN '北美洲'
                WHEN '加拿大'  THEN '北美洲'
                WHEN '墨西哥'  THEN '北美洲'
        ELSE '其他' END
FROM    Table_A
GROUP BY CASE country
                WHEN '中国'     THEN '亚洲'
                WHEN '印度'     THEN '亚洲'
                WHEN '日本'     THEN '亚洲'
                WHEN '美国'     THEN '北美洲'
                WHEN '加拿大'  THEN '北美洲'
                WHEN '墨西哥'  THEN '北美洲'
        ELSE '其他' END;

同样的，我们也可以用这个方法来判断工资的等级，并统计每一等级的人数。SQL代码如下；

 

SQL 代码   复制

SELECT
        CASE WHEN salary <= 500 THEN '1'
             WHEN salary > 500 AND salary <= 600  THEN '2'
             WHEN salary > 600 AND salary <= 800  THEN '3'
             WHEN salary > 800 AND salary <= 1000 THEN '4'
        ELSE NULL END salary_class,
        COUNT(*)
FROM    Table_A
GROUP BY
        CASE WHEN salary <= 500 THEN '1'
             WHEN salary > 500 AND salary <= 600  THEN '2'
             WHEN salary > 600 AND salary <= 800  THEN '3'
             WHEN salary > 800 AND salary <= 1000 THEN '4'
        ELSE NULL END;

对于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

 

代码

 

SQL 代码   复制

SELECT country,
       SUM( CASE WHEN sex = '1' THEN 
                      population ELSE 0 END),  --男性人口
       SUM( CASE WHEN sex = '2' THEN 
                      population ELSE 0 END)   --女性人口
FROM  Table_A
GROUP BY country;

GROUP BY子句中的NULL值处理

当GROUP BY子句中用于分组的列中出现NULL值时，将如何分组呢？SQL中，NULL不等于NULL（在WHERE子句中有过介绍）。然而，在GROUP BY子句中，却将所有的NULL值分在同一组，即认为它们是“相等”的。

2、按多列分组

基于这些列的不同组合的值分成不同的组，然后进行统计。

3、ROLLUP 和 CUBE 限定词

rollup可生成 横向 统计结果，

cube 可生成 纵向统计 结果。

比如

从 公司员工 表中 查出每个部门，每种岗位平均工资和最高工资及其横向统计结果。

select 部门编号，工种，avg(工资)，max（sal） from 公司员工 group by rollup(部门编号,工种);

6.2.2.3、having 子句。

可用 having 来限制（过滤）分组， having 子句必须跟在 group by 子句 之后。

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表中查询至少有两位教师的系及教师人数。

实现代码：

 

SQL 代码   复制

SELECT DNAME, COUNT(*) AS num_teacher

FROM TEACHER

GROUP BY DNAME

HAVING COUNT(*)>=2

 

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子句的输入。