oracle 如何在oracle中获取不同表的前十行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26582475/
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 to fetch top ten rows of different tables in oracle?
提问by Rika
I have 4 tables which have 5 columns in common. I want to fetch the latest overall top ten rows for these tables, and retrieve them.
How can i do that?
I tried using a simple join command, but instead of 5 columns I get 20 columns! each table has its own column name alias! but what I want is, to have a 5 column table which contains all of those top 10s.
我有 4 个表，它们有 5 个共同的列。我想为这些表获取最新的总体前十行，并检索它们。
我怎样才能做到这一点？我尝试使用一个简单的 join 命令，但是我得到了 20 列而不是 5 列！每个表都有自己的列名别名！但我想要的是，有一个包含所有前 10 名的 5 列表。
Update:
更新：
fetch FIRST 10 ROWS ONLY
Command returns the 10 first records, not the latest ones when I run the following command for example :
例如，当我运行以下命令时，命令返回前 10 条记录，而不是最新记录：
select * from tblTest order by ID FETCH First 3 ROWS Only;
I get
我得到
1 Ali
2 Jacob
3 Chris
Where I actually needed
我真正需要的地方
18 Fatima
19 Mary
20 Catherina
How can i achieve such a thing?
我怎样才能实现这样的事情？
回答by Mureinik
You can use union allto get all the rows consecutively, and then take the top 10:
您可以使用union all连续获取所有行，然后取前 10 行：
SELECT col1, col2, col3, col4, col5
FROM (SELECT col1, col2, col3, col4, col5
FROM (SELECT col1, col2, col3, col4, col5 FROM table1
UNION ALL
SELECT col1, col2, col3, col4, col5 FROM table2
UNION ALL
SELECT col1, col2, col3, col4, col5 FROM table3
UNION ALL
SELECT col1, col2, col3, col4, col5 FROM table4)
ORDER BY col1, col2, col3, col4, col5)
WHERE rownum <= 10
回答by Lalit Kumar B
In Oracle 12c, you can use the TOP-Nquery :
在 Oracle 12c 中，您可以使用TOP-N查询：
SQL> select * from(
2 (select deptno from emp
3 ORDER BY deptno
4 fetch FIRST 10 ROWS ONLY)
5 UNION all
6 (select deptno from emp
7 ORDER BY deptno
8 fetch FIRST 10 ROWS ONLY)
9 )
10 /
DEPTNO
----------
10
10
10
20
20
20
20
20
30
30
10
DEPTNO
----------
10
10
20
20
20
20
20
30
30
20 rows selected.
Unlike ROWNUM, you need not worry about the ORDER.
与此不同的是ROWNUM，您不必担心ORDER.
UpdateRegarding last records in the order
更新关于订单中的最后记录
Use DESCin order.
DESC按顺序使用。
SQL> SELECT *
2 FROM(
3 (SELECT deptno FROM emp ORDER BY deptno DESC
4 FETCH FIRST 3 ROWS ONLY
5 )
6 UNION ALL
7 (SELECT deptno FROM emp ORDER BY deptno DESC
8 FETCH FIRST 3 ROWS ONLY
9 ) )
10 /
DEPTNO
----------
30
30
30
30
30
30
6 rows selected.
SQL>
