mysql 使用union(all) + order by 导致排序失效
1、SQL1如下
SELECT aa.* FROM apas_smoke aa WHERE STATUS = 0 AND aa.area_id = 'd61523dda339441f80008634c6b91f60' AND aa.type = '3' AND to_days( create_time ) = to_days( now()) ORDER BY CAST( aa.smoke_number AS UNSIGNED ) ASC
查询结果如下,smoke_number 顺序为3,4
2、sql2如下:
SELECT bb.* FROM apas_smoke bb WHERE bb.STATUS = 0 AND bb.area_id = 'd61523dda339441f80008634c6b91f60' AND bb.type = '1' AND to_days( create_time ) = to_days( now()) ORDER BY CAST( bb.smoke_number AS UNSIGNED ) ASC
查询结果如下,smoke_number顺序为1,2
3、但是当使用UNION后,SQL如下
( SELECT aa.* FROM apas_smoke aa WHERE STATUS = 0 AND aa.area_id = 'd61523dda339441f80008634c6b91f60' AND aa.type = '3' AND to_days( create_time ) = to_days( now()) ORDER BY CAST( aa.smoke_number AS UNSIGNED ) ASC ) UNION ( SELECT bb.* FROM apas_smoke bb WHERE bb.STATUS = 0 AND bb.area_id = 'd61523dda339441f80008634c6b91f60' AND bb.type = '1' AND to_days( create_time ) = to_days( now()) ORDER BY CAST( bb.smoke_number AS UNSIGNED ) ASC )
查询结果,如下,smoke_number顺序与3,4,2,1 与预期的顺序 3,4,1,2不一致。
**原因:union(all)会使order by失效,解决办法,加limit
4、最终SQL
(
SELECT aa.* FROM apas_smoke aa WHERE STATUS = 0 AND aa.area_id = 'd61523dda339441f80008634c6b91f60' AND aa.type = '3' AND to_days( create_time ) = to_days( now()) ORDER BY CAST( aa.smoke_number AS UNSIGNED ) ASC LIMIT 2 )
UNION ( SELECT bb.* FROM apas_smoke bb WHERE bb.STATUS = 0 AND bb.area_id = 'd61523dda339441f80008634c6b91f60' AND bb.type = '1' AND to_days( create_time ) = to_days( now()) ORDER BY CAST( bb.smoke_number AS UNSIGNED ) ASC LIMIT 2 )
查询结果:
问题解决。