mysql进阶5：分组查询

/*语法：

select 分组函数 列（要求出现在group by的后面）

from 表

【where 筛选条件】

group by 分组的列表

[order by 子句]

注意：查询列表必须特殊，要求是分组函数和group by 后出现的字段

特点：

1.分组查询中筛选条件分为两类：

数据源 位置 关键字

分组前筛选 原始表 group by子句的前面 where

分组后筛选 分组后的结果集 group by子句的后面 having

1.分组函数做条件肯定是放在having子句中

2.能用分组前筛选的，优先考虑使用分组前筛选

2.group by 子句支持单个字段、多个字段分组（用逗号隔开没有顺序要求）表达式或函数（用的较少）

3.也可以添加排序（放在整个group by子句之后）

*/

count(1)，其实就是计算一共有多少符合条件的行。

1并不是表示第一个字段，而是表示一个固定值。

其实就可以想成表中有这么一个字段，这个字段就是固定值1，count(1)，就是计算一共有多少个1.

同理，count(2)，也可以，得到的值完全一样，count('x')，count('y')都是可以的。一样的理解方式。在你这个语句理都可以使用，返回的值完全是一样的。就是计数。

count(*)，执行时会把星号翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些。

MySQL中使用 GROUP BY 对数据进行分组，GROUP BY从字面意义上理解就是根据'BY'指定的规则对数据进行分组， 所谓分组就是将一个'数据集'划分成若干个'子区域'，然后针对若干个'小区域'进行数据处理 。基本语法形式为：

字段值为进行分组时所依据的列名称， “HAVING <条件表达式>” 指定满足表达式限定条件的结果将被显示。

GROUP BY关键字通常和集合函数一起使用，例如：MAX()、MIN()、COUNT()、SUM()、AVG()。即把数据分为多个逻辑组，并对每个组进行集合计算。

WHERE 子句过滤行，在数据分组前过滤；HAVING 子句过滤分组，在数据分组后过滤。WHERE排除的行不包括在分组里，且HAVING支持所有WHERE *** 作符。

使用GROUP BY可以对多个字段进行分组，根据多字段的值来进行层次分组，分组从左到右。

注意事项：

前两天同事有个 MySQL 数据分组的需求，如下测试数据，需要找出每个 name 分组中 create_date 最近的记录：

需要注意的是，此处用的 MySQL 是5.6，最初是使用这条语句：

<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10pxmargin-bottom: 10pxborder-radius: 5pxbox-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px">select name, value, create_date, update_date from t1 group by name order by create_date desc </pre>

用这条 SQL 得到的其实只是每个 name 分组中最先插入的记录，然后按照 create_date 进行了降序排列，和原始需求，完全不同。

此时可采用分而治之的策略，先做排序，再做分组：

<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10pxmargin-bottom: 10pxborder-radius: 5pxbox-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px">select * from (select name, value, create_date, update_date from t1 order by create_date desc) t group by t.name </pre>

当然，针对此需求，可能有其他方法，有兴趣的朋友，可以尝试写写，共享一下。

可能有细心的朋友会发现个问题，就是上述 SQL 中的 group by ，好像有些奇怪，如果按照常规，select 中的字段需要出现在 group by 中，上述语句竟然没报错？

如果我们在 MySQL 5.7 执行相同的语句：

<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10pxmargin-bottom: 10pxborder-radius: 5pxbox-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px">select name, value, create_date, update_date from t1 group by name order by create_date desc </pre>

因此从5.6升级到5.7，很可能出现这种相同的 SQL 执行结果不同的现象，这对兼容性测试的要求就会很高，究其原因，一方面是特性决定的，另一方面就是各种配置参数不同导致的。

可以在5.7的 sql_mode 中删除这个 ONLY_FULL_GROUP_BY ，即可达到5.6相同效果了，或者改写 SQL ，例如：

<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10pxmargin-bottom: 10pxborder-radius: 5pxbox-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px">select * from t1 a where create_date = (select max(create_date) from t1 b where a.name = b.name) </pre>

或者，

<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10pxmargin-bottom: 10pxborder-radius: 5pxbox-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px">select * from t1 a where not exists (select * from t1 b where a.name = b.name and b.create_date >a.create_date) </pre>

MySQL 8.0支持 row_number()函数， *** 作应该和如下 Oracle 相近的。

Oracle 中可以使用 row_number()实现此需求：

<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10pxmargin-bottom: 10pxborder-radius: 5pxbox-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px">select * from (select name, create_date, row_number() over (partition by name order by create_date desc) as r from t1) where r=1 </pre>

---