WITH절 CTE,재귀 CTE 사용법

WITH 절 CTE은 같은 SQL이 반복되어서 사용될 때 성능을 높이기 위해 사용되는데 테이블을 만들지 않고도 테이블을 만든 것과 같은 효과를 내는데, 실제로는 임시 테이블에 저장됩니다.

WITH 절 CTE, 재귀 CTE 특징

• CTE(공통 테이블 식)라고도 하는 임시로 이름을 지정한 DATA 집합입니다.
• CTE 뒤에는 일부 또는 모든 CTE 열을 참조하는 단일 SELECT, INSERT, UPDATE 또는 DELETE 문이 와야 합니다.
• CTE는 임시테이블하고 같은 성능을 발휘합니다.
• CTE에 둘 이상의 WITH 절을 지정할 수 없습니다.
• CTE에서는 INTO, 쿼리 힌트가 있는 OPTION 절, OR BROWSE는 사용할 수 없습니다.
• 재귀 CTE 정의는 적어도 두 개의 CTE 쿼리 정의 즉, 하나의 앵커 멤버와 재귀 멤버를 포함해야 가능합니다.

WITH절 기본 구조

CTE 기본 구조.

• Syntax:

WITH CTE_TABLE_NAME(COL1, COL2, COL3...)
AS
(
	<sql문>
)
SELECT COL1, COL2, COL3 ... 
FROM CTE_TABLE_NAME;

• Source:

--  WITH절 기본 구조.
WITH baseCte(MngId, EmpID, Title, cteParam ) AS
(
  SELECT MngId, EmpID, Title , 'paramTest'  paramTest
    FROM dbo.UserInfoForCte  
)
SELECT MngId, EmpID, Title ,cteParam
FROM baseCte
ORDER BY MngId;

단일 쿼리에서 여러 CTE 정의 사용 방법.

• CTE 쿼리 정의를 구분하기 위해 쉼표로 구분해서 사용할 수 있습니다.
• Source:

--문에서 쉼표로 구분하는 여러 CTE 사용하기
WITH
cteFirst (Mngcnt, dept) AS
(
  SELECT COUNT(*) Mngcnt, 'Manager' dept  from UserInfoForCte where MngId = '273'
),
cteSecond (saleCnt, dept) AS
(
  SELECT COUNT(*) saleCnt, 'Sales' dept  FROM UserInfoForCte where MngId > '273'
)
SELECT Mngcnt, dept FROM cteFirst
UNION ALL
SELECT  saleCnt, dept FROM cteSecond;

WITH절 CTE 활용

Recursive(재귀) CTE 구조.

• Syntax:

WITH CTE_TABLE_NAME(COL1, COL2, COL3 ...)
AS
(
    <쿼리문1 : SELECT * FROM TABLE_A>
    UNION ALL
    <쿼리문2 : SELECT * FROM TABLE_A JOIN CTE_TABLE_NAME>
)
SELECT * FROM CTE_TABLE_NAME;

재귀 공통 테이블식을 사용하여 계층적 목록 표시 방법.

• 회사에서 계층적 구조를 갖는 사장, 부사장, 관리자로 계층적 표시를 할 때는 보통 재귀 CTE를 사용해야 합니다.
• Source :

-- 재귀 공통 테이블 식을 사용하여 계층적 목록 표시
WITH DirectChart(MngId,Name, Title, EmpID, EmployeeLevel, Sort)
AS (
    SELECT MngId,CONVERT(VARCHAR(255), e.FirstName + ' ' + e.LastName),
          e.Title,
          e.EmpID,
          1,
          CONVERT(VARCHAR(255), e.FirstName + ' ' + e.LastName)
     FROM dbo.UserInfoForCte AS e
    WHERE e.MngId IS NULL
    UNION ALL
    SELECT e.MngId ,CONVERT(VARCHAR(255), REPLICATE ('|   ' , EmployeeLevel) + e.FirstName + ' ' + e.LastName),
          e.Title,
          e.EmpID,
          EmployeeLevel + 1,
          CONVERT (VARCHAR(255), RTRIM(Sort) + '|   ' + FirstName + ' ' +  LastName)
    FROM dbo.UserInfoForCte AS e
    JOIN DirectChart AS d ON e.MngId = d.EmpID
  )
SELECT MngId, EmpID, Name, Title, EmployeeLevel,Sort
FROM DirectChart
ORDER BY Sort;

MAXRECURSION을 사용법.

• 재귀 CTE가 무한 루프에 진입하는 것을 방지하는 데 MAXRECURSION을 사용합니다.
• 방법은 쿼리문 마지막에 OPTION (MAXRECURSION 숫자) 넣어주면 됩니다.

-- MAXRECURSION을 사용하여 문 취소
WITH DirectChart(MngId, EmpID, Title, EmployeeLevel) AS
(
   SELECT MngId, EmpID, Title, 1 AS EmployeeLevel
     FROM dbo.UserInfoForCte
    WHERE MngId IS NULL
   UNION ALL
   SELECT e.MngId, e.EmpID, e.Title, EmployeeLevel + 1
     FROM dbo.UserInfoForCte AS e
     INNER JOIN DirectChart AS d
     ON e.MngId = d.EmpID
)
SELECT MngId, EmpID, Title, EmployeeLevel
FROM DirectChart
OPTION (MAXRECURSION 10); --- 재귀 호출 회수를 입력하면 됩니다.

재귀 CTE 입력 DATA.

• 소스:

USE sampleDB;
DROP TABLE IF EXISTS UserInfoForCte;

 CREATE TABLE dbo.UserInfoForCte
(
EmpID SMALLINT NOT NULL,
FirstName NVARCHAR(30) NOT NULL,
LastName NVARCHAR(40) NOT NULL,
Title NVARCHAR(50) NOT NULL,
DeptID SMALLINT NOT NULL,
MngId SMALLINT NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmpID ASC),
CONSTRAINT FK_MyEmployees_MngId_EmpID FOREIGN KEY (MngId) REFERENCES dbo.UserInfoForCte (EmpID)
);
-- Populate the table with values.
INSERT INTO dbo.UserInfoForCte VALUES
(1, N'Ken', N'Sanchez', N'Chief Executive Officer(사장)',16, NULL)
,(273, N'Brian', N'Welcker', N'Vice President(부사장)', 3, 1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager(미국지점장)', 3, 273)
,(275, N'Michael', N'Blythe', N'Sales ', 3, 274)
,(276, N'Linda', N'Mitchell', N'Sales ', 3, 274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager(태평양지점장)', 3, 273)
,(286, N'Lynn', N'Tsoflias', N'Sales ', 3, 285)
,(16, N'David', N'Bradley', N'Marketing Manager(마케팅장)', 4, 273)
,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);