Home

Combinaison des Enregistrements

 

Sélection dans différentes tables

 

Introduction

Dans une fenêtre de requête ou dans le concepteur de requêtes, vous pouvez afficher les enregistrements d'autant de tables que vous voulez et ces tables n'avaient pas besoin d'avoir quelque chose en commun. Ils ne doivent même pas appartenir à la même base de données. Considérez la base de données ci-après nommée Cruise équipée d'une table nommée cabines :

CREATE DATABASE Cruise 
GO 
USE Cruise ; 
GO
 
 
CREATE TABLE Cabins(
    CabinType nvarchar (20) not-null, 
    Deck nchar(20), 
    Size int, 
    Rate1Passenger money, 
    Rate2Passengers money); 
GO 
INSERT INTO Cabins--Size in sq/ft 
VALUES(N'Inside', N'Riviera', 215, 289.00, 578.00), 
      (N'Outside', N'Riviera', 185, 319.00, 638.00), 
      (N'Outside', N'Riviera', 225, 389.00, 778.00), 
      (N'Suite', N'Verandah', 295, 1009.00, 2018.00), 
      (N'Inside', N'Upper', 185, 379.00, 758.00), 
      (N'Inside', N'Main', 215, 359.00, 718.00), 
      (N'Outside', N'Riviera', 185, 349.00, 698.00), 
      (N'Suite', N'Main', 300, 885.00, 1680.00)

Voici une autre base de données nommée Video Collection avec une table nommée Videos :

