SQL Server CTE 및 재귀 예시
재귀에는 CTE를 사용하지 않습니다.나는 그것에 대한 기사를 읽고 있었다.이 문서에서는 SQL 서버 CTE 및 재귀의 도움을 받아 직원 정보를 보여 줍니다.그것은 기본적으로 직원들과 그들의 관리자 정보를 보여주는 것이다.나는 이 질문이 어떻게 작동하는지 이해할 수 없다.다음은 쿼리입니다.
WITH
cteReports (EmpID, FirstName, LastName, MgrID, 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 e
INNER JOIN cteReports r
ON e.ManagerID = r.EmpID
)
SELECT
FirstName + ' ' + LastName AS FullName,
EmpLevel,
(SELECT FirstName + ' ' + LastName FROM Employees
WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID
에 대한 . "출력이 어떻게 있는지요."
나는 단지 그것이 어떻게 매니저를 먼저 보여주고 그의 부하가 루프에 있는지 알고 싶을 뿐이다.첫 번째 sql 문이 한 번만 실행되면 모든 직원 ID가 반환됩니다.
두 번째 쿼리는 현재 매니저 ID를 가진 종업원이 존재하는 데이터베이스에 대한 쿼리를 반복하여 실행합니다.
내부 루프에서 sql 문이 어떻게 실행되는지 설명하고 sql 실행 순서도 알려주세요.감사해요.
질문 2단계
;WITH Numbers AS
(
SELECT n = 1
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n+1 <= 10
)
SELECT n
FROM Numbers
Q 1) N의 값이 어떻게 증가하고 있습니까?값이 매번 N에 할당될 경우 N 값은 증가할 수 있지만 N 값은 처음 초기화되었을 때만 증가합니다.
Q 2) CTE 및 직원 관계 재귀:
두 명의 매니저를 추가하고 두 번째 매니저 밑에 몇 명의 직원을 추가하는 순간부터 문제가 시작됩니다.
첫 번째 매니저의 세부사항과 다음 행에는 해당 매니저의 부하와 관련된 직원 세부사항만 표시하고자 합니다.
가정하다
ID Name MgrID Level
--- ---- ------ -----
1 Keith NULL 1
2 Josh 1 2
3 Robin 1 2
4 Raja 2 3
5 Tridip NULL 1
6 Arijit 5 2
7 Amit 5 2
8 Dev 6 3
CTE 표현으로 결과를 표시하고 싶다.매니저와 종업원의 관계를 끌어내기 위해 여기에 기재한 sql에서 무엇을 수정해야 하는지 알려주세요.감사해요.
출력은 다음과 같습니다.
ID Name MgrID nLevel Family
----------- ------ ----------- ----------- --------------------
1 Keith NULL 1 1
3 Robin 1 2 1
2 Josh 1 2 1
4 Raja 2 3 1
5 Tridip NULL 1 2
7 Amit 5 2 2
6 Arijit 5 2 2
8 Dev 6 3 2
이게 가능...?
코드를 테스트한 것은 아니고, 코멘트로 어떻게 동작하는지를 이해하려고 했을 뿐입니다.
WITH
cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
-->>>>>>>>>>Block 1>>>>>>>>>>>>>>>>>
-- In a rCTE, this block is called an [Anchor]
-- The query finds all root nodes as described by WHERE ManagerID IS NULL
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID IS NULL
-->>>>>>>>>>Block 1>>>>>>>>>>>>>>>>>
UNION ALL
-->>>>>>>>>>Block 2>>>>>>>>>>>>>>>>>
-- This is the recursive expression of the rCTE
-- On the first "execution" it will query data in [Employees],
-- relative to the [Anchor] above.
-- This will produce a resultset, we will call it R{1} and it is JOINed to [Employees]
-- as defined by the hierarchy
-- Subsequent "executions" of this block will reference R{n-1}
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
r.EmpLevel + 1
FROM Employees e
INNER JOIN cteReports r
ON e.ManagerID = r.EmpID
-->>>>>>>>>>Block 2>>>>>>>>>>>>>>>>>
)
SELECT
FirstName + ' ' + LastName AS FullName,
EmpLevel,
(SELECT FirstName + ' ' + LastName FROM Employees
WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID
의 가장 예CTE그 작동 할 수 있다는 것을 할 수 .
;WITH Numbers AS
(
SELECT n = 1
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n+1 <= 10
)
SELECT n
FROM Numbers
Q 1) N의 값이 어떻게 증가하고 있는가. 값이 매번 N에 할당되면 N 값이 증가할 수 있지만 N 값이 처음 초기화되었을 때만 증가합니다.
A1: 「」는,N을 사용하다 N을 사용하다와 동등하다.SELECT 1 AS N이것은 개인적인 취향의 구문입니다. 가지 으로 열 할 수 .CTET-SQLCTE에Excel어떤 일이 일어나고 있는지 좀 더 친숙한 방법으로 설명하려고 노력합니다.
-- Outside
;WITH CTE (MyColName) AS
(
SELECT 1
)
-- Inside
;WITH CTE AS
(
SELECT 1 AS MyColName
-- Or
SELECT MyColName = 1
-- Etc...
)
Q 2) 여기서 CTE와 직원관계의 재귀에 대해 설명하겠습니다.매니저 2명을 추가하고 2명 아래에 직원을 몇 명 추가하는 순간 문제가 시작됩니다.첫 번째 매니저의 상세 내용을 표시하고 다음 행에는 해당 매니저의 부하 직원만 표시됩니다.
A2:
이 코드가 당신의 질문에 답합니까?
--------------------------------------------
-- Synthesise table with non-recursive CTE
--------------------------------------------
;WITH Employee (ID, Name, MgrID) AS
(
SELECT 1, 'Keith', NULL UNION ALL
SELECT 2, 'Josh', 1 UNION ALL
SELECT 3, 'Robin', 1 UNION ALL
SELECT 4, 'Raja', 2 UNION ALL
SELECT 5, 'Tridip', NULL UNION ALL
SELECT 6, 'Arijit', 5 UNION ALL
SELECT 7, 'Amit', 5 UNION ALL
SELECT 8, 'Dev', 6
)
--------------------------------------------
-- Recursive CTE - Chained to the above CTE
--------------------------------------------
,Hierarchy AS
(
-- Anchor
SELECT ID
,Name
,MgrID
,nLevel = 1
,Family = ROW_NUMBER() OVER (ORDER BY Name)
FROM Employee
WHERE MgrID IS NULL
UNION ALL
-- Recursive query
SELECT E.ID
,E.Name
,E.MgrID
,H.nLevel+1
,Family
FROM Employee E
JOIN Hierarchy H ON E.MgrID = H.ID
)
SELECT *
FROM Hierarchy
ORDER BY Family, nLevel
트리 구조의 다른 1개의 SQL
SELECT ID,space(nLevel+
(CASE WHEN nLevel > 1 THEN nLevel ELSE 0 END)
)+Name
FROM Hierarchy
ORDER BY Family, nLevel
이미 정답과 유사한 간단한 의미에 대해 개략적으로 설명하고자 합니다.
'단순' 용어로 재귀 CTE는 의미론적으로 다음과 같은 부분으로 정의할 수 있습니다.
1: CTE 쿼리.ANCHOR라고도 합니다.
2: UNION ALL(또는 UNION, EXCEPT 또는 CRESS)을 사용하여 (1)의 CTE에 대한 재귀적 CTE 쿼리. 따라서 최종 결과가 반환됩니다.
3: 코너/종단 조건.기본적으로는 재귀 쿼리에서 반환되는 행/튜플이 더 이상 없는 경우입니다.
그림을 명확하게 하는 간단한 예:
;WITH SupplierChain_CTE(supplier_id, supplier_name, supplies_to, level)
AS
(
SELECT S.supplier_id, S.supplier_name, S.supplies_to, 0 as level
FROM Supplier S
WHERE supplies_to = -1 -- Return the roots where a supplier supplies to no other supplier directly
UNION ALL
-- The recursive CTE query on the SupplierChain_CTE
SELECT S.supplier_id, S.supplier_name, S.supplies_to, level + 1
FROM Supplier S
INNER JOIN SupplierChain_CTE SC
ON S.supplies_to = SC.supplier_id
)
-- Use the CTE to get all suppliers in a supply chain with levels
SELECT * FROM SupplierChain_CTE
설명:첫 번째 CTE 쿼리는 다른 공급업체에 직접 공급하지 않는 기본 공급업체(잎 등)를 반환합니다(-1).
첫 번째 반복의 재귀 쿼리는 ANCAR에 의해 반환된 공급업체에 공급되는 모든 공급업체를 가져옵니다.이 프로세스는 조건이 튜플을 반환할 때까지 계속됩니다.
UNION ALL은 재귀 콜의 합계에 대한 모든 튜플을 반환합니다.
또 다른 좋은 예는 여기에서 찾을 수 있다.
PS: 재귀적인 CTE가 기능하기 위해서는 관계가 계층적(재귀적) 조건을 가지고 있어야 합니다.예: elementId = elementParentId..무슨 말인지 아시겠죠?
실행 프로세스는 재귀적인 CTE와 매우 혼동됩니다.최적의 답변은 https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx에서 찾았습니다.CTE 실행 프로세스의 개요는 다음과 같습니다.
재귀 실행의 의미는 다음과 같습니다.
- CTE 식을 앵커 멤버와 재귀 멤버로 분할합니다.
- 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 세트(T0)를 만듭니다.
- Ti를 입력으로, Ti+1을 출력으로 하여 재귀 멤버를 실행합니다.
- 빈 세트가 반환될 때까지 순서 3을 반복합니다.
- 결과 세트를 반환합니다.이것은 T0에서 Tn까지의 UNION ALL입니다.
--DROP TABLE #Employee
CREATE TABLE #Employee(EmpId BIGINT IDENTITY,EmpName VARCHAR(25),Designation VARCHAR(25),ManagerID BIGINT)
INSERT INTO #Employee VALUES('M11M','Manager',NULL)
INSERT INTO #Employee VALUES('P11P','Manager',NULL)
INSERT INTO #Employee VALUES('AA','Clerk',1)
INSERT INTO #Employee VALUES('AB','Assistant',1)
INSERT INTO #Employee VALUES('ZC','Supervisor',2)
INSERT INTO #Employee VALUES('ZD','Security',2)
SELECT * FROM #Employee (NOLOCK)
;
WITH Emp_CTE
AS
(
SELECT EmpId,EmpName,Designation, ManagerID
,CASE WHEN ManagerID IS NULL THEN EmpId ELSE ManagerID END ManagerID_N
FROM #Employee
)
select EmpId,EmpName,Designation, ManagerID
FROM Emp_CTE
order BY ManagerID_N, EmpId
언급URL : https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example
'programing' 카테고리의 다른 글
| R의 Excel 파일에서 시트 이름을 추출하는 방법 (0) | 2023.04.14 |
|---|---|
| 메뉴, 툴바, 스택 패널 등의 세로 구분 컨트롤 - 가능합니까? (0) | 2023.04.14 |
| Bash 스크립트에서 경과된 시간을 계산하려면 어떻게 해야 합니까? (0) | 2023.04.14 |
| XAML에서 제어 중심을 중심으로 회전하는 방법 (0) | 2023.04.14 |
| Windows에서 새로운 프로세스를 작성하는 것이 Linux보다 비싼 이유는 무엇입니까? (0) | 2023.04.14 |