分组后排序

需求：获取每个⽤户最⼤⾦额，然后按照最⼤⾦额倒序，输出：⽤户id，最⼤⾦额，如

下：

mysql> SELECT

 user_id ⽤户id, max(price) 最⼤⾦额

 FROM

 t_order t

 GROUP BY user_id

 ORDER BY 最⼤⾦额 desc;

+----------+--------------+

| ⽤户id | 最⼤⾦额 |

+----------+--------------+

| 1001 | 88.88 |

| 1003 | 66.66 |

| 1002 | 44.44 |

+----------+--------------+

3 rows in set (0.00 sec)

where & group by & having & order by & limit ⼀起协作

where、group by、having、order by、limit这些关键字⼀起使⽤时，先后顺序有明确的限

制，语法如下：

select 列 from 

表名

where [查询条件]

group by [分组表达式]

having [分组过滤条件]order by [排序条件]

limit [offset,] count;

注意：

写法上⾯必须按照上⾯的顺序来写。

⽰例：

需求：查询出2018年，下单数量⼤于等于2的，按照下单数量降序排序，最后只输出第1

条记录，显⽰：⽤户id，下单数量，如下：

mysql> SELECT

 user_id ⽤户id, COUNT(id) 下单数量

 FROM

 t_order t

 WHERE

 t.the_year = 2018

 GROUP BY user_id

 HAVING count(id)>=2

 ORDER BY 下单数量 DESC

 LIMIT 1;

+----------+--------------+

| ⽤户id | 下单数量 |

+----------+--------------+

| 1002 | 3 |

+----------+--------------+

1 row in set (0.00 sec)

mysql分组中的坑

本⽂开头有介绍，分组中select后⾯的列只能有2种：

1. 出现在group by后⾯的列

2. 使⽤聚合函数的列

oracle、sqlserver、db2中也是按照这种规范来的。⽂中使⽤的是5.7版本，默认是按照这种规范来的。

mysql早期的⼀些版本，没有上⾯这些要求，select后⾯可以跟任何合法的列。

示例

需求：获取每个⽤户下单的最⼤⾦额及下单的年份，输出：⽤户id，最⼤⾦额，年份，写

法如下：

mysql> select

 user_id ⽤户id, max(price) 最⼤⾦额, the_year 年份

 FROM t_order t

 GROUP BY t.user_id;

ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY 

clause and contains nonaggregated column 'javacode2018.t.the_year' 

which is not functionally dependent on columns in GROUP BY clause; 

this is incompatible with sql_mode=only_full_group_by

上⾯的sql报错了，原因因为the_year不符合上⾯说的2条规则（select后⾯的列必须出现

在group by中或者使⽤聚合函数），⽽sql_mode限制了这种规则，我们看⼀下sql_mode

的配置：

mysql> select @@sql_mode;

+---------------------------------------------------------------------

----------------------------------------------------------------------

+

| @@sql_mode 

|

+---------------------------------------------------------------------

----------------------------------------------------------------------

+

| 

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ER

ROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+---------------------------------------------------------------------

----------------------------------------------------------------------

+

1 row in set (0.00 sec)sqlmode中包含了`ONLYFULLGROUPBY`，这个表⽰select后⾯的列必须符合上⾯的说的2点

规范。

可以将ONLY_FULL_GROUP_BY去掉，select后⾯就可以加任意列了，我们来看⼀下效果。

修改mysql中的my.ini⽂件：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DI

VISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

重启mysql，再次运⾏，效果如下：

mysql> select

 user_id ⽤户id, max(price) 最⼤⾦额, the_year 年份

 FROM t_order t

 GROUP BY t.user_id;

+----------+--------------+--------+

| ⽤户id | 最⼤⾦额 | 年份 |

+----------+--------------+--------+

| 1001 | 88.88 | 2017 |

| 1002 | 44.44 | 2018 |

| 1003 | 66.66 | 2018 |

+----------+--------------+--------+

3 rows in set (0.03 sec)

看⼀下上⾯的数据，第⼀条88.88的年份是2017年，我们再来看⼀下原始数据：

mysql> select * from t_order;

+----+---------+---------------+-------+----------+

| id | user_id | user_name | price | the_year |

+----+---------+---------------+-------+----------+

| 1 | 1001 | 路⼈甲Java | 11.11 | 2017 |

| 2 | 1001 | 路⼈甲Java | 22.22 | 2018 |

| 3 | 1001 | 路⼈甲Java | 88.88 | 2018 |

| 4 | 1002 | 刘德华 | 33.33 | 2018 |

| 5 | 1002 | 刘德华 | 12.22 | 2018 |

| 6 | 1002 | 刘德华 | 16.66 | 2018 |

| 7 | 1002 | 刘德华 | 44.44 | 2019 |

| 8 | 1003 | 张学友 | 55.55 | 2018 |

| 9 | 1003 | 张学友 | 66.66 | 2019 |+----+---------+---------------+-------+----------+

9 rows in set (0.00 sec)

对⽐⼀下，userid=1001、price=88.88是第3条数据，即theyear是2018年，但是上⾯的分

组结果是2017年，结果和我们预期的不⼀致，此时mysql对这种未按照规范来的列，乱序

了，mysql取的是第⼀条。

正确的写法，提供两种，如下：

mysql> SELECT

 user_id ⽤户id,

 price 最⼤⾦额,

 the_year 年份

 FROM

 t_order t1

 WHERE

 (t1.user_id , t1.price)

 IN

 (SELECT

 t.user_id, MAX(t.price)

 FROM

 t_order t

 GROUP BY t.user_id);

+----------+--------------+--------+

| ⽤户id | 最⼤⾦额 | 年份 |

+----------+--------------+--------+

| 1001 | 88.88 | 2018 |

| 1002 | 44.44 | 2019 |

| 1003 | 66.66 | 2019 |

+----------+--------------+--------+

3 rows in set (0.00 sec)

mysql> SELECT

 user_id ⽤户id,

 price 最⼤⾦额,

 the_year 年份

 FROM

 t_order t1,(SELECT

 t.user_id uid, MAX(t.price) pc FROM

 t_order t

 GROUP BY t.user_id) t2

 WHERE

 t1.user_id = t2.uid

 AND t1.price = t2.pc;

+----------+--------------+--------+

| ⽤户id | 最⼤⾦额 | 年份 |

+----------+--------------+--------+

| 1001 | 88.88 | 2018 |

| 1002 | 44.44 | 2019 |

| 1003 | 66.66 | 2019 |

+----------+--------------+--------+

3 rows in set (0.00 sec)

上⾯第1种写法，⽐较少见，in中使⽤了多字段查询。

建议：在写分组查询的时候，最好按照标准的规范来写，select后⾯出现的列必须在

group by中或者必须使⽤聚合函数。

总结

1. 在写分组查询的时候，最好按照标准的规范来写，select后⾯出现的列必须在group

by中或者必须使⽤聚合函数。

2. select语法顺序：select、from、where、group by、having、order by、limit，顺序不

能搞错了，否则报错。