MYSQL中 UNION ALL 与 ORDER BY 联合使用

当关键字union all和order by联合使用时，union all优先级会比order by优先级高

完整sql如下：

SELECT 
id,is_sys AS isSys,created_time AS createdTime
FROM market_strategy_type
WHERE is_deleted = 'false' AND is_sys = 'true' 

UNION ALL

SELECT 
id,is_sys AS isSys,created_time AS createdTime
FROM market_strategy_type
WHERE is_deleted = 'false' AND is_sys = 'false' AND company_id = 1182834566247878656

ORDER BY createdTime desc

执行步骤

1、先执行上方一段查询语句

SELECT 
id,is_sys AS isSys,created_time AS createdTime
FROM market_strategy_type
WHERE is_deleted = 'false' AND is_sys = 'true' 

2、再执行下方一段查询语句

SELECT 
id,is_sys AS isSys,created_time AS createdTime
FROM market_strategy_type
WHERE is_deleted = 'false' AND is_sys = 'false' AND company_id = 1182834566247878656

3、在进行 union all 连接，将两个结果集进行合并，不进行去重，union all（合并不去重），union（合并并去重）

4、合并两个结果集之后再进行 order by 排序

 

注意事项

1、若将 order by 写在第一个（上方一段查询）查询语句后，会提示SQL异常：命令未正确结束！

2、若上下两段SQL查询未查询出 created_time，或者将 created_time as createdTime，由于表中时间字段为 created_time，若写成 order by created_time后，会提示SQL异常：字段 created_time 不存在！

　　因为返回结果中未查询出 created_time 字段，或者 created_time as createdTime了，因此需要根据返回的结果集，按照 createdTime进行排序；

 

当关键字union all和order by联合使用时，两段查询中的 order by 失效问题（参考：order by的使用）

完整sql如下：

SELECT 
id,is_sys AS isSys,created_time AS createdTime
FROM market_strategy_type
WHERE is_deleted = 'false' AND is_sys = 'true' 

UNION ALL

SELECT * FROM (
SELECT 
id,is_sys AS isSys,created_time AS createdTime
FROM market_strategy_type
WHERE is_deleted = 'false' AND is_sys = 'false' AND company_id = 1182834566247878656
ORDER BY created_time DESC
) t2

注意事项

1、当前t2结果集必须使用括号抱起来，并起别名 t2，否则会提示命令未正常结束！

2、当前结果集为两段结果的合集，但顺序被打乱，t2结果集并没有根据 created_time进行排序

3、此时可以使用 limit关键字解决，在t2结果集中使用 limit，可以解决顺序被打乱的问题，但最终返回结果受到了影响，完整sql如下

SELECT 
id,is_sys AS isSys,created_time AS createdTime
FROM market_strategy_type
WHERE is_deleted = 'false' AND is_sys = 'true' 

UNION ALL

SELECT * FROM (
SELECT 
id,is_sys AS isSys,created_time AS createdTime
FROM market_strategy_type
WHERE is_deleted = 'false' AND is_sys = 'false' AND company_id = 1182834566247878656
ORDER BY created_time DESC
LIMIT 0,10
) t2

4、上方SQL返回的最终结果集会根据 limit 关键字而受到影响，最终解决SQL如下所示：

SELECT 
id,is_sys AS isSys,created_time AS createdTime
FROM market_strategy_type
WHERE is_deleted = 'false' AND is_sys = 'true' 

UNION ALL

SELECT 
id,is_sys AS isSys,created_time AS createdTime
FROM market_strategy_type
WHERE is_deleted = 'false' AND is_sys = 'false' AND company_id = 1182834566247878656

ORDER BY isSys DESC,createdTime DESC;

当前SQL注意事项：

-- 结果集先根据 isSys进行倒序，在根据createdTime进行倒序，若存在冲突，则前者优先
ORDER BY isSys DESC,createdTime DESC;

-- 结果集根据 isSyus、createdTime综合进行倒序
ORDER BY isSys,createdTime DESC;

拓展：以上SQL写法上可进行优化，优化后的SQL如下所示：

SELECT 
id,is_sys AS isSys,created_time AS createdTime

FROM market_strategy_type
WHERE is_deleted = 'false' 
AND (is_sys = 'true' OR company_id = 1182834566247878656) 

ORDER BY is_sys DESC,created_time DESC

 

以下列举错误SQL：

-- 命令未正常结束
SELECT 
id,is_sys AS isSys,created_time AS createdTime
FROM market_strategy_type
WHERE is_deleted = 'false' AND is_sys = 'false' AND company_id = 1182834566247878656
ORDER BY created_time DESC

UNION ALL

SELECT 
id,is_sys AS isSys,created_time AS createdTime
FROM market_strategy_type
WHERE is_deleted = 'false' AND is_sys = 'true' 

-- created_time列不存在
SELECT 
id,is_sys AS isSys,created_time AS createdTime
FROM market_strategy_type
WHERE is_deleted = 'false' AND is_sys = 'false' AND company_id = 1182834566247878656

UNION ALL

SELECT 
id,is_sys AS isSys,created_time AS createdTime
FROM market_strategy_type
WHERE is_deleted = 'false' AND is_sys = 'true' 

ORDER BY created_time DESC