为什么在sql server ROW_NUMBER()中出现错误?

不能在同一个排名函数结果中筛选 select

with cte_1
as
(
SELECT ROW_NUMBER() over (ORDER BY TelNo ) as RowNum,Telno  FROM [ClubEatc].[dbo].[GetOnlineBills])
SELECT *
FROM cte_1
where RowNum=1

试试看,

SELECT *
FROM ( SELECT ROW_NUMBER() over (ORDER BY TelNo ) as RowNum, Telno  
        FROM [ClubEatc].[dbo].[GetOnlineBills]
     ) AS tbl
WHERE RowNum=1

您得到这个错误是因为您不能在WHERE语句中使用计算列allias,甚至不能在 where ROW_NUMBER() over (ORDER BY TelNo ) = 1 因为这是被禁止的。

您不需要使用CTE或子查询,只需使用 TOP 1 WITH TIES 订购方式: ROW_NUMBER() :

SELECT TOP 1 WITH TIES Telno  
FROM [ClubEatc].[dbo].[GetOnlineBills]
ORDER BY ROW_NUMBER() over (ORDER BY TelNo)