sql-server - T-SQL : WHERE clause in OVER(PARTITION BY. .. 排序依据...)

Question

我有以下查询

;WITH tmp AS
(
    SELECT *, ROW_NUMBER()
    OVER
        (PARTITION BY to_tel, duration, call_date 
        ORDER BY rates_start DESC) as rn
    FROM ##TempTable
)
SELECT *
FROM tmp
WHERE rn = 1
ORDER BY customer_id, to_code, duration

但我想修改它，它不给我最大 rates_start，而是给我特定日期之前的最大 rates_start。有什么办法可以做到这一点吗？

Answer 1

您可以在 cte 部分中添加 WHERE 。我不确定在这种情况下您是否仍想按 call_date 进行分区(我将其删除)。如果需要，请更改 PARTITION BY 部分。

;WITH tmp AS
(
    SELECT *, ROW_NUMBER()
    OVER
        (PARTITION BY to_tel, duration
        ORDER BY rates_start DESC) as rn
    FROM ##TempTable
    WHERE call_date < @somedate
)
SELECT *
FROM tmp
WHERE rn = 1
ORDER BY customer_id, to_code, duration