mysql中having用法，mysql5.6 排序失效_mysql 使用union（all） + order by 導致排序失效

1、SQL1如下

mysql中having用法？SELECT

aa.*

groupby后排序？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

)

查詢結果：

問題解決。