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
