使用UNION的两个SELECT查询的组合ORDER BY [英] Combined ORDER BY of two SELECT queries using UNION

问题描述

如何获得通过UNION连接的这两个查询检索到的组合ORDER BY结果？

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
SELECT '', '', '', '', '', '', n.notification, n.membership, n.link, n.created_at, '' FROM notifications n WHERE type = 'admin'

我希望返回的记录按照它们的id按降序排序。例如，如果第一个查询返回的记录是3,5,4,6,7，第二个查询返回的记录是2,1,9，那么所有记录应该像这样组合排序9,7,6,5,4,3,2,1。

我已经尝试过了：

SELECT * FROM
(

   *THE WHOLE QUERY ABOVE*

) AS x 
ORDER BY x.id

这不是返回正确的结果。它对第一个查询的结果按降序7,6,5,4,3排序，对第二个查询的结果按升序1,2,9排序。它们被单独分类，而不是一起分类。如何将它们组合在一起以用于9,7,6,5,4,3,2,1。

推荐答案

在两个查询中添加通知ID并为它们指定别名，因为您尚未在表中使用别名(我想)。然后，只需使用"；Thorsten Kettner"；回复的通知ID的别名进行订购。

SELECT u.id as uid, n.id as nid, 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
SELECT '', n.id as nid, '', '', '', '', '', n.notification, n.membership, n.link, n.created_at, '' FROM notifications n WHERE type = 'admin'
ORDER BY nid DESC

这篇关于使用UNION的两个SELECT查询的组合ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！