mysql 中 group by 再 order by 排序失效

group by 注意点

N_P_E

2562人浏览 · 2022-03-03 08:56:18

N_P_E · 2022-03-03 08:56:18 发布

背景：

查询一组数据，需要根据一个no分组再根据结束时间进行排序，可是，发现order by 之后的数据不正确，并不是像我预期中的那样。

~~错误的sql~~

SELECT
	lp.ls_equipment_no,
	lp.ls_produce_end_time 
FROM
	busi_ls_produce lp 
WHERE
	lp.ls_produce_status = 4 
	AND lp.is_delete = 0 
	AND lp.ls_produce_org_id = 100 
GROUP BY
	lp.ls_equipment_no 
ORDER BY
	CAST( lp.ls_equipment_no AS UNSIGNED ) ASC,
	lp.ls_produce_end_time DESC

原因：

因为先分组了，分组只是根据no分组，并没有和结束时间有关系，所以可能这个结束时间就不正确。

解决方法：

使用Max()函数

	SELECT
	lp.ls_equipment_no,
	lp.ls_produce_end_time,
	MAX( lp.ls_produce_end_time ) AS produceEndTime 
FROM
	busi_ls_produce lp 
WHERE
	lp.ls_produce_status = 4 
	AND lp.is_delete = 0 
	AND lp.ls_produce_org_id = 100 
GROUP BY
	lp.ls_equipment_no 
ORDER BY
	CAST( lp.ls_equipment_no AS UNSIGNED ) ASC