oracle 在一个 select 语句中使用 group by、order by 和 limit
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26322038/
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
using group by, order by and limit in one select statement
提问by No Idea
I can't seem to work out a way to answer the following query. I am using SQL Developer
我似乎无法找到回答以下查询的方法。我正在使用 SQL Developer
I need to find out what were the top ten institutions in terms of total applicants in 2007. Here is what command I have tried:
我需要找出 2007 年申请人总数排名前十的机构是什么。这是我尝试过的命令：
select i.instid, i.instname, i.insttype,sum(f.TotalNumberApplicants)as TotalNumberofApplicants, f.year
from INSTITUTION_DIM i, FACT f
where i.INSTID = f.INSTID
and year = 2007
order by f.TOTALNUMBERAPPLICANTS
group by i.INSTID,i.INSTNAME,i.INSTTYPE,f.YEAR
limit 10;
I keep getting this error:
我不断收到此错误：
ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:
*Action: Error at Line: 10 Column: 1
ORA-00933：SQL 命令未正确结束 00933. 00000 - “SQL 命令未正确结束” *原因：
*操作：行错误：10 列：1
Any ideas??
有任何想法吗？？
回答by Sylvain Leroux
There might be some confusion here. Maybe it worth taking some time to clarify that?
这里可能有些混乱。也许值得花一些时间来澄清这一点？
Today, Oracle Corporationholds both the Oracle Databaseand MySQL. Those two products are different database servers. Despite some similarities, the syntax and feature supported by their implementation of SQL are quite different. The way to limit the number of rows returned by a query is one of them.
今天，甲骨文公司同时拥有甲骨文数据库和MySQL。这两种产品是不同的数据库服务器。尽管有一些相似之处，但它们的 SQL 实现所支持的语法和特性却大不相同。限制查询返回的行数的方法就是其中之一。
The confusion is somehow maintained by the fact that its not quite obvious from some docs on docs.oracle.comthat they are in fact related to MySQL. Perhaps you fell into that trap?
从docs.oracle.com上的一些文档中可以看出，它们实际上与 MySQL 相关，这一事实以某种方式维持了这种混乱。也许你掉进了那个陷阱？
For sake of completeness, here how you limit rows returned both with Oracle and MySQL:
为了完整起见，这里是如何限制 Oracle 和 MySQL 返回的行：
Oracle
甲骨文
For Oracle, one way to do it is simply to use the pseudo-column ROWNUM:
对于 Oracle，一种方法是简单地使用伪列ROWNUM：
SELECT * FROM (
SELECT ....
GROUP BY ...
ORDER BY ...
)
WHERE ROWNUM <= 10
-- Yes, you need a subquery here. But Oracle is smart enough
-- to optimize that statement
Starting with Oracle 12c, you may use the FETCHclause
(straight from the doc as I'm not lucky enough to have tested Oracle 12)
从 Oracle 12c 开始，您可以使用该FETCH子句
（直接来自文档，因为我不够幸运没有测试过 Oracle 12）
SELECT ...
GROUP BY ...
ORDER BY ...
FETCH FIRST 10 ROWS ONLY;
MySQL
MySQL
MySQL supports the LIMITclausesince at least MySQL 3.23 if I remember it well. Maybe even before? Anyway, that means a very long time ago.
如果我记得很清楚，MySQL 至少从 MySQL 3.23 开始支持该LIMIT子句。也许甚至以前？无论如何，这意味着很久以前。
SELECT ...
GROUP BY ...
ORDER BY ...
LIMIT 10;
回答by Ram
SELECT [DISTINCT | ALL] {* | select_list}
FROM {table_name [alias] | view_name}
[{table_name [alias] | view_name}]...
[WHERE condition]
[GROUP BY condition_list]
[HAVING condition]
[ORDER BY {column_name | column_# [ ASC | DESC ] } .
- Your
Order Byclause should be aftergroup byclause Joinis recommended overCartesian product
- 你的
Order By条款应该在group by条款之后 Join推荐结束Cartesian product
CODE
代码
SELECT i.instid, i.instname, i.insttype,SUM(f.TotalNumberApplicants) AS TotalNumberofApplicants, f.year
FROM INSTITUTION_DIM i
INNER JOIN FACT f ON i.INSTID = f.INSTID
WHERE year = 2007 AND rownum <= 10
GROUP BY i.INSTID,i.INSTNAME,i.INSTTYPE,f.YEAR
ORDER BY f.TOTALNUMBERAPPLICANTS;
