SQL：如何使用 UNION 并按特定选择排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6036793/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: how to use UNION and order by a specific select?
提问by Topera
I have two selects:
我有两个选择：
SELECT id FROM a -- returns 1,4,2,3
UNION
SELECT id FROM b -- returns 2,1
I'm receiving correct num of rows, like: 1,4,2,3.
我收到正确的行数，例如：1,4,2,3。
But I want btable results first: 2,1,4,3or 2,1,3,4
但我b首先想要表格结果：2,1,4,3或2,1,3,4
How can I do this?
我怎样才能做到这一点？
(I'm using Oracle)
（我正在使用甲骨文）
采纳答案by Topera
Using @Adrian tips, I found a solution:
使用@Adrian 提示，我找到了一个解决方案：
I'm using GROUP BYand COUNT. I tried to use DISTINCTwith ORDER BYbut I'm getting error message: "not a SELECTed expression"
我正在使用GROUP BY和COUNT。我尝试将DISTINCT与ORDER BY一起使用，但收到错误消息：“不是 SELECTed 表达式”
select id from
(
SELECT id FROM a -- returns 1,4,2,3
UNION ALL -- changed to ALL
SELECT id FROM b -- returns 2,1
)
GROUP BY id ORDER BY count(id);
Thanks Adrian and thisblog.
谢谢阿德里安和这个博客。
回答by Adriano Carneiro
You want to do this:
你想这样做：
select * from
(
SELECT id, 2 as ordered FROM a -- returns 1,4,2,3
UNION
SELECT id, 1 as ordered FROM b -- returns 2,1
)
order by ordered
Update
更新
I noticed that even though you have two different tables, you join the IDs, that means, if you have 1in both tables, you are getting only one occurrence. If that's the desired behavior, you should stick to UNION. If not, change to UNION ALL.
我注意到即使你有两个不同的表，你加入了 ID，这意味着，如果你1在两个表中都有，你只会得到一个。如果这是所需的行为，您应该坚持使用UNION. 如果不是，请更改为UNION ALL。
So I also notice that if you change to the code I proposed, You would start getting both 1and 2(from both aand b). In that case, you might want to change the proposed code to:
所以我还注意到，如果您更改为我建议的代码，您将开始同时获得1和2（来自于a和b）。在这种情况下，您可能希望将建议的代码更改为：
select distinct id from
(
SELECT id, 2 as ordered FROM a -- returns 1,4,2,3
UNION
SELECT id, 1 as ordered FROM b -- returns 2,1
)
order by ordered
回答by Roeland Van Heddegem
@Adrien's answer is not working. It gives an ORA-01791.
@Adrien 的回答不起作用。它给出了一个 ORA-01791。
The correct answer (for the question that is asked) should be:
正确答案（针对所问的问题）应该是：
select id
from
(SELECT id, 2 as ordered FROM a -- returns 1,4,2,3
UNION ALL
SELECT id, 1 as ordered FROM b -- returns 2,1
)
group by id
order by min(ordered)
Explanation:
解释：
- The "UNION ALL" is combining the 2 sets. A "UNION" is wastefull because the 2 sets could not be the same, because the ordered field is different.
- The "group by" is then eliminating duplicates
- The "order by min (ordered)" is assuring the elements of table b are first
- “UNION ALL”组合了 2 套。“UNION”是浪费的，因为 2 个集合不能相同，因为有序字段不同。
- “分组依据”然后消除重复项
- "order by min (ordered)" 是确保表 b 的元素是第一
This solves all the cases, even when table b has more or different elements then table a
这解决了所有情况，即使表 b 具有比表 a 更多或不同的元素
回答by onedaywhen
SELECT id, 1 AS sort_order
FROM b
UNION
SELECT id, 2 AS sort_order
FROM a
MINUS
SELECT id, 2 AS sort_order
FROM b
ORDER BY 2;
回答by Allan
@Adrian's answer is perfectly suitable, I just wanted to share another way of achieving the same result:
@Adrian 的回答非常合适，我只是想分享另一种实现相同结果的方法：
select nvl(a.id, b.id)
from a full outer join b on a.id = b.id
order by b.id;
回答by Nur.B
SELECT id FROM a -- returns 1,4,2,3
UNION
SELECT id FROM b -- returns 2,1
order by 2,1
