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
)
查询结果:
问题解决。