如何在 AS400 中执行 SQL select top N ...
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2850423/
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 do SQL select top N ... in AS400
提问by James Moore
How do you perform a
你如何执行一个
Select top N * from as400table
type query against an as400/db2 database
针对 as400/db2 数据库键入查询
回答by James Moore
Select col1,col2
from
as400table
where col1='filter'
order by col1
fetch first N row only
Remember to set an ORDER BYclause, because DB2 does not guarantee that the rows returned by FETCH FIRST N ROW ONLYare always the same N.
记住设置ORDER BY子句，因为 DB2 不保证返回的行FETCH FIRST N ROW ONLY总是相同的 N。
回答by ajeh
Strictly, there is no equivalent of TOP N in DB2.
严格来说，DB2 中没有TOP N 的等价物。
SELECT 1 FROM sysibm.sysdummy1
WHERE EXISTS (SELECT 2 FROM sysibm.sysdummy1)
FETCH FIRST ROW ONLY
compiles and runs, but
编译并运行，但是
SELECT 1 FROM sysibm.sysdummy1
WHERE EXISTS (SELECT 2 FROM sysibm.sysdummy1 FETCH FIRST ROW ONLY)
will not compile.
不会编译。
TOP Nand FETCH FIRST Nare not the same. You can only use FETCH FIRSTonce per query, whereas TOP Ncan be used in any sub-select.
TOP N并且FETCH FIRST N不一样。FETCH FIRST每个查询只能使用一次，而TOP N可以在任何子选择中使用。
You can use a window function in a sub-query in order to simulate TOP N:
您可以在子查询中使用窗口函数来模拟TOP N：
select *
from (
select id, row_number()
over (order by id) as rn
from testsch.testtbl
) as r
where r.rn < 100 -- This is N rows you are looking for
This will return exactly 99 rows. I tried that in iSeries 7 and it worked.
这将正好返回 99 行。我在 iSeries 7 中尝试过，它奏效了。
回答by Joseph Morgan
I am only a baby-geek when it comes to IBM--I am a SQL Server guy. But I found the rownumber approach (which I have used successfully in Oracle) did not work in DB2. I used this one:
说到 IBM，我只是个极客——我是一个 SQL Server 人。但是我发现 rownumber 方法（我在 Oracle 中成功使用过）在 DB2 中不起作用。我用过这个：
SELECT
MYFIELD
FROM
"SCHEMANAME"."TABLENAME"
WHERE
FILTERCOL1 = 000001
AND FILTERCOL2 = 1
ORDER BY
MYFIELD DESC FETCH FIRST ROW ONLY
(I ordered descending because I needed the last value.)
（我按降序排序，因为我需要最后一个值。）
Hope this helps. Joey
希望这可以帮助。乔伊
回答by Gunay Anach
Wouldn't it be easier to limit the result instead? Bellow is in order by date and I take the top result
相反，限制结果不是更容易吗？波纹管按日期排序，我取最高结果
SELECT banana_equipment_id
FROM new_banana_equipment
WHERE banana_code=0000001
ORDER BY banana_date DESC
LIMIT 1;
回答by curious_wolf
It's an old thread, thought I would contribute
这是一个旧线程，以为我会做出贡献
Consider using RANK()OVER()for the top n balances?
考虑使用RANK()OVER()前 n 个余额？
WITH RANK_TBL AS
(SELECT FIELD1 AS "ENDING BALANCE",
RANK() OVER(ORDER BY FIELD1 DESC) AS "RANK NUMBER"
FROM LIBRARY/TABLE)
SELECT *
FROM RANK_TBL
WHERE "RANK NUMBER" < 6
