在Where子句中使用带有ROW_NUMBER() OVER(PARTITION BY...)的Select语句

Question

我一定是做错了什么。我尝试使用row_number函数来选择只有1的行号，这样我就总是得到最新的索引。然而，在where子句中无法识别rownum。

SELECT  fs.docu_id as docID 
, fs.field_3 AS ProductNo
, fs.field_4 AS [Status]
, fs.field_5 AS [Index]
,pd.[doku_nr] AS docIDshort
, ROW_NUMBER() OVER(PARTITION BY fs.field_3 ORDER BY fs.field_5 Desc) AS rownum
FROM [table1] fs
JOIN [table2] pd
ON fs.docu_id=pd.docu_id 
AND  fs.field_4 = 'valid'

我漏掉了什么？我需要创建不同的select语句吗？

谢谢。

- Tony Clifton

Answer 1

您不能在 Where 子句中使用窗口函数（只能在 SELECT 或 ORDER BY 中使用）。

但是您可以使用CTE：

WITH CTE
     AS (SELECT fs.docu_id                    AS docID, 
                fs.field_3                    AS ProductNo, 
                fs.field_4                    AS [Status], 
                fs.field_5                    AS [Index], 
                pd.[doku_nr]                  AS docIDshort, 
                Row_number() OVER( 
                    Partition BY fs.field_3 
                    ORDER BY fs.field_5 DESC) AS rownum 
         FROM   [table1] fs 
                JOIN [table2] pd 
                  ON fs.docu_id = pd.docu_id 
                     AND fs.field_4 = 'valid') 
SELECT docid, productno, status, [index], docIDshort 
FROM   CTE
WHERE  rownum = 1 

- Tim Schmelter

---

或者 SELECT TOP(1) WITH TIES ...ORDER BY rownum ; 可以不使用 CTE，但效率会更低。 - Martin Smith