DB2中的ROW_NUMBER（） [英] ROW_NUMBER() in DB2

问题描述

如何在DB2数据库的where子句中使用ROW_NUMBER（）。
我在下面尝试过，但是没有用：

How to use ROW_NUMBER() in where clause in DB2 database. I have tried below but it did not work:

SELECT * FROM CSPAPP.LOCATIONS
WHERE (ROW_NUMBER() OVER(ORDER BY LOCATION)) BETWEEN 100 AND 200

它给出了错误：无效的使用聚合函数或OLAP函数。

It gave error : Invalid use of aggregate function or OLAP function.

我还尝试了以下方法：

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

推荐答案

您不能在同一别名上引用别名定义的级别。您需要将其包装到派生表中：

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

这篇关于DB2中的ROW_NUMBER（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！