我有一个 SQL 查询,进行了一些排名操作,如下所示:
SELECT RANK() OVER(PARTITION BY XXX ORDER BY yyy,zzz,oooo) as ranking, *
FROM SomeTable
WHERE ranking = 1 --> this is not possible
我希望在WHERE语句的结尾中使用该排名。
现在我将此查询嵌套在另一个查询中,并在那里对排名进行过滤,但是否有更简单或更快速的方法可以从SELECT语句中过滤这些值?
我有一个 SQL 查询,进行了一些排名操作,如下所示:
SELECT RANK() OVER(PARTITION BY XXX ORDER BY yyy,zzz,oooo) as ranking, *
FROM SomeTable
WHERE ranking = 1 --> this is not possible
我希望在WHERE语句的结尾中使用该排名。
现在我将此查询嵌套在另一个查询中,并在那里对排名进行过滤,但是否有更简单或更快速的方法可以从SELECT语句中过滤这些值?
;WITH MyCTE AS
(
SELECT
RANK() OVER(PARTITION BY XXX ORDER BY yyy,zzz,oooo) as ranking,
*
FROM SomeTable
)
SELECT *
FROM MyCTE
WHERE ranking = 1 --> this is now possible!
对于之前的帖子我很抱歉,我忘记了:窗口函数只能在select或order by子句中使用。你需要使用子查询:
SELECT * FROM
(
SELECT RANK() OVER(PARTITION BY XXX ORDER BY yyy,zzz,oooo) as ranking, *
FROM SomeTable
) t
WHERE ranking = 1
OR A CTE.
select * from (
select RANK() OVER(PARTITION BY name ORDER BY id) as ranking, *
from PostTypes
) A
where A.ranking = 1
https://data.stackexchange.com/stackoverflow/query/edit/59515