SQL：如何使用UNION并通过特定的选择进行排序？

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 b table results first: 2,1,4,3 or 2,1,3,4

但我首先想要b表结果：2,1,4,3或2,1,3,4

How can I do this?

我怎样才能做到这一点？

(I'm using Oracle)

（我正在使用Oracle）

5 个解决方案

#1

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 1 in 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 1 and 2 (from both a and 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

#2

Using @Adrian tips, I found a solution:

使用@Adrian提示，我找到了一个解决方案：

I'm using GROUP BY and COUNT. I tried to use DISTINCT with ORDER BY but I'm getting error message: "not a SELECTed expression"

我正在使用GROUP BY和COUNT。我尝试使用带有ORDER BY的DISTINCT，但我收到错误消息：“不是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 this blog.

感谢Adrian和这个博客。

#3

@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.
“UNION ALL”结合了2套。 “UNION”是浪费的，因为2组不能相同，因为有序场是不同的。
The "group by" is then eliminating duplicates
然后，“group by”消除了重复
The "order by min (ordered)" is assuring the elements of table b are first
“按分钟排序（有序）”是确保表b的元素是第一个

This solves all the cases, even when table b has more or different elements then table a

这解决了所有情况，即使表b具有更多或不同的元素，然后表a

#4

@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;

#5

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;

#1