27,582
社区成员




最好给出完整的表结构,测试数据,计算方法和正确结果.
DECLARE @t TABLE (Data VARCHAR(40) )
INSERT @t (Data) SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97'
INSERT @t (Data) SELECT '0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1'
INSERT @t (Data) SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C'
INSERT @t (Data) SELECT '5AAF477C-274D-400D-9067-035968F33B19'
INSERT @t (Data) SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE'
INSERT @t (Data) SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888'
;WITH Liang AS
(
SELECT A.Data,B.number,
ROW_NUMBER() OVER(PARTITION BY A.Data ORDER BY B.number)-B.number AS rowid,
SUBSTRING(A.Data,B.number,1) AS [Char]
FROM @t AS A
JOIN master.dbo.spt_values AS B
ON B.type = 'P' AND SUBSTRING(A.Data,B.number,1)=SUBSTRING(A.Data,B.number+1,1)
AND B.number < LEN(A.Data)
)
SELECT *
FROM(
SELECT Data,[Char],
MIN(number) AS Pos,COUNT(*)+1 AS [Length]
FROM Liang
GROUP BY Data,[Char],rowid
) AS T
ORDER BY MAX(Length) OVER(PARTITION BY Data) DESC,Data,Pos
/*
Data Char Pos Length
---------------------------------------- ---- ----------- -----------
8083ED5A-D3B9-4694-BB04-F0B09C588888 B 20 2
8083ED5A-D3B9-4694-BB04-F0B09C588888 8 32 5
4A73E7EB-7777-4A04-9258-F1E75097977C 7 10 4
4A73E7EB-7777-4A04-9258-F1E75097977C 7 34 2
9992EDC6-D117-4DEE-B410-4E5FAE46AE97 9 1 3
9992EDC6-D117-4DEE-B410-4E5FAE46AE97 1 11 2
9992EDC6-D117-4DEE-B410-4E5FAE46AE97 E 17 2
5AAF477C-274D-400D-9067-035968F33B19 A 2 2
5AAF477C-274D-400D-9067-035968F33B19 7 6 2
5AAF477C-274D-400D-9067-035968F33B19 0 16 2
5AAF477C-274D-400D-9067-035968F33B19 3 32 2
725DA718-30D0-44A9-B36A-89F27CDFEEDE 4 15 2
725DA718-30D0-44A9-B36A-89F27CDFEEDE E 33 2
(13 行受影响)
*/
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',5
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
;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
),
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
),
Liang4 AS
(
SELECT A.EmployeeID,ISNULL(B.[Count],0) AS [Count]
FROM Liang AS A
LEFT JOIN (
SELECT flag,SUM([Count]) AS [Count] FROM Liang3
GROUP BY flag
) AS B
ON A.EmployeeID=B.flag
),
Liang2 AS
(
SELECT A.EmployeeID,CAST(LastName+','+FirstName AS varchar(50)) AS Name,
ReportsTo,A.[Count],
CAST(ROW_NUMBER() OVER(ORDER BY B.[Count] DESC) AS varbinary(MAX)) AS tree,
level = 0,B.[Count] AS total
FROM Liang AS A
JOIN Liang4 AS B
ON A.EmployeeID=B.EmployeeID
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 C.[Count] DESC,A.[Count] DESC) AS binary(4))
AS varbinary(MAX)),
B.level + 1,C.[Count]
FROM Liang AS A
JOIN Liang2 AS B
ON B.EmployeeID = A.ReportsTo
JOIN Liang4 AS C
ON A.EmployeeID=C.EmployeeID
)
SELECT REPLICATE(' ',A.level*2)+'|--'+A.Name AS Name,
level,
by_self=ISNULL(A.[Count],0),
by_sub=ISNULL(A.[total],0),
total=ISNULL(A.[Count],0)+ISNULL(A.[total],0)
FROM Liang2 AS A
ORDER BY tree;
/*
Name level by_self by_sub total
-------------------------------------------------- ----------- ----------- ----------- -----------
|--Fuller,Andrew 0 2 20 22
|--Buchanan,Steven 1 2 6 8
|--King,Robert 2 3 0 3
|--Dodsworth,Anne 2 2 0 2
|--Suyama,Michael 2 1 0 1
|--Callahan,Laura 1 4 0 4
|--Davolio,Nancy 1 3 0 3
|--Leverling,Janet 1 3 0 3
|--Peacock,Margaret 1 2 0 2
(9 行受影响)
*/