有本事就来啊，挑战TSQL语句

[拓狼]

【挑战TSQL】父子结构数据的计算
http://www.windbi.com/showtopic-2955.aspx

【挑战TSQL】找出字符串里连续重复字符所在的位置以及长度
http://www.windbi.com/showtopic-2954.aspx

[vivian_lanlan]

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 行受影响)
*/

[vivian_lanlan]

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 行受影响)


*/

[-狙击手-]

NX

[fcuandy]

来几个有点难度的吧

[SQL77]

路过

[Mr_Nice]

路过，看看！

[--小F--]

学习

[小坏猪猪]

learing

[让你望见影子的墙]

没这个本事，闪

[dawugui]

最好给出完整的表结构,测试数据,计算方法和正确结果.

发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

[guguda2008]

一小撮没本事的烤鸭路过

[橡胶轮胎行业数字化高总]

来给windbi做广告了吧。

[Oracle13g]

楼上的有点牛,嘿嘿...

[tjgsf1]

学习!