针对公用表表达式 (CTE) 的 T-SQL JOIN

T-SQL JOIN against a Common Table Expression (CTE)

提问by Jared

Is it possible to perform a JOIN sub query against a Common Table Expression? If not, then can someone let me know how to perform what I am trying to do below? Examples would be excellent.

是否可以针对公共表表达式执行 JOIN 子查询？如果没有，那么有人可以让我知道如何执行我在下面尝试执行的操作吗？例子会很好。

For example:

例如：

LEFT JOIN (

            ;WITH [UserDefined]
                AS (SELECT *, -- Make sure we get only the latest revision.
                        ROW_NUMBER() OVER (PARTITION BY [ID]
                                                ORDER BY [RevisionNumber] DESC) AS RN
                    FROM [syn_Change])

            SELECT [UserDefined].[ID]
                ,[UserDefined].[ChangeNumber]
                ,[UserDefined].[Usr_CoResponsibility]
                ,[UserDefined].[Usr_StarFlowStatus]

            FROM [UserDefined]
            WHERE (RN = 1) 

            ) [UserColumns]
ON [UserColumns].[ChangeNumber] = [CTE].[ChangeNumber]

Here is my full query:

这是我的完整查询：

;WITH CTE
    AS (SELECT *, -- Make sure we get only the latest revision.
            ROW_NUMBER() OVER (PARTITION BY [ItemID]
                                    ORDER BY [RevisionNumber] DESC) AS RN
        FROM [dw_Change])

SELECT [CTE].[ItemID]
        ,[CTE].[ViewID]
        ,[CTE].[FolderItemID]
        ,[CTE].[RevisionNumber]
        ,[CTE].[ChangeNumber]
        ,[CTE].[Synopsis]
        ,[CTE].[Description]
        ,[CTE].[EnteredOn]
        ,[CTE].[Responsibility]
        --,[UserColumns].[Usr_CoResponsibility]
        --,[UserColumns].[Usr_StarFlowStatus]
        ,[CTE].[Status] -- This will display the human name on the front-end with code.
        ,[Users].[F7] AS [ResponsibilityName]
        ,[GroupName].[Name] AS [AppGroupName]
        ,[AppName].[Name] AS [AppName]
FROM CTE
LEFT JOIN [S3] [Users] ON [Users].[F0] = [CTE].[Responsibility]
LEFT JOIN (SELECT [Name], [ViewID]
            FROM [dw_Folder]
            WHERE ([FolderItemID] = -1)) [GroupName]
ON [GroupName].[ViewID] = [CTE].[ViewID]

LEFT JOIN (SELECT [Name], [ItemID]
            FROM [dw_Folder]
            WHERE ([FolderItemID] <> -1)) [AppName]
ON [AppName].[ItemID] = [CTE].[FolderItemID]

LEFT JOIN (

            ;WITH [UserDefined]
                AS (SELECT *, -- Make sure we get only the latest revision.
                        ROW_NUMBER() OVER (PARTITION BY [ID]
                                                ORDER BY [RevisionNumber] DESC) AS RN
                    FROM [syn_Change])

            SELECT [UserDefined].[ID]
                ,[UserDefined].[ChangeNumber]
                ,[UserDefined].[Usr_CoResponsibility]
                ,[UserDefined].[Usr_StarFlowStatus]

            FROM [UserDefined]
            WHERE (RN = 1) 

            ) [UserColumns]
ON [UserColumns].[ChangeNumber] = [CTE].[ChangeNumber]

WHERE (RN = 1)

Thanks so much!

非常感谢！

回答by Yuck

When you define a CTE you're doing so before any of the rest of the query. So you can't write:

当您定义 CTE 时，您是在查询的任何其余部分之前这样做。所以你不能写：

LEFT JOIN (
  ;WITH CTE
  ...
)

As a quick aside, the reasonpeople put ;in front of WITHis because all previous statements need to be terminated. If developers could get in the habit of terminating all SQL statements with ;then it wouldn't be necessary, but I digress...

顺便说一句，人们放在前面的原因是因为所有先前的语句都需要终止。如果开发人员可以养成终止所有 SQL 语句的习惯，那么就没有必要了，但我离题了……;WITH;

You can write multiple CTEs like so:

您可以像这样编写多个 CTE：

WITH SomeCTE AS (
  SELECT ...
  FROM ...
), AnotherCTE AS (
  SELECT ...
  FROM ...
)
SELECT *
FROM SomeCTE LEFT JOIN
     AnotherCTE ON ...
;

回答by marc_s

If you have multiple CTE's, they need to be at the beginning of your statement (comma-separated, and only one ;WITHto start the list of CTE's):

如果您有多个 CTE，则它们需要位于语句的开头（逗号分隔，并且只有一个;WITH用于开始 CTE 列表）：

;WITH CTE AS (......),
 [UserDefined] AS (.......)
SELECT.....

and then you can use both (or even more than two) in your SELECTstatement.

然后你可以在你的SELECT语句中同时使用这两个（甚至两个以上）。

回答by James Hill

Multiple CTE need to be declared first. Example:

需要先声明多个 CTE。例子：

WITH CTE_1 AS
(
    ....
),
CTE_2 AS
(
    ...
)

SELECT        *
FROM          FOO
LEFT JOIN     CTE_1
LEFT JOIN     CTE_2

回答by MatBailie

;
WITH
  RANKED_CTE AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY [ItemID] ORDER BY [RevisionNumber] DESC) AS RN
  FROM
    [dw_Change]
)
,
  CTE AS
(
  SELECT
    *
  FROM
    RANKED_CTE
  WHERE
    RN = 1
)
,
  GroupName AS
(
  SELECT
    [Name], [ViewID]
  FROM
    [dw_Folder]
  WHERE
    ([FolderItemID] = -1)
)
,
  AppName AS
(
  SELECT
    [Name], [ItemID]
  FROM
    [dw_Folder]
  WHERE
    ([FolderItemID] <> -1)
)
SELECT [CTE].[ItemID]
        ,[CTE].[ViewID]
        ,[CTE].[FolderItemID]
        ,[CTE].[RevisionNumber]
        ,[CTE].[ChangeNumber]
        ,[CTE].[Synopsis]
        ,[CTE].[Description]
        ,[CTE].[EnteredOn]
        ,[CTE].[Responsibility]
        --,[UserColumns].[Usr_CoResponsibility]
        --,[UserColumns].[Usr_StarFlowStatus]
        ,[CTE].[Status] -- This will display the human name on the front-end with code.
        ,[Users].[F7] AS [ResponsibilityName]
        ,[GroupName].[Name] AS [AppGroupName]
        ,[AppName].[Name] AS [AppName]
FROM
          CTE
LEFT JOIN [S3] [Users]  ON [Users].[F0] = [CTE].[Responsibility]
LEFT JOIN [GroupName]   ON [GroupName].[ViewID] = [CTE].[ViewID]
LEFT JOIN [AppName]     ON [AppName].[ItemID] = [CTE].[FolderItemID]

回答by Adam V

You can have multiple CTEs, I believe; you just have to put them both at the top.

我相信你可以有多个 CTE；你只需要把它们都放在最上面。

See here:

见这里：

You can, however, define multiple CTEs after the WITH keyword by separating each CTE with a comma.

但是，您可以通过用逗号分隔每个 CTE，在 WITH 关键字之后定义多个 CTE。