SQL DB2 中的 ROW_NUMBER()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27183941/
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
ROW_NUMBER() in DB2
提问by Mandar
How to use ROW_NUMBER() in where clause in DB2 database. I have tried below but it did not work:
如何在 DB2 数据库中的 where 子句中使用 ROW_NUMBER()。我在下面尝试过，但没有用：
SELECT * FROM CSPAPP.LOCATIONS
WHERE (ROW_NUMBER() OVER(ORDER BY LOCATION)) BETWEEN 100 AND 200
It gave error : Invalid use of aggregate function or OLAP function.
它给出了错误：聚合函数或 OLAP 函数的使用无效。
I also tried with followiong ways :
我也尝试了以下方法：
SELECT (ROW_NUMBER() OVER(ORDER BY LOCATION)) AS RN ,* FROM CSPAPP.LOCATIONS
WHERE RN < 200
SELECT (ROW_NUMBER() OVER(ORDER BY LOCATION)) AS RN ,LOCATION FROM CSPAPP.LOCATIONS
WHERE RN < 200
回答by a_horse_with_no_name
You can't reference an alias on the same level where it is defined. You need to wrap this into a derived table:
您不能在定义它的同一级别上引用别名。您需要将其包装到派生表中：
SELECT location
FROM (
SELECT row_number() over(order by location) as rn,
location
FROM cspapp.locations
)
WHERE rn < 200
回答by clutton
I use something like this when selecting based on row number in iSeries DB2:
在基于 iSeries DB2 中的行号进行选择时，我使用了类似的方法：
SELECT *
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY location) as RRN, *
FROM CSPAPP.LOCATIONS
)
WHERE RRN between 100 and 200
If you are only interested in the 1 field you may be able to assign a name to the select and reference the fields:
如果您只对 1 字段感兴趣，您可以为选择指定一个名称并引用这些字段：
SELECT DATA.location
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY location) as RRN, *
FROM CSPAPP.LOCATIONS
) as DATA
WHERE DATA.RRN between 100 and 200
回答by Sinisa Hajnal
You could try FETCH FIRST 200 ROWS ONLY instead of row_number. Write your select as you would normally without ROW_NUMBER, order by whatever you need and FETCH FIRST x.
您可以尝试 FETCH FIRST 200 ROWS ONLY 而不是 row_number。像往常一样在没有 ROW_NUMBER 的情况下编写您的选择，按您需要的任何内容排序，然后先取 x。
selecting all columns with "*" is not a good practice especiallyif you have 600+ columns (and that by itself is bad design of the database).
选择所有带有“*”的列并不是一个好习惯，特别是如果您有 600 多列（这本身就是数据库的糟糕设计）。
回答by Md. Kamruzzaman
Without using row_number() function:
不使用 row_number() 函数：
SELECT * FROM
(SELECT * FROM CSPAPP.LOCATIONS ORDER BY LOCATION FETCH FIRST 200 rows only)
ORDER BY LOCATION DESC FETCH FIRST 100 rows only;
With Row number:
SELECT ROW_NUMBER() OVER(ORDER BY LOCATIONS), LOCATIONS as RNM FROM
(SELECT * FROM CSPAPP.LOCATIONS ORDER BY LOCATIONS FETCH FIRST 200 rows only)
ORDER BY LOCATIONS DESC FETCH FIRST 100 rows only;
回答by Ravindra Kumar
You can reference an alias on the same level where it is defined. You need to wrap this into a derived table:
您可以在定义它的同一级别上引用别名。您需要将其包装到派生表中：
SELECT T1.* FROM(
SELECT row_number() over(order by location) as rn ,L.*
FROM cspapp.locations L) As T1
WHERE T1.rn < 200
But you should understand *is never be a best practice. You should use the column name rather than *(L.col1).
但是你应该明白*从来都不是最佳实践。您应该使用列名而不是*( L.col1)。
