oracle 您如何在查询中使用 MINUS 进行 ORDER BY？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7698280/
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
How do you ORDER BY in a query using MINUS?
提问by ladookie
I want to ORDER BYthe result of a MINUSquery.
我想要查询ORDER BY的结果MINUS。
My first attempt doesn't work:
我的第一次尝试不起作用：
SELECT *
FROM Table1
MINUS
SELECT *
FROM table2
WHERE table2.foo = 'bar'
ORDER BY foo
How would you do it?
你会怎么做？
oops:I was doing ORDER BY table2.fooinstead of just ORDER BY foo. Now it works.
哎呀：我在做ORDER BY table2.foo而不仅仅是ORDER BY foo. 现在它起作用了。
回答by case nelson
However, to answer your question, you can use a with query:
但是，要回答您的问题，您可以使用 with 查询：
with tmp_minus as (
SELECT *
FROM Table1
MINUS
SELECT *
FROM table2
WHERE table2.foo = 'bar'
)
select * from tmp_minus
ORDER BY foo
You should also be able to do a subselect:
您还应该能够进行子选择：
select * from (
SELECT *
FROM Table1
MINUS
SELECT *
FROM table2
WHERE table2.foo = 'bar'
) tmp_minus
ORDER BY foo
回答by Jonathan Leffler
If the MINUS were replaced by UNION, the ORDER BY would apply to the result of the UNION. Are you sure that's not what you get with MINUS?
如果 MINUS 被 UNION 替换，则 ORDER BY 将适用于 UNION 的结果。你确定这不是你用 MINUS 得到的吗？
If it doesn't work directly, then:
如果它不能直接工作，那么：
SELECT result.*
FROM (SELECT *
FROM Table1
MINUS
SELECT *
FROM table2
WHERE table2.foo = 'bar') AS result
ORDER BY foo;
However, I think this is unlikely to be necessary.
但是，我认为这不太可能是必要的。
回答by Jon Heller
You can use the position instead of the column name. Assuming that foo is the first column in the results:
您可以使用位置而不是列名称。假设 foo 是结果中的第一列：
SELECT *
FROM Table1
MINUS
SELECT *
FROM table2
WHERE table2.foo = 'bar'
ORDER BY 1
You normally do not want results to depend on a specific column order, so I would only use this for adhoc queries.
您通常不希望结果依赖于特定的列顺序，因此我只会将它用于临时查询。
