mysql的groupby⽤法在5.7和5.6中存在区别
原来在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 mytablegroupby分组
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
up = a.group AND b.age >= a.age) <= 2
ORDER up ASC, a.age DESC
select * from table AS a WHERE (select (count(*) from table AS b upname
AND 满⾜特定的条件(b.updatetime>=a.updatetime)))<2 order by 排序条件
第四种改写的⽅法和第三种的原理类似，也是⾃⼰联查⾃⼰，只是不需要⼦查询，通过having条件来过滤
SELECT a.* FROM mytable AS a
LEFT JOIN mytable AS a2
upname = a2.groupname AND a.age <= a2.age
GROUP BY a.person
HAVING COUNT(*) <= 2
ORDER upname, a.age DESC;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。