本文讨论了在MySQL中使用UNION合并查询结果时，ORDER BY子句可能失效的问题，导致数据排序不符合预期。通过示例SQL查询展示了在UNION查询中，查询结果的smoke_number顺序与单独查询时不同。解决方法是在每个子查询中添加LIMIT限制返回的行数，从而确保排序的正确性。最后给出了修正后的SQL语句，成功解决了排序问题。
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
)
查询结果：
问题解决。
标签：aa,bb,union,days,number,smoke,mysql,id,order
来源： https://www.cnblogs.com/lansetuerqi/p/13986528.html
扫一扫
4790
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
