我在where子句中发现了一个用Row_Number()
函数回答的问题。当我尝试一个查询时,我得到了以下错误:
“消息4108,级别15,状态1,行1窗口函数只能出现在SELECT或ORDER BY子句中。”
下面是我尝试过的查询。如果有人知道如何解决这个问题,请让我知道。
SELECT employee_id
FROM V_EMPLOYEE
WHERE row_number() OVER ( ORDER BY employee_id ) > 0
ORDER BY Employee_ID
发布于 2009-09-23 16:04:24
要解决此问题,请将select语句包装在CTE中,然后可以对CTE进行查询,并在where子句中使用窗口函数的结果。
WITH MyCte AS
(
select employee_id,
RowNum = row_number() OVER ( order by employee_id )
from V_EMPLOYEE
ORDER BY Employee_ID
)
SELECT employee_id
FROM MyCte
WHERE RowNum > 0
发布于 2009-09-23 16:04:09
SELECT employee_id
FROM (
SELECT employee_id, ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
FROM V_EMPLOYEE
) q
WHERE rn > 0
ORDER BY
Employee_ID
请注意,此筛选器是冗余的:ROW_NUMBER()
从1
开始,并且始终大于0
。
发布于 2012-03-19 13:54:47
Select * from
(
Select ROW_NUMBER() OVER ( order by Id) as 'Row_Number', *
from tbl_Contact_Us
) as tbl
Where tbl.Row_Number = 5
https://stackoverflow.com/questions/1466963
复制相似问题