SQL:如何使用 UNION 并按特定选择排序?

2021-12-19 00:00:00 sql union select oracle

我有两个选择:

SELECT id FROM a -- returns 1,4,2,3
UNION
SELECT id FROM b -- returns 2,1

我收到正确的行数，例如:1,4,2,3.

I'm receiving correct num of rows, like: 1,4,2,3.

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

But I want b table results first: 2,1,4,3 or 2,1,3,4

我该怎么做?

(我使用的是 Oracle)

(I'm using Oracle)

推荐答案

使用@Adrian 提示，我找到了解决方案:

Using @Adrian tips, I found a solution:

我正在使用 GROUP BY 和 COUNT.我尝试将 DISTINCT 与 ORDER BY 一起使用，但收到错误消息:not a SELECTed expression"

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"

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

感谢 Adrian 和这个博客.

Thanks Adrian and this blog.

相关文章