Microsoft SQL Server
공통 표 표현식

통사론

• WITH cte_name [( column_name_1 , column_name_2 , ...)] AS ( cte_expression )

비고

직원 계층 구조

표 설정

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)

공통 표 표현식

;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

산출:

FullName	EmpLevel	ManagerName
켄 산체스	1	없는
키이스 홀	2	켄 산체스
프레드 블로거	2	켄 산체스
ㅁㅁ	2	켄 산체스
조셉 워커	삼	키이스 홀
피터 밀러	삼	프레드 블로거
샘 잭슨	삼	ㅁㅁ
클로이 사무엘	삼	ㅁㅁ
조지 위즐리	삼	ㅁㅁ
마이클 켄싱턴	4	샘 잭슨

CTE를 사용하여 n 번째 가장 높은 급여 찾기

직원 테이블 :

|  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 (공통 표 표현식) :

 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

둘째로 높은 급여를 찾으려면 N을 2로 바꿉니다. 마찬가지로 3 번째로 높은 급여를 찾으려면 N을 3으로 바꾸십시오.

CTE를 사용하여 중복 행 삭제

직원 테이블 :

|  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 (공통 표 표현식) :

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

실행 결과 :

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

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)

이 예제는 @startdate 변수에 지정된 날짜로 시작하여 날짜의 다음 열의 @numberDays 값을 반환하는 단일 열 표의 날짜 표를 반환합니다.

재귀 CTE

이 예제는 올해부터 2011 년 (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

myYear
2016 년
2015
2014
2013 년
2012 년
2011 년

재귀 호출을 제한하는 쿼리 옵션으로 MAXRECURSION을 사용하여 재귀를 제어 할 수 있습니다 (코드에서 스택 오버플로가 발생한다고 생각할 수 있음).

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

메시지 530, 수준 16, 상태 1, 줄 2 문이 종료되었습니다. 명령문 완료 전에 최대 재귀 10이 소모되었습니다.

여러 개의 AS 문이있는 CTE

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

공통 테이블 표현식을 사용하면 쉼표로 구분 된 AS 문을 사용하여 여러 개의 u 리를 작성할 수 있습니다. 그런 다음 쿼리는 여러 가지 방법으로 이러한 쿼리 중 하나 또는 모두를 참조 할 수 있습니다.

Modified text is an extract of the original Stack Overflow Documentation

아래 라이선스 CC BY-SA 3.0

와 제휴하지 않음 Stack Overflow