SQL高级查询之分组查询

1. GROUP BY

假如有如下一张表inoutinfo，

status	number	spend
正在车场	粤BS8120	10
已出车场	粤BS8120	15
已出车场	粤BS8120	20
正在车场	粤BS8121	25
已出车场	粤BS8121	30
已出车场	粤BS8121	35

查询同辆车的进出记录（GROUP BY + 单个字段）

select number as 车牌号, count(*) as 数量
from inoutinfo
Group by number

测试结果：

解读：1. 这条语句的执行顺序：查询inoutinfo中的记录-按照number分组-输出number和count(*)这两个字段；
2. 输出的字段，必须要在Group by子句或包含在聚合函数中，例如下面的语句将会报错，

--报错：选择列表中的列 'inoutinfo.spend' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中
select number as 车牌号, count(*), spend as 数量
from inoutinfo
Group by number

那么，为什么会这样呢？
其实，道理很简单，我们根据number分组，可以分成2组（“粤BS8120”“粤BS8121”），说明最终显示只有两笔记录。因此对于所有输出字段来说，要么有出现在Group by子句中，将记录汇总显示，要么通过聚合函数将记录处理成单值显示。
常见的聚合函数有：count，sum，avg，max，min；

查询同辆车的不同状态下的进出记录（GROUP BY + 多个字段）

select number 车牌号, status 状态, SUM(spend) 费用
from inoutinfo
group by number, status

测试结果：

解读：1. 分组顺序是按照group by后面的字段顺序依次分组，例如先按照number分成“粤BS8120”“粤BS8121”，然后按照status将之前的每一个组又分成两组，“已出车场”“正在车场”：所以最终有四组：“粤BS8120 + 已出车场”，“粤BS8120 + 正在车场”，“粤BS8121 + 已出车场”，“粤BS8121 + 正在车场”，然后根据分组，通过聚合函数sum统计费用；
2. 为了显示清楚，建议输出字段的顺序和GROUP BY子句中字段的顺序要保持一致；
3. GROUP BY后面字段的顺序，不影响最终的分组结果。

2. GROUP BY ALL

在GROUP BY的后面加上一个ALL，表示对通过WHERE子句查询得到的结果集进行分组查询后，依然显示被WHERE子句过滤掉的记录，但是其他通过聚合函数统计的数据都会显示0或NULL。

select number 车牌号, COUNT(*) 数量, SUM(spend) 费用
from inoutinfo
where number = '粤BS8120'
group by all number

测试结果：

解读：1. where子句在group by子句之前执行，因此粤BS8121的相关记录在分组前已经被过滤掉了，但是在结果集中还是显示出来了，只是对应的数量和费用显示为0和NULL。

3. GROUP BY WITH CUBE

GROUP BY 后面加上CUBE运算符后，结果集会在GROUP BY分组统计的结果集基础上，增加归纳各个单独字段和相互混合字段后统计得到的多维交叉结果集。下面我们用几个例子来说明，

单个字段的分组查询

select number 车牌号, COUNT(*) 数量
from inoutinfo
group by number with cube

测试结果：

解读：1. 与之间没有加CUBE的结果集对比，发现多了一笔记录（NULL，6），这就是通过归纳车牌号这个字段后，统计得到的记录， 这里的归纳称为“小计”，用NULL填充；
2. 其实上面的那个NULL易于统计数据中本身包含的NULL值混淆，所以我们可以采用grouping这个函数解决这个问题：grouping作用于归纳出来的NULL，返回1，作用于查询得到的NULL，返回0。修正后的代码如下，

select 
case
when(grouping(number)= 1) then '小计' 
else number
end 车牌号, COUNT(*) 数量
from inoutinfo
group by number with cube

测试结果：

两个字段的分组查询

select 
number 车牌号, status 状态, COUNT(*) 数量
from inoutinfo
group by number, status with cube

测试结果：

解读：1. 通过前面可知，GROUP BY分组会产生四笔记录，对应上图中的，1、2、4、5。通过归纳车牌号，得到3和6两笔记录；通过归纳状态得到8和9两笔记录；通过归纳车牌号和状态得到7这笔记录；
2. GROUP BY后面字段的顺序不影响分组结果。

4. GROUP BY WITH ROLLUP

ROLLUP与CUBE作用一样，都是在GROUP BY的基础上，进一步归纳统计数据，但是ROLLUP比CUBE的限制要多。它不会将所有的字段混合归纳，而是根据GROUP BY后面字段的顺序，从高等级字段向低等级字段归纳，因此GROUP BY后面字段的顺序会影响分组结果。

select 
number 车牌号, status 状态, COUNT(*) 数量
from inoutinfo
group by number, status with rollup

测试结果：

解读：1. 与上面的结果对比，发现结果集少了两笔，因为它没有归纳车牌号下不同状态的统计数据。按照字段等级，status在number字段的后面，所以status字段的等级比number的高，所以先归纳status字段，得到3和6两笔记录，然后根据3和6归纳车牌号字段，得到7这笔记录。

5. GROUP BY和HAVING、WHERE、ORDER BY一起使用

select 
number, status, COUNT(*) 数量, SUM(spend) total
from inoutinfo2
where status = '已出车场'
group by number, status
having SUM(spend) > 20
order by total asc

测试结果：

解读： 1.它们四者的执行顺序：where > group by > having > order by;
2. 因为where是在group by之前执行，所以where子句中不能包含聚合函数，而having就可以；
3. having用于过滤分组后的结果集，所以不能使用输出字段的别名进行判读。而执行order by时，结果集已经查询完成，所以在order by中能使用输出字段的别名进行排序。