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 )

查询结果:

 问题解决。

 

posted @ 2020-11-16 18:54  蓝色土耳其  阅读(1544)  评论(0编辑  收藏  举报