我试图使用row_number over从我的表中选择特定的行。然而,SQL会提示错误信息“无效的列名'ROWNUMBERS'”。有人能帮我纠正吗?
SELECT ROW_NUMBER() OVER (ORDER BY Price ASC) AS ROWNUMBERS, *
FROM Product
WHERE ROWNUMBERS BETWEEN @fromCount AND @toCount
我试图使用row_number over从我的表中选择特定的行。然而,SQL会提示错误信息“无效的列名'ROWNUMBERS'”。有人能帮我纠正吗?
SELECT ROW_NUMBER() OVER (ORDER BY Price ASC) AS ROWNUMBERS, *
FROM Product
WHERE ROWNUMBERS BETWEEN @fromCount AND @toCount
试图在WHERE
子句中引用别名列是行不通的,因为逻辑查询处理正在进行。 WHERE
在SELECT
子句之前被评估。 因此,在评估WHERE
时,列ROWNUMBERS
不存在。
在此示例中引用该列的正确方法是:
SELECT a.*
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Price ASC) AS ROWNUMBERS, *
FROM Product) a
WHERE a.ROWNUMBERS BETWEEN @fromCount AND @toCount
以下是操作顺序的参考:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
这里有个解决特定错误的答案。但我也想解决更普遍的问题。看起来你在这里做的很类似于为显示分页结果而进行分页操作。如果是这种情况,并且你可以使用 Sql Server 2012,现在有更好的方法了。请查看OFFSET/FETCH
:
SELECT First Name + ' ' + Last Name
FROM Employees
ORDER BY First Name
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;