亚洲视频二区_亚洲欧洲日本天天堂在线观看_日韩一区二区在线观看_中文字幕不卡一区

本文介紹了使用UNION的兩個SELECT查詢的組合ORDER BY的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！

問題描述

如何獲得通過UNION連接的這兩個查詢檢索到的組合ORDER BY結(jié)果？

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，那么所有記錄應(yīng)該像這樣組合排序9,7,6,5,4,3,2,1。

我已經(jīng)嘗試過了：

SELECT * FROM
(

   *THE WHOLE QUERY ABOVE*

) AS x 
ORDER BY x.id

這不是返回正確的結(jié)果。它對第一個查詢的結(jié)果按降序7,6,5,4,3排序，對第二個查詢的結(jié)果按升序1,2,9排序。它們被單獨(dú)分類，而不是一起分類。如何將它們組合在一起以用于9,7,6,5,4,3,2,1。

推薦答案

在兩個查詢中添加通知ID并為它們指定別名，因?yàn)槟形丛诒碇惺褂脛e名(我想)。然后，只需使用&quot；Thorsten Kettner&quot；回復(fù)的通知ID的別名進(jìn)行訂購。

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

這篇關(guān)于使用UNION的兩個SELECT查詢的組合ORDER BY的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，