SQL技巧（4）分组的定义和性质

（Molinaro-SQL cookbook笔记）

给部门10的员工分组，且返回组中成员数，最高工资和最低工资

select deptno, count(*) as cnt, max(sal) as hi_sal, min(sal) as lo_sal
from emp
where deptno = 10
group by deptno
           

SQL组的定义

把SQL组定义为（G, e），其中G是使用GROUP BY的单个查询或自包含查询的结果集，e是G的一个成员，而且必须满足下列规则：

（1）对与G中的每个e都是独特（各不相同）的，而且表示e的一个或多个实例

（2）对于G中的每个e，聚集函数COUNT会返回一个大于0的值

注：在上述规则中用“行”替换“e“将非常准确，因为结果集里的行就代表了组。

性质1：组是非空的

根据组的定义，不能从空表中创建组，创建如下空表

create table fruits (name varchar(10))
           

采用以下三种方法对这个空表创建组

select name
from fruits
group by name
           

select conut(*) as cnt
from fruits
group by name
           

select name, count(*) as cnt
from fruits
group by name
           

结果都是（no rows selected)，从空表中创建SQL的组是不可能的

性质2：组是独特的

insert into fruits values ('Oranges')
insert into fruits values ('Oranges')
insert into fruits values ('Oranges')
insert into fruits values ('Apple')
insert into fruits values ('Peach')
           

select name 
from fruits
group by name
           

NAME

Apple

Orange

Peach

select name, count(*) as cnt
from fruits
group by name
           

NAME	CNT
Apple	1
Oranges	3
Peach	1

所以在查询中使用GROUP BY时，SELECT列表中就不必使用DISTINCT

悖论：

由于SQL结果集和表的性质，可能可以返回一个包含重复组的结果集

例1：

select coalesce(name, 'NULL') as name, count(*) as cnt
from fruits
group by name
union all
select coalesce(name, 'NULL') as name, count(*) as cnt
from fruits
group by name
           

NAME	CNT
Apple	1
Oranges	3
Peach	1
NULL	3
Apple	1
Oranges	3
Peach	1
NULL	3

对于这种情况，每个GROUP BY生成的结果集符合组的定义，只有合并两个GROUP BY查询结果集时，才会创建包含重复组的多集，上述查询中使用了UNION ALL，它不是一个集合操作，也调用了两次GROUP BY。

例2：

select x.*
from (
       select coalesce(name, 'NULL') as name, count(*) as cnt
       from fruits
       group by name
     )x,
     (select deptno from dept) y
           

NAME	CNT
Apple	1
Apple	1
Apple	1
Apple	1
Oranges	3
Oranges	3
Oranges	3
Oranges	3
Peach	1
Peach	1
Peach	1
Peach	1
NULL	3
NULL	3
NULL	3
NULL	3

此查询中使用了笛卡尔积，只有先实现分组，再执行笛卡尔积才会起作用。其实两个例子都没有对SQL组的定义造成任何破坏

性质3：每组的COUNT绝不为0

注：此处指包含GROUP BY的COUNT，而不是使用COUNT本身

悖论:

SQL中NULL是合法的”值“，由于聚集函数会忽略NULL，所以当一个表只包含一行，而且它的值是NULL，那么在GROUP BY查询时COUNT会返回什么呢？

insert into fruits values (null)
insert into fruits values (null)
insert into fruits values (null)
           

select coalesce(name, 'NULL') as name, count(name) as cnt
from fruits
group by name
           

NAME	CNT
Apple	1
NULL
Oranges	3
Peach	1

使用COUNT(*)代替COUNT(NAME)可避免NULL组悖论，如果传递给聚集函数的列中存在NULL值，将忽略这些值。传递*时COUNT对行进行技术，而不对实际列值进行技术。

SELECT和GROUP BY的关系

当使用聚集函数时，对于SELECT列表中的项，如果没有作为聚集函数的参数，那么它必须是组的一部分：

select deptno, count(*) as cnt
from emp
group by deptno
           

一定要在GROUP BY子句中列出DEPTNO

由用户定义的函数，窗口函数和非关联的标量子查询返回的常量、标量值都是此规则的例外

由于SELECT子句是GROUP BY子句之后进行计算的，所以SELECT 列表允许这些结构，而且不必（某些情况下不能）在GROUP BY子句中指定

select 'hello' as msg, 
       1 as num, deptno, 
       (select count(*) from emp) as total, 
       count(*) as cnt
from emp
group by deptno
           

上述查询中SELECT列表中没有包含在GROUP BY子句中项不会影响各DEPTNO的CNT值，也不会改变DEPTNO值。

所以，准确的匹配规则是： 对于可能会更改组（或更改聚集函数返回值）的SELECT列表项，一定要包含在GROUP BY子句中那么什么会改变组及聚集函数返回值呢？答案是 SELECT对象表的其他列

如果SELECT列表中只有聚集函数，那么可以在GROUP BY子句中列出任意想要的有效列：

select count(*)
from emp
group by deptno
           

COUNT(*)

3

5

6

select count(*)
from emp
group by deptno, job
           

COUNT(*)
1
1
1
2
2
1
1
1
4

注：有可能DEPTNO和JOB的值都不是独特的，但二者的组合是独特的