代码之家 › 专栏 › 技术社区 › We Are All Monica

SQL Server:是否加入包含WITH子句的派生表?

derived-table row-number subquery sql-server

We Are All Monica · 技术社区 · 15 年前

我想加入一个子查询/派生表,该表包含一个WITH子句(WITH子句是筛选所必需的 ROW_NUMBER() = 1 )在teradata中,类似的东西可以正常工作,但是teradata使用 QUALIFY ROW_NUMBER() = 1 而不是with子句。

下面是我的尝试:

-- want to join row with max StartDate on JobModelID
INNER JOIN (
    WITH AllRuns AS (
        SELECT *,
            ROW_NUMBER() OVER (PARTITION BY JobModelID ORDER BY StartDate DESC) AS RowNumber
        FROM Runs
    )
    SELECT * FROM AllRuns WHERE RowNumber = 1
) Runs
ON JobModels.JobModelID = Runs.JobModelID

我做错什么了?

2 回复 | 直到 15 年前

mechanical_meat nazca 15 年前

添加连接条件可能效率较低,但通常对我来说效果很好。

INNER JOIN (
    SELECT *,
           ROW_NUMBER() OVER 
           (PARTITION BY JobModelID 
           ORDER BY StartDate DESC) AS RowNumber
      FROM Runs
    ) Runs
ON JobModels.JobModelID = Runs.JobModelID 
AND Runs.RowNumber = 1

Adriaan Stander 15 年前

您可以使用多个with子句。有点像

;WITH AllRuns AS ( 
        SELECT  *, 
                ROW_NUMBER() OVER (PARTITION BY JobModelID ORDER BY StartDate DESC) AS RowNumber 
        FROM    Runs 
),
Runs AS(
        SELECT  * 
        FROM    AllRuns 
        WHERE   RowNumber = 1
)

SELECT  *
FROM    ... INNER JOIN ( 
        Runs ON JobModels.JobModelID = Runs.JobModelID

有关用法/结构/规则的详细信息,请参见 WITH common_table_expression (Transact-SQL)