关于MySQL ＞ 5.7的 order by，group by

先上一个SQL：

SELECT
temp.facility_id ,
temp.unit_id,
temp.user_id,
temp.`day`,
temp.time_period,
temp.stool_id,
temp.stool_abbreviation,
temp.situation_id,
temp.situation_abbreviation,
temp.stool_incontinence,
temp.bloody_stool
FROM
(
SELECT
mumr.bloody_stool as bloody_stool,
mumr.stool_incontinence as stool_incontinence,
DATE_FORMAT( mumr.`date`, '%Y-%m-%d' ) day,
DATE_FORMAT( mumr.`date`, '%H' ) time_period,
mumr.stool_volume_id as stool_id,
a3.abbreviation stool_abbreviation,
mumr.situation_id as situation_id,
a2.abbreviation situation_abbreviation,
mumr.facility_id ,
mumr.unit_id,
mumr.user_id
FROM
mnt_user_excretion_record  mumr
LEFT join mst_abbreviation a2 on mumr.situation_id = a2.id
AND a2.facility_id = 1 AND a2.`status` = 1
LEFT join mst_abbreviation a3 on mumr.stool_volume_id = a3.id
AND a3.facility_id = 1 AND a3.`status` = 1
WHERE mumr.facility_id = 1
AND mumr.unit_id = 1166
AND mumr.user_id = 4332
AND mumr.`status` = 1
AND mumr.service_setting_type IN (1,2,3)
AND mumr.`date` BETWEEN '2022/01/24' AND '2022/01/25'
ORDER BY mumr.bloody_stool DESC,mumr.stool_incontinence DESC,mumr.stool_volume_id DESC
) temp
GROUP BY temp.`day`,temp.time_period

这个SQL在5.7以下的查询结果是没有任何问题的:

换个8.0的MySQL:

最终发现是MySql 5.7以上对子查询进行了优化，认为子查询中的order by可以进行忽略，只要Derived table里不包含如下条件就可以进行优化：
1 . UNION clause
2 . GROUP BY
3 . DISTINCT
4 . Aggregation
5 . LIMIT or OFFSET

原SQL可以在子查询中加入 DISTINCT 或者
HAVING 1 再 ORDER BY mumr.bloody_stool DESC或者
排序之后 LIMIT 999999

这样就查询的数据一致了