CREATE DATABASE VideoCollection 
GO 
 
 
USE VideoCollection 
GO 
 
 
CREATE TABLE Videos 
(
    Title nvarchar(50), 
    Director nvarchar(50), 
    WideScreen bit, 
    Rating nchar(10), 
    YearReleased int
) 
GO 
 
 
INSERT INTO Videos 
VALUES(N'War of the Roses (The)', N'Dany de Vito', 0, N'R', 2001), 
      (N'Memoirs of a Geisha', N'Rob Marshall', 1, N'PG-13', 2006), 
      (N'Last Castle (The)», N'Rod Lurie', 1, N'', 2001), 
      ('N'Sneakers, N'Phil Alden Robinson', 1, N'PG-13', 2003); 
GO

Pour afficher les enregistrements de plus d'une table, dans une fenêtre de requête, écrivez une instruction SELECT pour chaque table et éxécutez-la. Si les tables appartiennent à différentes bases de données, assurez-vous que vous le signalez. Voici un exemple :

USE Cruise ; 
GO 
SELECT CabinType AS [Cabin Type], Desk, Size AS [Size in  sq/ft, 
       Rate1Passenger AS [Rate for 1 Pasenger], 
       Rate2Passengers AS [Rate for 2 Passengers] 
FROM Cabins
GO 
 
 
USE VideoCollection ; 
GO 
SELECT Title, Director, WideScreen As [Has Wide Screen], 
       Rating, YearReleased AS [(c)Year] 
FROM Videos ; 
GO

Lors de l'exécution, la partie inférieure de la fenêtre affiche les enregistrements des tables :

Showing Records of Various Tables

 

 
CREATE DATABASE Cruise 
GO 
USE Cruise ; 
GO 
 
 
CREATE TABLE Cabins (
     CabinType nvarchar (20) not null, 
     Deck nchar(20), 
     Size int, 
     Rate1Passenger money, 
     Rate2Passengers money); 
GO 
INSERT INTO Cabins--Size in sq/ft 
VALUES(N'Inside', N'Riviera', 215, 289.00, 578.00), 
      (N'Outside', N'Riviera', 185, 319.00, 638.00), 
      (N'Outside', N'Riviera', 225, 389.00, 778.00), 
      (N'Suite', N'Verandah', 295, 1009.00, 2018.00), 
      (N'Inside', N'Upper', 185, 379.00, 758.00), 
      (N'Inside', N'Main', 215, 359.00, 718.00), 
      (N'Outside', N'Riviera', 185, 349.00, 698.00), 
      (N'Suite', N'Main', 300, 885.00, 1680.00)

Voici une autre base de données nommée Video Collection avec une table nommée Videos :

CREATE DATABASE 
VideoCollection 
GO 
 
 
USE VideoCollection 
GO 
 
 
CREATE TABLE Videos 
(
    Title nvarchar(50), 
    Director nvarchar(50), 
    WideScreen bit, 
    Rating nchar(10), 
    YearReleased int
) 
GO 
 
 
INSERT INTO Videos 
VALUES(N'War of the Roses (The)', N'Dany de Vito', 0, N'R ', 2001), 
      (N'Memoirs of a Geisha',  N'Rob Marshall', 1, N'PG-13 ', 2006), 
      (N'Last Castle (The)», N'Rod Lurie ', 1, N '', 2001), 
      ('N'Sneakers, N'Phil Alden Robinson ', 1, N'PG-13 ', 2003); 
GO

Pour afficher les enregistrements de plus d'une table, dans une fenêtre de requête, écrivez une instruction SELECT pour chaque table et l'exécutez. Si les tables appartiennent à différentes bases de données, assurez-vous que vous le signalez. Voici un exemple :

USE Cruise ; 
GO 
SELECT CabinType AS [Cabin Type], Deck, Size AS [Size in sq/ft], 
       Rate1Passenger AS [Rate for 1 Passenger], 
       Rate2Passengers AS [Rate for 2 Passengers] 
FROM Cabins ;
GO 
 
 
USE VideoCollection ; 
GO 
SELECT Title, Director, WideScreen As [Has Wide Screen], 
         Rating, YearReleased AS [(c)Year] 
FROM Videos ; 
GO

Lors de l'exécution, la partie inférieure de la fenêtre affiche les enregistrements des tables :

Showing Records of Various Tables

Practical LearningEtude pratique : Présentation des syndicats

  1. Démarrez Microsoft SQL Server et connectez-vous au serveur
  2. Dans la barre d'outils standard, cliquez sur le bouton Nouvelle requête
  3. Pour créer une nouvelle base de données, tapez ce qui suit :
    USE master
    GO
     
    IF EXISTS(SELECT name 
       FROM sys.databases 
       WHERE name = N'MusicCollection'
    ) 
    DROP DATABASE MusicCollection 
    GO 
    
    CREATE DATABASE MusicCollection 
    GO
     
    USE MusicCollection 
    GO
    
    CREATE TABLE AfricanPop (
        Artist nchar(50),  
        AlbumName nvarchar(50), 
        Releaser nvarchar(50), 
        CopyrightYear int); 
    GO
    
    CREATE TABLE Rock (
        Musician nvarchar(50), 
        Title nvarchar(50) not null, 
        RecordLabel nvarchar(50), 
        YearReleased smallint); 
    GO
    
    INSERT INTO AfricanPop 
    VALUES(N'Salif Keita', N'Folon... The Past', N'Mango ', 1995), 
          (N'Vincent Nguini ', N'Symphony-Bantu', N'Mesa Records', 1994), 
          (N'Tshala Muana', N'Mutuashi ', N'Stern''s Music ', 1996); 
    GO
    
    INSERT INTO Rock 
    VALUES(N'Taylor Dayne', N'Can''t Fight Fate', N'Arista Records', 1989), 
          (N'Cyndi Lauper', N'She''s So Unusual', N'CBS', 1983), 
          (N'Beverly Hills COP ', N'Soundtrack ', N'MCA Records', 1984), 
          (N'Michael Jackson ', 'N'Dangerous, N'MJJ Productions, 1991), 
          (N'Bruce Hornsby and the Range', N'The Way it is', 
           N'Arista/Ariola International ', 1986); 
    GO
  4. Appuyez sur F5 pour exécuter
  5. Pour visualiser les enregistrements de deux tables, remplacez le code contenu dans la fenêtre de requête avec :
    USE MusicCollection 
    GO 
    SELECT * FROM AfricanPop ; 
    GO 
    SELECT ALL * FROM Rock ; 
    GO
  6. Appuyez sur la touche F5 pour voir le résultat

Music Collection

Union des Records

Parfois, pour des raisons de comparaison des enregistrements ou pour vous préparer à les fusionner, vous pouvez afficher, dans une vue, les enregistrements de plus d'une table.

Pour prendre en charge la possibilité de sélectionner les enregistrements des tables différentes et de les afficher ensemble, utilisez l'opérateur UNION. La formule de base à suivre est la suivante :

SELECT What FROM OneTable 
UNION 
SELECT What FROM AnotherTable ;

Il y a des règles à suivre :

  • Les deux tables doivent avoir le même nombre de colonnes
  • La séquence des types de données des colonnes de chaque table doit être la même. Par exemple, si la colonne d'une table est basée sur la chaîne, la colonne correspondante dans l'autre table doit également être basée sur la chaîne
  • Les types de données dans l'ordre des colonnes de deux tables doivent être compatibles. Par exemple, si la première colonne d'une table a un type de données d'un nombre entier, la première colonne de l'autre table doit également avoir un type de données basées sur le nombre entier qui peut être concilié avec la colonne correspondante de l'autre table
  • Les colonnes ne doivent pas nécessairerement avoir le même nom

Practical LearningEtude pratique : Union des Records

  1. Remplacez le code contenu dans la fenêtre de requête avec :
    USE MusicCollection ; 
    GO
    
    SELECT * FROM AfricanPop 
    UNION 
    SELECT * FROM Rock ; 
    GO
  2. Appuyez sur la touche F5 pour l'exécuter.
    Cela donnerait :

    UNION

  3. Notez que, par défaut, le studio utilise les noms de colonne de la première table sous la forme d'en-têtes. Bien entendu, si vous le souhaitez, vous pouvez spécifier les légendes si vous le souhaitez, à l'aide de l'opérateur AS.
    Modifiez le code comme suit :
    USE MusicCollection; 
    GO
    
    SELECT Artist AS [Musician], AlbumName AS [Album Title], 
             Releaser AS [Label], CopyrightYear AS [(c)Year] 
    FROM AfricanPop 
    UNION
    SELECT ALL * FROM Rock ; 
    GO
  4. Appuyez sur la touche F5 pour exécuter.
    Cela donnerait :

    UNION

  5. Sélectionnez et supprimez tout dans la fenêtre de requête

Copie de documents


Introduction

Imaginez que vous avez deux tables qui sont censées contenir les mêmes valeurs. Peut-être que les tables ont été créées par des personnes différentes pour le même but. Il y a peut-être une ancienne table contenant les enregistrements de transactions d'affaires précédentes et il y a une nouvelle table avec les nouveaux enregistrements. À un moment donné, vous pouvez fusionner ces enregistrements. Vous disposez de différentes options.

Considérez les tableaux suivants :

USE Exercise ; 
GO 
 
 
CREATE TABLE Seasonals
(
    ContractorCode nchar(10), 
    FirstName nvarchar (20), 
    LastName nvarchar (20), 
    Wage money
); 
 
 
INSERT INTO Seasonals 
VALUES(N'86824', N'Julie', N'Chance', 12.84), 
      (N '84005', N'Ayinda ', N'Kaihibu ', 9.52); 
GO 
 
 
CREATE TABLE Employees 
(
    EmplNbr nchar(10), 
    FirstName nvarchar (20), 
    LastName nvarchar (20), 
    HourlySalary money
); 
 
 
INSERT INTO Employees 
VALUES(N'22684', N'Ann', N'Keans', 20.52), 
      (N '48157', N'Godwin ', N'Harrison ', 18,75), 
      (N '82476', N'Timothy ', N'Journ ', 28.04), 
      (N '15007', N'Ralph ', N'Sunny ', 22.64); 
GO
 
 
 

Copie d'une table

La copie des enregistrements se compose du fait de les transférer d'une table, la source, à une autre table, la cible. Vous pouvez copier tous les enregistrements de la source vers la cible. Vous pouvez sélectionner les colonnes à copier. Ou vous pouvez indiquer dans quelles conditions certains enregistrements pourraient être copiés.

Pour copier tous les enregistrements d'une table à l'autre, la source et la cible doivent avoir le même nombre de colonnes, la même séquence de colonnes à l'égard de leurs types de données. Pour copier les enregistrements, démarrez une instruction INSERT ou INSERT INTO. Au lieu du mot-clé VALUES, créez une instruction SELECT qui implique la table source. Voici un exemple :

USE Exercise ; 
GO

INSERT INTO Employees SELECT * FROM Seasonals ; 
GO

Une fois que cette instruction a été exécutée, tous les enregistrements issus de la table source sont copiés vers la table cible :

Employees

Si vous utilisez la formule ci-dessus, les enregistrements d'une colonne de la table source pourraient être copiés à la colonne correspondante de la table. Parfois, vous souhaitez fusionner les tables que ni l'une ni l'autre ne partagent la même séquence de colonnes ni ont le même nombre de colonnes. Considérez les tableaux suivants :

USE Exercise ; 
GO 
 
 
CREATE TABLE Seasonals 
(
     ContractorCode nchar(10), 
     Wage money, 
     LastName nvarchar (20) 
     FirstName nvarchar (20)
); 
 
 
INSERT INTO Seasonals 
VALUES(N'86824', 12.84, N'Chance', N'Julie'), 
      (N'84005', 9.52, N'Kaihibu ', N'Ayinda '); 
GO 
 
 
 
CREATE TABLE Employees
 (
    EmplNbr nchar(10), 
    EmployeeName nvarchar(50), 
    HourlySalary d'argent
); 
 
 
INSERT INTOEmployees 
VALUES(N'22684', N'Ann Keans', 20.52), 
      (N '48157', Harrison N'Godwin ', 18,75), 
      (N '82476 ', journal N'Timothy', 18,75), 
      (N '15007 ', station N'Ralph', 18,75); 
GO

Dans un tel cas, avant de copier les enregistrements, Vous devez analyser la table pour trouver un moyen de converger les enregistrements. Voici un exemple :

USE Exercise1 ; 
GO 
 
 
INSERT INTO Employees 
SELECT ContractorCode, FirsName+''+LastName, Wage FROM Seasonals ; 
GO 

Employees

De la même façon, vous pouvez définir une condition à suivre lors de la copie des enregistrements.

Fusion des enregistrements

 

Introduction

Imaginez que vous avez deux tables créées à des moments différents, par différentes personnes ou pour des différentes raisons générales. Vous pouvez avoir deux tables qui possèdent des enregistrements en double (le même enregistrement dans plus d'une table, par exemple le même numéro d'employé et le même nom dans deux tables). Vous pouvez avoir des enregistrements dans des tables différentes, mais certains de ces documents partagent la valeur d'un champ (vous pouvez avoir un employé A dans une table et un autre employé B dans une autre table mais tous les deux ont le même numéro d'employé avec des noms différents, peut-être lors de la fusion des deux compagnies). Comme une affectation, vous serez invité à combiner les enregistrements de ces tables en une seule.

Practical LearningEtude pratique : Présentation de fusion

  1. Dans la fenêtre de requête, tapez ce qui suit :
    -- =============================================  
    -- Database:	 CarDealer 
    -- Date Created: Monday 07 September 2009  
    -- Author:		 FunctionX  
    -- ============================================= 
    USE master 
    GO  
     
     
    -- Drop the database if it already exists 
    IF  EXISTS (  	
           SELECT name   		
           FROM sys.databases  
           WHERE name = N'CarDealer' 
    ) 
    DROP DATABASE CarDealer 
    GO 
     
     
    CREATE DATABASE CarDealer
    GO  
     
     
    USE CarDealer;
    GO 
     
     
    -- ============================================= 
    -- Database: CarDealer 
    -- Table:    NewCars 
    -- ============================================= 
    CREATE TABLE NewCars  
    ( 
     	CarCode nchar(20) not null,  
      	CarYear int,  
     	Make nvarchar(40),  
     	Model nvarchar(50),  
     	Price money  );  
    GO    
     
     
    INSERT INTO NewCars 
    VALUES(N'P2740442', 2010, N'Honda', N'Accord', 24650), 
            	 (N'WKL72750', 2009, N'Honda', N'CR-V', 25445),  	
              (N'68471815', 2010, N'Honda', N'Accord', 28245),  
              (N'974PP392', 2009, N'Honda', N'Civic', 22500),  	
              (N'75854H80', 2009, N'Honda', N'Civic Hybrid', 14675),  	
              (N'26RT8137', 2010, N'Honda', N'Insight', 22080); 
    GO  
     
     
    -- ============================================= 
    -- Database: CarDealer 
    -- Table:    UsedVehicles 
    -- ============================================= 
    CREATE TABLE UsedVehicles 
    (  	
            VehicleNumber nchar(20) not null, 
            YearManufactured int, 
            Make nvarchar(40), 
            Model nvarchar(50),  
            Mileage int,  
            OriginalPrice money, 
            CurrentValue money 
    ); 
    GO   
     
     
    INSERT INTO UsedVehicles 
    VALUES(N'984795MM', 1998, N'Ford', N'Escort', 112683, 12420, 3250),  
     	 (N'96304807', 2006, N'Toyota', N'Corolla', 64286, 18855, 12500);  
    GO 
    INSERT INTO UsedVehicles(VehicleNumber, Make, Model, OriginalPrice) 
    VALUES(N'P2740442', N'Honda', N'Accord', 24650),  	
          (N'68471815', N'Honda', N'Accord', 28245),  	
          (N'75854H80', N'Honda', N'Civic Hybrid', 14675); 
    GO
  2. Appuyez sur F5 pour exécuter
  3. Pour visualiser les enregistrements de chaque tableau, remplacez le code contenu dans la fenêtre de requête avec :
    USE CarDealer ; 
    GO 
    SELECT CarCode AS [Car Code], CarYear AS [Year], 
           Make, Model, Price AS [Market Value] FROM NewCars ; 
    GO 
    SELECT VehicleNumber AS [Vehicle #], YearManufactured AS [Year], 
          Make, Model, Mileage, OriginalPrice AS [Original Value], 
          CurrentValue AS [Market Value] FROM UsedVehicles ; 
    GO
  4. Appuyez sur F5 pour exécuter

Car Dealer

La Fusion des Records

La fusion d'un enregistrement consiste à insérer les enregistrements d'une table, dénommée la source, dans une autre table, dénommée la cible. Lorsque vous effectuez cette opération, vous aurez la possibilité de :

  • Insertion de tous les enregistrements de la source vers la cible
  • Mise à jour des enregistrements qui répondent à un critère
  • Suppression de certains enregistrements selon une condition

La formule primaire pour fusionner deux tables est la suivante :

MERGE Table1 AS Target 
USING Table2 AS Source 
ON Table1.CommonField = Table2.CommonField 
WHEN MATCHED Matched Options
   THEN Match Operation (s)
WHEN NOT MATCHED BY TARGET Not Matched By Target Options 
       THEN Not Matched By Target operation (s) 
WHEN NOT MATCHED BY TARGET Not Matched By Target Options 
       THEN Not Matched By Source Operation (s) 

Vous commencez avec l'opérateur MERGE suivi de la table à laquelle les enregistrements seront ajoutés.

Vous poursuivez avec l'opérateur USING suivi de la table à partir de laquelle les enregistrements seront restaurés.

Vous devez spécifier la condition par laquelle les enregistrements doivent correspondre. Pour fusionner les enregistrements, chacune des tables doit être une colonne commune. Les colonnes n'ont pas besoin d'avoir le même nom, mais ils doivent être du même type (et taille). Pour fournir cette information, tapez ON suivi de la condition.

Après avoir spécifié les tables et les enregistrements correspondant aux conditions, vous devez indiquer quoi faire si/quand un enregistrement de la condition source rencontre des enregistrements de la table cible.

Practical LearningEtude pratique : Fusion des Records

  • Supprimez l'ensemble du code dans la fenêtre de requête et remplacez-le avec :
    USE CarDealer ; 
    GO 
     
     
    MERGE UsedVehicles AS Target 
    USING NewCars AS Source 
    ON (Target.VehicleNumber = Source.CarCode) 
    WHEN NOT MATCHED BY Target 
           THEN INSERT (VehicleNumber, YearManufactured, 
                        Make, Model, OriginalPrice) 
                VALUES(CarCode, CarYear, Make, Model, Price) 
    WHEN MATCHED
            THEN UPDATE SET Target.YearManufactured = Source.CarYear, 
                            Target.Make = Source.Make, 
                            Target.Model = Source.Model, 
                            Target.OriginalPrice = Source.Price
     
    GO

Sortir les résultats d'une fusion.

Si vous faites une fusion à l'aide de la formule ci-dessus, après que la fusion ait été effectuée, vous ne sauriez pas le résultat, sauf si vous exécutez une nouvelle requête sur la table cible. Heureusement, vous pouvez demander le moteur de base de données pour afficher immédiatement un résumé de ce qui s'est passé. Pour ce faire, après la dernière instruction THEN, créez une expression OUTPUT

MERGE Table1 AS Target 
USING Table2 AS Source 
ON Table1.CommonField = Table2.CommonField 
WHEN MATCHED Options 
    THEN Match Operation (s)
WHEN NOT MATCHED BY TARGET Not Matched By Target Options 
       THEN Not Matched By Target Operation (s) 
WHEN NOT MATCHED BY SOURCE Not Matched By Source Options
       THEN Not Matched By Source Operation (s)
OUTPUT $action, DELETED | INSERTED | from_table_name.*

Pour obtenir un résumé des opérations fusionnantes, si vous effectuez un seul type d'opération, tapez OUTPUT, suivi de inserted.* ou deleted.*. Si vous effectuez différents types d'opérations, tapez OUTPUT, suivi de $action, suivi de inserted.* ou deleted.* ou tous les deux.

Practical LearningEtude pratique : Les résultats d'une fusion de sortie

  1. Pour visualiser les enregistrements de chaque tableau, ajouter la dernière ligne suivante :
    USE CarDealer ; 
    GO 
     
     
    MERGE UsedVehicles AS Target 
    USING NewCars AS Source 
    ON (Target.VehicleNumber = Source.CarCode) 
    WHEN NOT MATCHED BY Target
           THEN INSERT(VehicleNumber, YearManufactured, 
                       Make, Model, OriginalPrice) 
                VALUES(CarCode, CarYear, Make, Model, Price) 
    WHEN MATCHED
           THEN UPDATE SET Target.YearManufactured = Source.CarYear, 
                           Target.Make = Source.Make, 
                           Target.Model = Source.Model, 
                           Target.OriginalPrice = Source.Price 
    OUTPUT $action, inserted.*, deleted.*, 
    GO
  2. Appuyez sur F5 pour exécuter
  3. Pour afficher les résultats sur la table cible, modifiez l'instruction comme suit :
    USE CarDealer ; 
    GO 
     
     
    SELECT ALL * FROM UsedVehicles ; 
    GO
  4. Appuyez sur F5 pour exécuter

    Car Dealer

Expressions Communes de Tables (CTE)

  

Introduction

Un CTE (Coomon Table Expression) est une sélection temporaire ou une autre opération de requête d'enregistrements d'une ou de plusieurs tables. Utilisez-la pour vous faire une idée de ce que l'opération produirait si exécutée sur une table.Vous pouvez créer un CTE pour créer, sélectionner, fusionner ou supprimer des enregistrements.

Il existe deux types d'expressions de table communes : récursives et non récursives.

Practical LearningEtude pratique : Présentation des expressions de table communes

  1. Supprimez tout dans la fenêtre de requête et remplacez-la par le texte suivant :
    USE master;  
    GO    
     
     
    IF  EXISTS(SELECT name   	
               FROM sys.databases    
               WHERE name = N'CeilInn3' 
    ) 
    DROP DATABASE CeilInn3
    GO  
     
     
    CREATE DATABASE CeilInn3 
    GO 
     
     
    USE CeilInn3;
    GO  
     
     
    CREATE TABLE SleepingRooms (  
        RoomNumber nchar(10) not null,   
        RoomType nvarchar(20) default N'Bedroom',  
        BedType nvarchar(40) default N'Queen',  
        Rate money default 75.85,   
        Available bit default 0 
    );  
    GO  
     
     
    CREATE TABLE ConferenceRooms (  
        RoomNumber nchar(10) not null, 
        RoomType nvarchar(20) default N'Conference',  
        BedType nvarchar(40),  
        Rate money default 75.85,  
        Available bit default 0 
    );
    GO  
     
     
    INSERT INTO SleepingRooms(RoomNumber, BedType, Rate, Available)
    VALUES(N'104', N'Queen', 80.25, 0),  
          (N'105', N'King', 95.50, 1),   
          (N'106', N'Queen', 65.95, 1),  
          (N'107', N'Queen', 65.95, 1),  
          (N'108', N'King', 92.50, 1),   
          (N'109', N'Queen', 68.95, 0),  
          (N'110', N'Queen', 74.95, 1); 
    GO 
     
    INSERT INTO ConferenceRooms(RoomNumber, Rate, Available) 
    VALUES(N'C-120', 525.00, 1); 
    GO 
    INSERT INTO SleepingRooms(RoomNumber, RoomType, BedType, Rate, Available) 
    VALUES(N'116', N'Studio', N'King', 112.95, 0); 
    GO 
    INSERT INTO ConferenceRooms(RoomNumber, Rate, Available)
    VALUES(N'C-122', 450.00, 1); 
    GO 
    INSERT INTO SleepingRooms(RoomNumber, RoomType, BedType, Rate, Available)
    VALUES(N'202', N'Studio', N'King', 105.95, 1),  
          (N'203', N'Studio', N'Queen', 102.50, 1); 
    GO 
    INSERT INTO SleepingRooms(RoomNumber, BedType, Rate, Available)
    VALUES(N'204', N'Double', 115.95, 1),   
         (N'205', N'Queen', 82.95, 0),   
         (N'206', N'King', 98.50, 1)
    GO 
    INSERT INTO ConferenceRooms(RoomNumber, Rate, Available) 
    VALUES(N'C-302', 650.00, 1);
    GO
  2. Appuyez sur F5 pour exécuter

Création d'une Expression de Table Commune

La formule pour créer une expression de table commune (CTE) est la suivante :

[WITH <common_table_expression > [,.. .n]] 
 
 
<common_table_expression >:: =
        expression_name [(column_name [,.. .n])] 
    AS 
        (CTE_query_definition)

Vous commencez avec le mot clé WITH suivi d'un nom pour le jeu temporaire. Le nom mus doit être différent de tout tableau qui sera utilisé dans l'expression de la CTE. Nous verrons plus tard les paramètres que vous pouvez ajouter après le nom. Après le nom, tapez AS suivi des parenthèses. Entre les parenthèses, créez une expression SELECT simple ou composée.

Après le code qui définit le CTE, c'est-à-dire après l'expression AS(CTE_query_definition), créez une instruction SELECT qui produira les résultats.

Practical LearningEtude pratique : Création d'une expression de table commune

  1. Supprimez tout dans la fenêtre de requête et remplacez-la avec :
    USE CeilInn3 ; 
    GO 
     
     
    WITH BedRooms AS 
       (SELECT * FROM SleepingRooms
    ) SELECT * FROM BedRooms ; 
    GO
  2. Appuyez sur F5 pour exécuter

    WITH
  3. Pour visualiser les résultats des deux tables, modifiez l'instruction comme suit :
    USE CeilInn3 ; 
    GO 
     
     
    WITH BedRooms AS 
    (
       SELECT * FROM SleepingRooms
    )
     
     
    SELECT * FROM BedRooms ; 
    SELECT * FROM SleepingRooms ;
    GO
  4. Appuyez sur F5 pour exécuter
  5. Pour voir uniquement une liste des chambres disponibles depuis le CTE, modifiez l'instruction comme suit :
    USE CeilInn3 ; 
    GO 
     
     
    WITH BedRooms AS 
    (
        SELECT * FROM SleepingRooms WHERE Available = 1
    ) 
    SELECT * FROM BedRooms
    GO
  6. Appuyez sur la touche F5 pour voir le résultat

    With

Un CTE avec paramètres

Pour vous assurer que vous pouvez contrôler en externe les résultats d'un CTE, vous pouvez y transmettre un type de paramètre. Pour faire cela, après le nom du CTE et avant l'opérateur AS, ajoutez les parenthèses et passer un ou plusieurs paramètres, chacun représenté par un nom. Les noms des paramètres doivent être exactement les mêmes noms de colonnes de la table dont la déclaration du CTE sera basée. Le nombre de colonnes doit être le même que le nombre de colonnes qui seront impliquées dans la dernière instruction SELECT.

Dans le corps du CTE, utilisez les paramètres comme vous le souhaitez. Par exemple, vous pouvez impliquer les paramètres dans une condition dans le CTE.

Practical LearningEtude pratique : Passer des paramètres à un CTE

  1. Pour passer des paramètres dans le CTE, supprimez tout dans la fenêtre de requête et remplacez-la avec :
    USE CeilInn3 ; 
    GO 
     
     
    WITH BedRooms(RoomNumber, RoomType, BedType, Rate, Available) 
    AS
    (
           SELECT RoomNumber, RoomType, BedType, Rate, Available 
           FROM SleepingRooms 
           WHERE BedType = N'Queen'
    ) 
    SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms
    GO
  2. Appuyez sur F5 pour exécuter

    WITH
  3. Pour voir uniquement une liste des chambres disponibles depuis le CTE, modifiez l'instruction comme suit :
    USE CeilInn3 ; 
    GO
    
    WITH BedRooms(RoomNumber, RoomType, BedType, Rate, Available) 
    AS
    (
         SELECT RoomNumber, RoomType, BedType, Rate, Available 
         FROM SleepingRooms 
         WHERE BedType = N'Queen'
    ) 
    SELECT RoomNumber, RoomType, Rate, Available 
    FROM BedRooms
    WHERE Available = 1; 
    GO
  4. Appuyez sur la touche F5 pour voir le résultat

    With

Expressions de Table Communes Récursives

Une expression de table commune récursive est un CTE pouvant contenir plus d'une instruction SELECT. Dans le corps du CTE, vous pouvez créer autant d'instructions SELECT que vous le souhaitez, mais ces déclarations doivent être jointes. Pour se joindre à elles, vous pouvez utiliser un opérateur UNION, UNION ALL ou MERGER.

Practical LearningEtude pratique : Création d'un CTE récursif

  1. Modifiez le code du CTE comme suit :
    USE CeilInn3 ; 
    GO 
     
     
    WITH HotelRooms
    AS 
    (
       SELECT * FROM SleepingRooms
       UNION 
       SELECT * FROM ConferenceRooms
    ) 
    SELECT * FROM HotelRooms ;
    GO
  2. Appuyez sur F5 pour exécuter
  3. Pour voir une liste de toutes les chambres disponibles, modifiez l'instruction comme suit :
    USE CeilInn3 ; 
    GO
    
    WITH HotelRooms 
    AS
    (
       SELECT * FROM SleepingRooms 
       UNION 
       SELECT * FROM ConferenceRooms
    ) 
    SELECT RoomNumber, RoomType, BedType, Rate
    FROM HotelRooms 
    WHERE Available = 1; 
    GO
  4. Appuyez sur F5 pour exécuter

    WITH

Expressions de table communes non-récursives

Une expression de table commune non récursive est un CTE qui peut être suivi par une seule instruction SELECT, INSERT, UPDATE ou DELETE qui implique une colonne à l'intérieur du corps du CTE.

      
 

Précédent Copyright © 2010 Yevol.com Suivant