日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

本文介紹了使用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并為它們指定別名，因為您尚未在表中使用別名(我想)。然后，只需使用&quot；Thorsten Kettner&quot；回復的通知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的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，