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
The simplest example of a recursive CTE I can think of to illustrate its operation is;
;WITH Numbers AS
(
SELECT n = 1
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n+1 <= 10
)
SELECT n
FROM Numbers
Q 1) how value of N is getting incremented. if value is assign to N every time then N value can be incremented but only first time N value was initialize.
A1: In this case, N is not a variable. N is an alias. It is the equivalent of SELECT 1 AS N. It is a syntax of personal preference. There are 2 main methods of aliasing columns in a CTE in T-SQL. I've included the analog of a simple CTE in Excel to try and illustrate in a more familiar way what is happening.
-- Outside
;WITH CTE (MyColName) AS
(
SELECT 1
)
-- Inside
;WITH CTE AS
(
SELECT 1 AS MyColName
-- Or
SELECT MyColName = 1
-- Etc...
)
Q 2) now here about CTE and recursion of employee relation the moment i add two manager and add few more employee under second manager then problem start. i want to display first manager detail and in the next rows only those employee details will come those who are subordinate of that manager
A2:
Does this code answer your question?
--------------------------------------------
-- 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
Another one sql with tree structure
SELECT ID,space(nLevel+
(CASE WHEN nLevel > 1 THEN nLevel ELSE 0 END)
)+Name
FROM Hierarchy
ORDER BY Family, nLevel
Would like to outline a brief semantic parallel to an already correct answer.
In 'simple' terms, a recursive CTE can be semantically defined as the following parts:
1: The CTE query. Also known as ANCHOR.
2: The recursive CTE query on the CTE in (1) with UNION ALL (or UNION or EXCEPT or INTERSECT) so the ultimate result is accordingly returned.
3: The corner/termination condition. Which is by default when there are no more rows/tuples returned by the recursive query.
A short example that will make the picture clear:
;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
Explanation: The first CTE query returns the base suppliers (like leaves) who do not supply to any other supplier directly (-1)
The recursive query in the first iteration gets all the suppliers who supply to the suppliers returned by the ANCHOR. This process continues till the condition returns tuples.
UNION ALL returns all the tuples over the total recursive calls.
Another good example can be found here.
PS: For a recursive CTE to work, the relations must have a hierarchical (recursive) condition to work on. Ex: elementId = elementParentId.. you get the point.
실행 프로세스는 재귀 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
'code' 카테고리의 다른 글
| Android의 SQlite에서 준비된 문을 어떻게 사용합니까? (0) | 2020.08.23 |
|---|---|
| SQL Server 2008 : 사용자 이름에 권한을 부여하려면 어떻게합니까? (0) | 2020.08.23 |
| addChildViewController는 실제로 무엇을합니까? (0) | 2020.08.23 |
| Sublime Text의 인덱싱에서 폴더를 제외하고 사이드 바에 계속 표시하려면 어떻게해야합니까? (0) | 2020.08.23 |
| HQL에서 고유 한 쿼리를 생성하는 방법 (0) | 2020.08.23 |