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

news/2024/7/2 1:24:16

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

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