mysql的group by用法在5.7和5.6中存在区别

最新推荐文章于 2024-08-17 09:30:00 发布

项哥

最新推荐文章于 2024-08-17 09:30:00 发布

阅读量8.8k

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/liufang1991/article/details/53034198

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

http://stackoverflow.com/questions/39909261/order-by-before-group-by-in-subquery-working-in-mysql-5-5-but-not-in-mysql-5-7

http://stackoverflow.com/questions/34115174/i-am-getting-an-error-in-mysql-related-to-only-full-group-by-when-executing-a-qu

http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results，文章中写的参考例子都是获取到分组中的前两条记录，我们只要一条记录，把<=2改为<2即可

原来在5.6及其以下可以使用select * from(select * from table order by 排序字段) group by 字段，这种写法来得到分组需要的第一条记录，然而在5.7中子查询的排序条件会被忽略掉，暂时没有通过修改配置就可以解决的方案，只能参考demo自己改写sql语句，按照大神Drew的原句是我们原来的这种写法It is just sloppy programming.

第一种改写的办法：直接就是查出需要的ID再联查

第二种改写的办法就是添加变量

SELECT
    person,
    groupname,
    age
FROM
(
    SELECT
        person,
        groupname,
        age,
        @rn := IF(@prev = groupname, @rn + 1, 1) AS rn,
        @prev := groupname
    FROM mytable
    JOIN (SELECT @prev := NULL, @rn := 0) AS vars
    ORDER BY groupname, age DESC, person
) AS T1
WHERE rn <= 2

参照这个写法即

select * from(select *,@rn := IF(@prev = groupname, @rn + 1, 1) AS rn, @prev := groupname from table join(select @prev:=NULL,@rn:=0) AS vars order by groupname,其它排序字段) group by groupname where rn<2

第三种改写的方法就是在where条件中联查自己过滤

SELECT a.person, a.group, a.age FROM person AS a WHERE 
(SELECT COUNT(*) FROM person AS b 
WHERE b.group = a.group AND b.age >= a.age) <= 2 
ORDER BY a.group ASC, a.age DESC

select * from table AS a WHERE (select (count(*) from table AS b where a.groupname=b.groupname AND 满足特定的条件(b.updatetime>=a.updatetime)))<2 order by 排序条件

第四种改写的方法和第三种的原理类似，也是自己联查自己，只是不需要子查询，通过having条件来过滤

SELECT a.* FROM mytable AS a
  LEFT JOIN mytable AS a2 
    ON a.groupname = a2.groupname AND a.age <= a2.age
GROUP BY a.person
HAVING COUNT(*) <= 2
ORDER BY a.groupname, a.age DESC;