ti-enxame.com

T-SQL JOIN contra uma Expressão de Tabela Comum (CTE)

É possível executar uma subconsulta JOIN em uma expressão de tabela comum? Caso contrário, alguém pode me informar como executar o que estou tentando fazer abaixo? Exemplos seriam excelentes.

Por exemplo:

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]

Aqui está minha consulta completa:

;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)

Muito obrigado!

19
Jared

Quando você define um CTE, o faz antes de qualquer outra consulta. Então você não pode escrever:

LEFT JOIN (
  ;WITH CTE
  ...
)

Como um aparte rápido, as pessoas motivo colocam ; na frente de WITH é porque todas as instruções anteriores precisam ser encerradas. Se os desenvolvedores adquirissem o hábito de finalizar todas as instruções SQL com ; então não seria necessário, mas eu discordo ...

Você pode escrever vários CTEs da seguinte forma:

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

Se você tem vários CTEs, eles precisam estar no início de sua declaração (separados por vírgula e apenas um ;WITH para iniciar a lista de CTEs):

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

e então você pode usar os dois (ou até mais de dois) na sua declaração SELECT.

3
marc_s

CTE múltiplo precisa ser declarado primeiro. Exemplo:

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

SELECT        *
FROM          FOO
LEFT JOIN     CTE_1
LEFT JOIN     CTE_2
3
James Hill
;
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]
2
MatBailie

Você pode ter vários CTEs, acredito; você só precisa colocar os dois no topo.

Veja aqui :

No entanto, você pode definir vários CTEs após a palavra-chave WITH, separando cada CTE por vírgula.

1
Adam V