使用UNION合并两个SELECT查询结果后如何统一按id降序排序

问题原因 #

• 第二个UNION查询分支的首个返回字段（对应第一个查询的u.id列）被写死为空字符串''，没有取notifications表的真实id值，导致admin类型通知的id字段无效，无法参与全局排序
• 外层排序语句未指定DESC降序规则，默认执行升序排序，和要求的倒序结果不符

正确实现代码 #

SELECT * FROM
(
  SELECT u.id, u.name, u.gender, n.user, n.other_user, n.type, n.notification, n.membership, n.link, n.created_at, p.photo 
  FROM notifications n
  INNER JOIN users u ON
  CASE
    WHEN n.user = :me THEN u.id = n.other_user
    WHEN n.other_user = :me THEN u.id = n.user
  END
  LEFT JOIN photos p ON
  CASE
    WHEN n.user = :me THEN p.user = n.other_user AND p.order_index = (SELECT MIN(order_index) FROM photos WHERE user = n.other_user)
    WHEN n.other_user = :me THEN p.user = n.user AND p.order_index = (SELECT MIN(order_index) FROM photos WHERE user = n.user)
  END
  UNION ALL -- 无去重需求时建议用UNION ALL，性能远高于UNION
  SELECT n.id, '', '', '', '', '', n.notification, n.membership, n.link, n.created_at, '' 
  FROM notifications n WHERE type = 'admin'
) AS x 
ORDER BY x.id DESC;

内容的提问来源于stack exchange，提问作者Relaxing Music