CTEs e queries recursivas em SQL Server

CTE é o acrónimo para Common Table Expressions.

As CTEs podem ser usadas para construir queries recursivas, na medida em que as CTEs podem ter referências para elas próprias. Apresenta-se, agora, a sintaxe básica de uma CTE para queries recursivas:

WITH cte_name (column_list) AS
(anchor_member
UNION ALL
recursive_member)
outer_query

A cláusula WITH é composta por duas queries ligadas pelo operador UNION ALL.
O primeiro operando do UNION ALL não faz referência à CTE (anchor_member). O segundo faz referência à CTE e representa a recursão (recursive_member).

Na primeira invocação da recursão, a referência à CTE representa o resultado da primeira query (anchor_member) e a segunda query (recursive_member) usa o resultado da primeira. Nas invocações seguintes, o sistema executa a segunda query repetidamente (recursive_member). A execução da segunda query termina quando o resultado da invocação anterior da query é vazio.
O operador UNION ALL junta os registos acumulados e os registos obtidos na última invocação. (UNION ALL significa que registos duplicados não são eliminados)

A outer_query especifica a query que usa a CTE para obter o resultado da união de ambos os membros do operador UNION ALL.

Exemplo para o Menu do Dia

CREATE TABLE [dbo].[MNU_tblMenuDia](
[ParagrafoID] [int] NOT NULL,
[ParagrafoPaiID] [int] NULL,
[Texto] [varchar](1024) NOT NULL,
[Ambito] [varchar](8) NOT NULL
)
GO

INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(1,NULL,'Menu do Dia','All')
INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(2,1,'Pratos de Carne','All')
INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(7,4,'Pratos de Peixe','All')
INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(3,2,'Almondegas com molho de tomate','Seg')
INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(4,2,'Peru recheado','Ter')
INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(8,7,'Solha gratinada','Seg')
INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(9,7,'Bacalhau à senhor prior','Ter')
INSERT [dbo].[MNU_tblMenuDia] ([ParagrafoID],[ParagrafoPaiID],[Texto],[Ambito])VALUES(12,9,'Restaurante Nicolao','All')

A tabela MNU_tblMenuDia é composta por quatro colunas. A coluna Texto especifica o texto do menu; a coluna Ambito especifica a qualidade o texto, se específico de um dia da semana ou se geral.

Conteudo da tabela MNU_tblMenuDia
1 NULL Menu do Dia All
2 1 Pratos de Carne All
7 4 Pratos de Peixe All
3 2 Almondegas com molho de tomate Seg
4 2 Peru recheado Ter
8 7 Solha gratinada Seg
9 7 Bacalhau à senhor prior Ter
12 9 Restaurante Nicolao All

Apresenta-se, agora, a cláusula WITH que especifica a query para apresentação do Menu de Terça-feira:

WITH Menu(ParagrafoID, ParagrafoPaiID, Texto, Ambito, nivel) AS
(
SELECT ParagrafoID,
ParagrafoPaiID, Texto, Ambito,
0 as nivel
FROM
sin.[MNU_tblMenuDia]
WHERE ParagrafoPaiID IS NULL
UNION ALL
SELECT sin.[MNU_tblMenuDia].ParagrafoID,
sin.[MNU_tblMenuDia].ParagrafoPaiID, sin.[MNU_tblMenuDia].Texto, sin.[MNU_tblMenuDia].Ambito,
nivel + 1
FROM
sin.[MNU_tblMenuDia] INNER JOIN Menu ON sin.[MNU_tblMenuDia].ParagrafoPaiID = Menu.ParagrafoID
)
select distinct ParagrafoID, ParagrafoPaiID, Texto, nivel, Ambito
from Menu
where Ambito in ('All', 'Ter')
GO

O SELECT apresenta o seguinte resultado
1 NULL Menu do Dia 0 All
2 1 Pratos de Carne 1 All
4 2 Peru recheado 2 Ter
7 4 Pratos de Peixe 3 All
9 7 Bacalhau à senhor prior 4 Ter
12 9 Restaurante Nicolao 5 All



Apresenta-se, de seguida, a cláusula WITH que especifica a query para apresentação do Menu de Segunda-feira:

WITH Menu(ParagrafoID, ParagrafoPaiID, Texto, Ambito, nivel) AS
(
SELECT ParagrafoID,
ParagrafoPaiID, Texto, Ambito,
0 as nivel
FROM
sin.[MNU_tblMenuDia]
WHERE ParagrafoPaiID IS NULL
UNION ALL
SELECT sin.[MNU_tblMenuDia].ParagrafoID,
sin.[MNU_tblMenuDia].ParagrafoPaiID, sin.[MNU_tblMenuDia].Texto, sin.[MNU_tblMenuDia].Ambito,
nivel + 1
FROM
sin.[MNU_tblMenuDia] INNER JOIN Menu ON sin.[MNU_tblMenuDia].ParagrafoPaiID = Menu.ParagrafoID
)
select distinct Texto, nivel
from Menu
where Ambito in ('All', 'Seg') order by nivel
GO

O SELECT apresenta o seguinte resultado
Menu do Dia 0
Pratos de Carne 1
Almondegas com molho de tomate 2
Pratos de Peixe 3
Solha gratinada 4
Restaurante Nicolao 5



Restrições a uma CTE para queries recursivas:
• A definição da CTE deve ter pelo menos dois SELECTs (anchor_member & recursive_member) combinados pelo operador UNION ALL.
• Ambos os membros têm de ter o mesmo número de colunas (consequência directa do uso do UNION ALL).
• As colunas de ambos os membros têm de ser dos mesmos tipos de dados.
• A cláusula FROM do membro recursivo só pode referir uma vez o nome da CTE.
• Não são permitidas as opções seguintes no membro recursivo:
* SELECT DISTINCT
* GROUP BY
* HAVING
* Aggregation Functions
* TOP
* Subquerires (Salientar que a única operação de JOIN permitida neste membro é INNER JOIN.)

Licença CC BY-SA 4.0 Silvia Pinhão Lopes, 9.6.18