Microsoft SQL Server
Typowe wyrażenia tabelowe

Składnia

• Z nazwą cte [( nazwa_kolumny_1 , nazwa_kolumny_2 , ...)] AS ( wyrażenie cte )

Uwagi

Hierarchia pracowników

Konfiguracja stołu

CREATE TABLE dbo.Employees
(
    EmployeeID INT NOT NULL PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    ManagerID INT NULL
)

GO

INSERT INTO Employees VALUES (101, 'Ken', 'Sánchez', NULL)
INSERT INTO Employees VALUES (102, 'Keith', 'Hall', 101)
INSERT INTO Employees VALUES (103, 'Fred', 'Bloggs', 101)
INSERT INTO Employees VALUES (104, 'Joseph', 'Walker', 102)
INSERT INTO Employees VALUES (105, 'Žydrė', 'Klybė', 101)
INSERT INTO Employees VALUES (106, 'Sam', 'Jackson', 105)
INSERT INTO Employees VALUES (107, 'Peter', 'Miller', 103)
INSERT INTO Employees VALUES (108, 'Chloe', 'Samuels', 105)
INSERT INTO Employees VALUES (109, 'George', 'Weasley', 105)
INSERT INTO Employees VALUES (110, 'Michael', 'Kensington', 106)

Wspólne wyrażenie tabeli

;WITH cteReports (EmpID, FirstName, LastName, SupervisorID, EmpLevel) AS
(
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, r.EmpLevel + 1
    FROM Employees        AS e
    INNER JOIN cteReports AS r ON e.ManagerID = r.EmpID
)

SELECT
    FirstName + ' ' + LastName AS FullName,
    EmpLevel,
    (SELECT FirstName + ' ' + LastName FROM Employees WHERE EmployeeID = cteReports.SupervisorID) AS ManagerName
FROM cteReports
ORDER BY EmpLevel, SupervisorID

Wynik:

Pełna nazwa	EmpLevel	Nazwa menedżera
Ken Sánchez	1	zero
Keith Hall	2)	Ken Sánchez
Fred Bloggs	2)	Ken Sánchez
Žydre Klybe	2)	Ken Sánchez
Joseph Walker	3)	Keith Hall
Peter Miller	3)	Fred Bloggs
Sam Jackson	3)	Žydre Klybe
Chloe Samuels	3)	Žydre Klybe
George Weasley	3)	Žydre Klybe
Michael Kensington	4	Sam Jackson

Znajdź n-tą najwyższą pensję za pomocą CTE

Tabela pracowników:

|  ID  | FirstName | LastName | Gender | Salary |
+------+-----------+----------+--------+--------+
| 1    | Jahangir  | Alam     | Male   | 70000  |
| 2    | Arifur    | Rahman   | Male   | 60000  |
| 3    | Oli       | Ahammed  | Male   | 45000  |
| 4    | Sima      | Sultana  | Female | 70000  |
| 5    | Sudeepta  | Roy      | Male   | 80000  |
+------+-----------+----------+--------+--------+

CTE (Common Table Expression):

 WITH RESULT AS
(
    SELECT SALARY,
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
    FROM EMPLOYEES
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = 1

Aby znaleźć 2. najwyższą pensję, po prostu zamień N na 2. Podobnie, aby znaleźć 3. najwyższą pensję, po prostu zamień N na 3.

Usuń zduplikowane wiersze za pomocą CTE

Tabela pracowników:

|  ID  | FirstName | LastName | Gender | Salary |
+------+-----------+----------+--------+--------+
|  1   | Mark      | Hastings | Male   | 60000  |
|  1   | Mark      | Hastings | Male   | 60000  |
|  2   | Mary      | Lambeth  | Female | 30000  |
|  2   | Mary      | Lambeth  | Female | 30000  |
|  3   | Ben       | Hoskins  | Male   | 70000  |
|  3   | Ben       | Hoskins  | Male   | 70000  |
|  3   | Ben       | Hoskins  | Male   | 70000  |
+------+-----------+----------+--------+--------+

CTE (Common Table Expression):

WITH EmployeesCTE AS
(
   SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS RowNumber
   FROM Employees
)
DELETE FROM EmployeesCTE WHERE RowNumber > 1

Wynik wykonania:

|  ID  | FirstName | LastName | Gender | Salary |
+------+-----------+----------+--------+--------+
|  1   | Mark      | Hastings | Male   | 60000  |
|  2   | Mary      | Lambeth  | Female | 30000  |
|  3   | Ben       | Hoskins  | Male   | 70000  |
+------+-----------+----------+--------+--------+

Wygeneruj tabelę dat za pomocą CTE

DECLARE @startdate CHAR(8), @numberDays TINYINT

SET @startdate = '20160101'
SET @numberDays = 10;

WITH CTE_DatesTable
AS
(
  SELECT CAST(@startdate as date) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= DateAdd(DAY, @numberDays-1, @startdate)
)

SELECT [date] FROM CTE_DatesTable

OPTION (MAXRECURSION 0)

Ten przykład zwraca tabelę dat z jedną kolumną, zaczynając od daty określonej w zmiennej @startdate i zwracając daty o wartości następnej @numberDays.

Rekurencyjne CTE

Ten przykład pokazuje, jak dostać się każdego roku od tego roku do 2011 roku (2012 - 1).

WITH yearsAgo
(
    myYear
)
AS
(
     -- Base Case: This is where the recursion starts
     SELECT DATEPART(year, GETDATE()) AS myYear

     UNION ALL  -- This MUST be UNION ALL (cannot be UNION)

     -- Recursive Section: This is what we're doing with the recursive call
     SELECT yearsAgo.myYear - 1
     FROM yearsAgo
     WHERE yearsAgo.myYear >= 2012
)
     SELECT myYear FROM yearsAgo;  -- A single SELECT, INSERT, UPDATE, or DELETE

mój rok
2016 r
2015 r
2014
2013
2012 r
2011 r

Możesz kontrolować rekurencję (myśl przepełnienie stosu w kodzie) za pomocą MAXRECURSION jako opcji zapytania, która ograniczy liczbę wywołań rekurencyjnych.

WITH yearsAgo
(
    myYear
)
AS
(
     -- Base Case
     SELECT DATEPART(year , GETDATE()) AS myYear
     UNION ALL
     -- Recursive Section
     SELECT yearsAgo.myYear - 1
     FROM yearsAgo
     WHERE yearsAgo.myYear >= 2002
)
     SELECT * FROM yearsAgo
     OPTION (MAXRECURSION 10);

Msg 530, poziom 16, stan 1, wiersz 2 Instrukcja wygasła. Maksymalna rekurencja 10 została wyczerpana przed zakończeniem instrukcji.

CTE z wieloma instrukcjami AS

;WITH cte_query_1
AS
(
    SELECT *
    FROM database.table1
), 
cte_query_2 
AS
(
    SELECT *
    FROM database.table2
)
SELECT *
FROM cte_query_1
WHERE cte_query_one.fk IN
(
    SELECT PK
    FROM cte_query_2
)

W przypadku typowych wyrażeń tabelowych możliwe jest tworzenie wielu zapytań za pomocą instrukcji AS oddzielonych przecinkami. Zapytanie może następnie odwoływać się do jednego lub wszystkich tych zapytań na wiele różnych sposobów, nawet je łącząc.

Modified text is an extract of the original Stack Overflow Documentation

Licencjonowany na podstawie CC BY-SA 3.0

Nie związany z Stack Overflow