sql - 按新选择的列排序

Question

我有一个查询，如:

SELECT 
    R.*     
FROM 
    (SELECT A, B, 
            (SELECT smth from another table) as C,
     ROW_NUMBER() OVER (ORDER BY C DESC) AS RowNumber 
     FROM SomeTable) R
WHERE 
     RowNumber BETWEEN 10 AND 20

这给了我一个错误 ORDER BY C DESC .

我明白为什么会导致这个错误，所以我想再添加一个 SELECT与 ORDER BY并且只选择从 10 到 20 的行。但我认为有 3 个嵌套 SELECT 不好。命令。

还有什么可能选择这些行？

Answer 1

列不能引用同一级别的别名，您必须先对其进行表派生，或者使用 CTE。

SELECT 
    R.* , ROW_NUMBER() OVER (ORDER BY C DESC) AS RowNumber     
FROM 
    (SELECT A, B, (SELECT smth from another table) as C
     FROM SomeTable) R
-- WHERE 
     -- but you still cannot do this
     -- RowNumber BETWEEN 10 AND 20

需要这样做:

select S.*
from
(
    SELECT 
        R.* , ROW_NUMBER() OVER (ORDER BY C DESC) AS RowNumber     
    FROM 
        (SELECT A, B, 
                (SELECT smth from another table) as C
         FROM SomeTable) R
) as s
where s.RowNumber between 10 and 20

为了避免深度嵌套并使其至少看起来令人愉快，请使用 CTE:

with R as
(
     SELECT A, B, (SELECT smth from another table) as C
     FROM SomeTable
)
,S AS 
(
    SELECT R.*, ROW_NUMBER() OVER (ORDER BY C DESC) AS RowNumber
    FROM R
)
SELECT S.*
FROM S
WHERE S.RowNumber BETWEEN 1 AND 20