>我正在尝试使用Row_Number,它在 order by 子句中工作正常,但是在 where 子句中使用它时,我得到无效列,这对我来说有意义吗?
有人能解释为什么会这样吗?谢谢一堆
SELECT col1,col2,
ROW_NUMBER() OVER(PARTITION BY col2 ORDER BY col2) as rownr
FROM table1 t1 WITH(NOLOCK)
JOIN table2 t2 WITH(NOLOCK) ON t2.id = t1.id
WHERE rownr > 1
ORDER BY rownr,unit
order by
的逻辑processing
是在select
之后,但where
子句是在select
之前处理的,这就是您收到该错误的原因。
为此,您应该将查询设置为sub-select
并在外部查询中筛选记录
SELECT col1,
col2
FROM (SELECT col1,
col2,
Row_number()OVER(PARTITION BY col2 ORDER BY col2) AS rownr,
unit
FROM table1 t1 WITH(NOLOCK)
JOIN table2 t2 WITH(NOLOCK)
ON t2.id = t1.id) a
WHERE rownr > 1
ORDER BY rownr, unit
有关 SELECT 语句的逻辑处理顺序的详细信息,请查看此处
ROW_NUMBER()
不能用于同一查询。在外部查询中ORDER BY
。
编辑:您不能在同一查询中使用rownr
,因为您正在一个地方计算ROW_NUMBER()。可以在外部查询中访问rownr
。
SELECT * FROM
(
SELECT col1,col2,unit,
ROW_NUMBER() OVER(PARTITION BY col2 ORDER BY col2) as rownr
FROM table1 t1 WITH(NOLOCK)
JOIN table2 t2 WITH(NOLOCK) ON t2.id = t1.id
)TAB
WHERE rownr > 1
ORDER BY rownr,unit