DECLARE @emp TABLE (
EmployeeID INT,
FirstName VARCHAR(15),
LastName VARCHAR(15),
ReportsTo INT
)
DECLARE @ord TABLE (
OrderID INT,
EmployeeID INT
)
INSERT INTO @emp(EmployeeID, FirstName, LastName, ReportsTo)
SELECT 2,'Andrew','Fuller',NULL UNION ALL
SELECT 1,'Nancy','Davolio',2 UNION ALL
SELECT 3,'Janet','Leverling',2 UNION ALL
SELECT 4,'Margaret','Peacock',2 UNION ALL
SELECT 5,'Steven','Buchanan',2 UNION ALL
SELECT 8,'Laura','Callahan',2 UNION ALL
SELECT 6,'Michael','Suyama',5 UNION ALL
SELECT 7,'Robert','King',5 UNION ALL
SELECT 9,'Anne','Dodsworth',7
UNION ALL
SELECT 10,'Liang','JiaHui',NULL UNION ALL
SELECT 11,'Tang','LanLan',10 UNION ALL
SELECT 12,'LanLan','JiaJia',11
INSERT INTO @ord (OrderID, EmployeeID)
SELECT 10258,1 UNION ALL
SELECT 10270,1 UNION ALL
SELECT 10275,1 UNION ALL
SELECT 10265,2 UNION ALL
SELECT 10277,2 UNION ALL
SELECT 10251,3 UNION ALL
SELECT 10253,3 UNION ALL
SELECT 10256,3 UNION ALL
SELECT 10250,4 UNION ALL
SELECT 10252,4 UNION ALL
SELECT 10248,5 UNION ALL
SELECT 10254,5 UNION ALL
SELECT 10249,6 UNION ALL
SELECT 10289,7 UNION ALL
SELECT 10303,7 UNION ALL
SELECT 10308,7 UNION ALL
SELECT 10262,8 UNION ALL
SELECT 10268,8 UNION ALL
SELECT 10276,8 UNION ALL
SELECT 10278,8 UNION ALL
SELECT 10255,9 UNION ALL
SELECT 10263,9
UNION ALL
SELECT 10254,10 UNION ALL
SELECT 12520,11
;WITH Liang AS
(
SELECT A.*,B.[Count]
FROM @emp AS A
LEFT JOIN (SELECT EmployeeID,COUNT(*) AS [Count]
FROM @ord GROUP BY EmployeeID) AS B
ON A.EmployeeID = B.EmployeeID
),
Liang2 AS
(
SELECT EmployeeID,CAST(LastName+','+FirstName AS varchar(50)) AS Name,
ReportsTo,[Count],
CAST(ROW_NUMBER() OVER(ORDER BY EmployeeID) AS varbinary(MAX)) AS tree,
level = 0
FROM Liang
WHERE ReportsTo IS NULL
UNION ALL
SELECT A.EmployeeID,CAST(LastName+','+FirstName AS varchar(50)) AS name,
A.ReportsTo,A.[Count],
CAST(B.tree+CAST(ROW_NUMBER() OVER(PARTITION BY A.ReportsTo
ORDER BY B.EmployeeID ) AS binary(4)) AS varbinary(MAX)),
B.level + 1
FROM Liang AS A
JOIN Liang2 AS B
ON B.EmployeeID = A.ReportsTo
),
Liang3 AS
(
SELECT A.EmployeeID AS flag ,B.EmployeeID AS empid,B.ReportsTo AS parentID,
B.[Count]
FROM Liang AS A
JOIN Liang AS B
ON A.EmployeeID = B.ReportsTo
UNION ALL
SELECT A.flag,B.EmployeeID,B.ReportsTo,
ISNULL(B.[Count],0)
FROM Liang3 AS A
JOIN Liang AS B
ON A.empid = B.ReportsTo
)
SELECT REPLICATE(' ',A.level*2)+'|--'+A.Name AS Name,
level,
by_self=ISNULL(A.[Count],0),
by_sub=ISNULL(B.[Count],0),
total=ISNULL(A.[Count],0)+ISNULL(B.[Count],0)
FROM Liang2 AS A
LEFT JOIN (
SELECT flag,SUM([Count]) AS [Count]
FROM Liang3 GROUP BY flag
) AS B
ON A.EmployeeID=B.flag
ORDER BY tree;
/*
Name level by_self by_sub total
-------------------------------------------------- ----------- ----------- ----------- -----------
|--Fuller,Andrew 0 2 20 22
|--Davolio,Nancy 1 3 0 3
|--Leverling,Janet 1 3 0 3
|--Peacock,Margaret 1 2 0 2
|--Buchanan,Steven 1 2 6 8
|--Suyama,Michael 2 1 0 1
|--King,Robert 2 3 2 5
|--Dodsworth,Anne 3 2 0 2
|--Callahan,Laura 1 4 0 4
|--JiaHui,Liang 0 1 1 2
|--LanLan,Tang 1 1 0 1
|--JiaJia,LanLan 2 0 0 0
(12 行受影响)
*/
