使用UNION的两个SELECT查询的组合ORDER BY
如何获得通过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
相关文章