mysql使用多条子语句排序查询，同时使用union连接，返回结果排序的问题处理办法

最新推荐文章于 2026-07-22 16:17:57 发布

原创最新推荐文章于 2026-07-22 16:17:57 发布 · 493 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

本文探讨了在MySQL中使用UNION与ORDER BY时遇到的问题，即UNION操作的优先级高于ORDER BY，导致结果未按预期排序。通过在每个子查询中添加优先级字段并统一排序规则，解决了排序问题，确保了数据按照行政级别顺序正确展示。

处理前的排序方式（行政级别顺序：中心，所，村）

SELECT * FROM
	(
	(
SELECT
	*,
	( st_distance ( point ( longitude, latitude ), point ( 114.32602, 30.55383 ) ) * 111195 ) AS distance 
FROM
	activity_wish_list 
WHERE
	area_5 = 2426 
	AND enabled = 1 
ORDER BY
	distance ASC  -- 本村
	) UNION
	(
SELECT
	*,
	( st_distance ( point ( longitude, latitude ), point ( 114.32602, 30.55383 ) ) * 111195 ) AS distance 
FROM
	activity_wish_list 
WHERE
	area_4 = 2425 
	AND area_5 > 0 
	AND area_5 != 2426 
	AND enabled = 1 
ORDER BY
	distance ASC  -- 本所其他村
	) UNION
	(
SELECT
	*,
	( st_distance ( point ( longitude, latitude ), point ( 114.32602, 30.55383 ) ) * 111195 ) AS distance 
FROM
	activity_wish_list 
WHERE
	area_4 = 2425 
	AND area_5 = 0 
	AND enabled = 1  -- 本所
ORDER BY
	distance ASC 
	) UNION
	(
SELECT
	*,
	( st_distance ( point ( longitude, latitude ), point ( 114.32602, 30.55383 ) ) * 111195 ) AS distance 
FROM
	activity_wish_list 
WHERE
	area_3 = 2424 
	AND area_4 != 2425 
	AND area_5 > 0  -- 其他所的村
	AND enabled = 1 
ORDER BY
	distance ASC 
	) UNION
	(
SELECT
	*,
	( st_distance ( point ( longitude, latitude ), point ( 114.32602, 30.55383 ) ) * 111195 ) AS distance 
FROM
	activity_wish_list 
WHERE
	area_3 = 2424 
	AND area_4 != 2425 
	AND area_5 = 0 
	AND enabled = 1  -- 其他所
ORDER BY
	distance ASC 
	) UNION
	(
SELECT
	*,
	( st_distance ( point ( longitude, latitude ), point ( 114.32602, 30.55383 ) ) * 111195 ) AS distance 
FROM
	activity_wish_list 
WHERE
	area_3 = 2424 
	AND area_4 = 0 
	AND area_5 = 0 
	AND enabled = 1 -- 中心
ORDER BY
	distance ASC 
	) 
	) AS wish_list 
WHERE
	`enabled` = 1  
	AND `deleted_at` IS NULL

运行结果并没有按照每个自语句排序好后显示出来。
问题在于：mysql中union和order by同时使用时，union优先级高于order by，实际上union连接多个子语句是没有排序的，这就是问题所在。

处理办法：在每个子语句的查询结果中加一个字段用于区分子语句的优先级，再按照所有子语句相同的排序规则去排序。如下：

SELECT *
FROM
	(
	(
SELECT
	*,
	0 AS temp,
	( st_distance ( point ( longitude, latitude ), point ( 114.32602, 30.55383 ) ) * 111195 ) AS distance 
FROM
	activity_wish_list 
WHERE
	area_5 = 2426 
	AND enabled = 1 
	) UNION
	(
SELECT
	*,
	1 AS temp,
	( st_distance ( point ( longitude, latitude ), point ( 114.32602, 30.55383 ) ) * 111195 ) AS distance 
FROM
	activity_wish_list 
WHERE
	area_4 = 2425 
	AND area_5 > 0 
	AND area_5 != 2426 
	AND enabled = 1 
	) UNION
	(
SELECT
	*,
	2 AS temp,
	( st_distance ( point ( longitude, latitude ), point ( 114.32602, 30.55383 ) ) * 111195 ) AS distance 
FROM
	activity_wish_list 
WHERE
	area_4 = 2425 
	AND area_5 = 0 
	AND enabled = 1 
	) UNION
	(
SELECT
	*,
	3 AS temp,
	( st_distance ( point ( longitude, latitude ), point ( 114.32602, 30.55383 ) ) * 111195 ) AS distance 
FROM
	activity_wish_list 
WHERE
	area_3 = 2424 
	AND area_4 != 2425 
	AND area_5 > 0 
	AND enabled = 1 
	) UNION
	(
SELECT
	*,
	4 AS temp,
	( st_distance ( point ( longitude, latitude ), point ( 114.32602, 30.55383 ) ) * 111195 ) AS distance 
FROM
	activity_wish_list 
WHERE
	area_3 = 2424 
	AND area_4 != 2425 
	AND area_5 = 0 
	AND enabled = 1 
	) UNION
	(
SELECT
	*,
	5 AS temp,
	( st_distance ( point ( longitude, latitude ), point ( 114.32602, 30.55383 ) ) * 111195 ) AS distance 
FROM
	activity_wish_list 
WHERE
	area_3 = 2424 
	AND area_4 = 0 
	AND area_5 = 0 
	AND enabled = 1 
ORDER BY
	distance ASC 
	) 
	) AS wish_list 
WHERE
	`enabled` = 1
	AND `deleted_at` IS NULL 
ORDER BY
	`temp` ASC,
	`distance` ASC