如何从表中检索一半的记录 - Oracle 11g
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30781979/
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 retrieve half of records from a table - Oracle 11g
提问by Fulvio
How can i retrieve (select)half of records from a table, for example, a table with 1000 rows, retrieve 500 (50%) from the table. (in this case i can use rownum because we know the exact quantity of rows (1000) - select * from table where rownum <= 500), but i have to count every table to achieve the statement.
如何从表中检索（选择）一半的记录，例如，有 1000 行的表，从表中检索 500 (50%) 条记录。（在这种情况下，我可以使用 rownum，因为我们知道行的确切数量 (1000) - select * from table where rownum <= 500），但我必须计算每个表才能实现该语句。
What's the best way do you think i can do this?
你认为我能做到的最好方法是什么？
回答by David Aldridge
Well, you could count the rows and select half:
好吧，您可以计算行数并选择一半：
select *
from my_table
where rownum <= (select count(*)/2 from my_table)
That would tendto select rows that are contiguous within the physical segments.
这将倾向于选择在物理段内连续的行。
Or ...
或者 ...
select *
from (select rownum rn, * from my_table)
where mod(rn,2) = 0
That would tendto select "every other" row, so you'd get a pretty even spread from the physical data segments.
这将倾向于选择“每隔一个”行，因此您会从物理数据段中获得相当均匀的分布。
Or ...
或者 ...
select *
from my_table sample (50)
That would be approximately half of the rows.
这将是大约一半的行。
Or ...
或者 ...
select *
from my_table sample block (50)
That would be the rows from approximately half of the data blocks below the high water marks of the segments.
那将是来自段的高水位线以下的大约一半数据块的行。
Probably lots of different ways available, and which one you want probably depends on whether you want the selected pseudo-randomly or not.
可能有很多不同的可用方式，您想要哪种方式可能取决于您是否想要伪随机选择。
If you want to use the output of the query, use something like:
如果要使用查询的输出，请使用以下内容：
select ...
from (select *
from my_table
where rownum <= (select count(*)/2 from my_table)) my_table
join ...
In that circumstance the SAMPLE syntax would be more compact.
在这种情况下，SAMPLE 语法会更紧凑。
回答by Giorgi Nakeuri
With NTILEwindow function:
带NTILE窗函数：
select * from (SELECT *, NTILE(2) OVER(ORDER BY (SELECT NULL FROM DUAL)) nt FROM TableName) as t
where nt = 1
or:
或者：
select * from (SELECT *, NTILE(2) OVER(ORDER BY NULL) nt FROM TableName) as t
where nt = 1
回答by Lalit Kumar B
If you are on Oracle 12c, you could use the Top-n row limitingfeature which allows to limit by percentage of rows.
如果您使用 Oracle 12c，则可以使用Top-n 行限制功能，该功能允许按行的百分比进行限制。
For example, in my EMPtable in SCOTTschema I have 14 rows, and I want first 50 percent rowsbased on order by sal:
例如，在我EMP的SCOTT模式表中，我有14 rows，我想首先50 percent rows基于按顺序排序：
SQL> SELECT empno, sal FROM emp
2 ORDER BY sal
3 FETCH FIRST 50 PERCENT ROWS ONLY;
EMPNO SAL
---------- ----------
7369 800
7900 950
7876 1100
7521 1250
7654 1250
7934 1300
7844 1500
7 rows selected.
SQL>
回答by Harish Raju
As far my knowledge goes, the best way to perform this operation is by executing the following block of code:
据我所知，执行此操作的最佳方法是执行以下代码块：
SELECT * FROM table_name
WHERE ROWNUM <= (SELECT COUNT(*)/2 FROM table_name);
SELECT * FROM table_name
WHERE ROWNUM <= (SELECT COUNT(*)/2 FROM table_name);
And to get the second half:
并获得下半场：
SELECT * FROM
(SELECT ROWNUM AS RN,table_name.* FROM table_name)
WHERE RN > (SELECT COUNT(*)/2 FROM table_name);
SELECT * FROM
(SELECT ROWNUM AS RN,table_name.* FROM table_name)
WHERE RN > (SELECT COUNT(*)/2 FROM table_name);
回答by ravibeli
If total no of records are not even number, above query won't work, here is the working query regardless of count(*)/2returns either whole number or decimal
如果总记录数不是偶数，上面的查询将不起作用，这里是工作查询，无论count(*)/2返回整数还是小数
SELECT ROWNUM, tmp.*
FROM employees tmp
WHERE TO_NUMBER(ROWNUM) <= (SELECT count(*)/2 FROM employees);
回答by Charles Bretana
which half of the rows do you want ?
你想要哪一半的行？
this gets every other row...
这会每隔一行...
Select * from table t
Where Mod(rownum, 2) = 0
this gets first half ...
这得到了上半场...
Select * from table t
Where rownum <=
(Select count(*)
from table) / 2
