SQL 只选择第一个结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7834081/
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
Select first result only
提问by Alex Hope O'Connor
I have written the following query:
我编写了以下查询：
SELECT CLIENT.CLIENTNO, CLIENT.CNAME, TOTALS.TOTAL
FROM CLIENT, (SELECT CLIENTNO, SUM(AMOUNT) AS TOTAL
FROM PURCHASE GROUP BY CLIENTNO) TOTALS
WHERE CLIENT.CLIENTNO = TOTALS.CLIENTNO AND ROWNUM <= 1
ORDER BY TOTALS.TOTAL DESC;
However it is giving me the wrong answer, but if I remove the ROWNUM <= 1clause, then the correct answer is at the top of the result set.
然而，它给了我错误的答案，但是如果我删除了该ROWNUM <= 1子句，那么正确的答案就在结果集的顶部。
So what can I change in this query to make it produce the correct answer?
那么我可以在此查询中更改什么以使其产生正确的答案？
Thanks, Alex.
谢谢，亚历克斯。
EDIT: Forgot to say that I only want the query to return the the first result in the result set.
编辑：忘了说我只希望查询返回结果集中的第一个结果。
回答by Lukas Eder
The ROWNUMfilter applies before the sorting. What you need to do is this:
该ROWNUM过滤器适用分拣之前。你需要做的是：
SELECT * FROM (
SELECT CLIENT.CLIENTNO, CLIENT.CNAME, TOTALS.TOTAL
FROM CLIENT, (SELECT CLIENTNO, SUM(AMOUNT) AS TOTAL
FROM PURCHASE GROUP BY CLIENTNO) TOTALS
WHERE CLIENT.CLIENTNO = TOTALS.CLIENTNO
ORDER BY TOTALS.TOTAL DESC
)
WHERE ROWNUM <= 1
回答by BigMike
Wrap the rownum where in another external query:
将 rownum where 包裹在另一个外部查询中：
select * from (
SELECT CLIENT.CLIENTNO, CLIENT.CNAME, TOTALS.TOTAL
FROM CLIENT, (SELECT CLIENTNO, SUM(AMOUNT) AS TOTAL FROM PURCHASE GROUP BY CLIENTNO) TOTALS
WHERE CLIENT.CLIENTNO = TOTALS.CLIENTNO ORDER BY TOTALS.TOTAL DESC)
where ROWNUM <= 1;
