Quand utiliser l'expression de table commune (CTE)

Question

J'ai commencé à lire sur l'expression de la table commune et ne peux pas penser à une cas d'utilisation où j'aurais besoin de l'utiliser. Cela semblerait redondant, car on peut faire la même chose avec les tables dérivées. Y a-t-il quelque chose qui me manque ou que je ne comprends pas bien? Quelqu'un peut-il me donner un exemple simple de limitations avec des requêtes de sélection régulières, dérivées ou de tables temporaires pour rendre le cas de CTE? Des exemples simples seraient très appréciés.

Answer 1

Un exemple, si vous avez besoin de faire référence à rejoindre le même ensemble de données plusieurs fois, vous pouvez le faire en définissant une expression de table commune. donc, peut-être une forme de code de la réutilisation.

Un exemple de l'auto référencement est la récursivité: Requêtes Récursives à l'Aide de la CTE

Passionnant de Microsoft definitions Extraits de Livres en Ligne:

Un CTE peut être utilisé pour:

• Créer une requête récursive. Pour plus d'informations, consultez Requêtes Récursives à l'Aide d'Expressions de Table Communes.

• Substitut pour un affichage lors de l'utilisation générale de vue n'est pas nécessaire; c'est, vous n'avez pas à stocker la définition dans les métadonnées.

• Permettre le regroupement en une colonne qui est dérivé à partir d'un scalaire sous-sélection, ou une fonction qui n'est pas déterministe ou a accès externe.

• Référence de la table résultante de multiples fois dans la même instruction.

Answer 2

- Je les utiliser pour briser des requêtes complexes, particulièrement complexe, jointures et sous-requêtes. Je trouve que je suis en utilisant de plus en plus comme des "pseudo-point de vue" pour m'aider à obtenir ma tête autour de l'intention de la requête.

Ma seule plainte à leur sujet est qu'ils ne peuvent pas être ré-utilisé. Par exemple, j'ai peut-être une procédure stockée avec deux mises à jour des déclarations qui pourraient utiliser les mêmes CTE. Mais la "portée" de la CTE est de la première requête.

La difficulté est, "de simples exemples' n'est probablement pas vraiment besoin de CTE!

Encore, très pratique.

Answer 3

Je vois deux raisons d’utiliser cte.

Pour utiliser une valeur calculée dans la clause where. Cela me semble un peu plus propre qu'une table dérivée.

Supposons qu'il y ait deux tables - Questions et réponses réunies par Questions.ID = Answers.Question_Id (et l'ID du quiz)

 WITH CTE AS
(
    Select Question_Text,
           (SELECT Count(*) FROM Answers A WHERE A.Question_ID = Q.ID) AS Number_Of_Answers
    FROM Questions Q
)
SELECT * FROM CTE
WHERE Number_Of_Answers > 0
 

Voici un autre exemple où je veux obtenir une liste de questions et réponses. Je veux que les réponses soient regroupées avec les questions dans les résultats.

 WITH cte AS
(
    SELECT [Quiz_ID] 
      ,[ID] AS Question_Id
      ,null AS Answer_Id
          ,[Question_Text]
          ,null AS Answer
          ,1 AS Is_Question
    FROM [Questions]

    UNION ALL

    SELECT Q.[Quiz_ID]
      ,[Question_ID]
      ,A.[ID] AS  Answer_Id
      ,Q.Question_Text
          ,[Answer]
          ,0 AS Is_Question
        FROM [Answers] A INNER JOIN [Questions] Q ON Q.Quiz_ID = A.Quiz_ID AND Q.Id = A.Question_Id
)
SELECT 
    Quiz_Id,
    Question_Id,
    Is_Question,
    (CASE WHEN Answer IS NULL THEN Question_Text ELSE Answer END) as Name
FROM cte    
GROUP BY Quiz_Id, Question_Id, Answer_id, Question_Text, Answer, Is_Question 
order by Quiz_Id, Question_Id, Is_Question Desc, Name
 

Answer 4

Aujourd'hui, nous allons apprendre expression de table Commune qui est une nouvelle fonctionnalité qui a été introduite dans SQL server 2005 et disponible dans les versions ultérieures.

Expression de table commune :- expression de table Commune peut être définie comme un résultat temporaire ensemble ou, en d'autres termes, son substitut de vues dans SQL Server. Expression de table commune n'est valable que dans le lot de déclaration où il a été défini et ne peut pas être utilisé dans d'autres sessions.

La syntaxe de déclaration des CTE(Common table expression) :-

with [Name of CTE]
as
(
Body of common table expression
)

Prenons un exemple :-

CREATE TABLE Employee([EID] [int] IDENTITY(10,5) NOT NULL,[Name] [varchar](50) NULL)

insert into Employee(Name) values('Neeraj')
insert into Employee(Name) values('dheeraj')
insert into Employee(Name) values('shayam')
insert into Employee(Name) values('vikas')
insert into Employee(Name) values('raj')

CREATE TABLE DEPT(EID INT,DEPTNAME VARCHAR(100))
insert into dept values(10,'IT')
insert into dept values(15,'Finance')
insert into dept values(20,'Admin')
insert into dept values(25,'HR')
insert into dept values(10,'Payroll')

J'ai créé deux tables employé et Dept et inséré 5 lignes dans chaque table. Maintenant, je tiens à associer à ces tables et créer un résultat temporaire mis à l'utiliser davantage.

With CTE_Example(EID,Name,DeptName)
as
(
select Employee.EID,Name,DeptName from Employee 
inner join DEPT on Employee.EID =DEPT.EID
)
select * from CTE_Example

Permet de prendre chaque ligne de la déclaration de l'un par un et de les comprendre.

Pour définir CTE nous écrire "avec" la clause, puis nous donner un nom à la table de l'expression, ici, j'ai donné le nom de "CTE_Example"

Puis nous écrivons "Comme", et de joindre notre code en deux tranches (---), on peut joindre plusieurs tables dans le joint entre parenthèses.

Dans la dernière ligne, j'ai utilisé "Select * from CTE_Example" , nous faisons référence à l'expression de table Commune dans la dernière ligne de code, on peut Donc dire que Sa comme une vue, où nous sommes à la définition et à l'utilisation de la vue en un seul lot et de la CTE est pas stocké dans la base de données comme un objet permanent. Mais il se comporte comme un point de vue. nous pouvons effectuer la suppression et la mise à jour de la déclaration sur la CTE, et qui auront un impact direct sur la table référencée ceux-ci sont utilisés dans les CTE. Prenons un exemple pour comprendre ce fait.

With CTE_Example(EID,DeptName)
as
(
select EID,DeptName from DEPT 
)
delete from CTE_Example where EID=10 and DeptName ='Payroll'

Dans la déclaration ci-dessus, nous sommes la suppression d'une ligne de CTE_Example et il va supprimer les données de la table référencée "DEPT" qui est utilisée dans le CTE.

Answer 5

BrianK

Je pense que vous rendez ce sujet un peu plus compliqué que nécessaire. La clause HAVING est très utile dans ces situations et aide à garder les choses lisibles. Parfois, le CTE peut ajouter une couche supplémentaire de lecture lorsqu'une requête suffit.

Essaye ça:

 SELECT Question_Text, 
       Count(A.*) As Number_Count
FROM Questions Q
INNER JOIN Answers A on Q.Question_ID = A.Question_ID
GROUP BY Q.Question_Text
HAVING Count(A.*) > 0