SQL树形数据查询

[zhiking3387]

比如数据如下：

DocNo SrcDocNo Qty
A 1
B 1
C 1
A1 A 2
A2 A 2
A3 A 2
A4 A 2
B1 B 5
B2 B 5
B3 B 5
B4 B 5
C1 C 8
C2 C 8
C3 C 8
A11 A1 3
A12 A1 3
A13 A1 3
A14 A1 3
A15 A1 3
A21 A2 4
A22 A2 4
A23 A2 4
A24 A2 4
B11 B1 2
B12 B1 2
B13 B1 2

如何实现如下这种效果？

DocNo SrcDocNo Qty
A 1
A1 A 2
A11 A1 3
A12 A1 3
A13 A1 3
A14 A1 3
A15 A1 3
A2 A 2
A21 A2 4
A22 A2 4
A23 A2 4
A24 A2 4
A3 A 2
A4 A 　
B 1
B1 B 5
B11 B1 2
B12 B1 2
B13 B1 2
B2 B 5
B3 B 5
B4 B 5
C 1
C1 C 8
C2 C 8
C3 C 8

[zhiking3387]

数据与效果如图片所示，左边是数据，右边是实现的效果，用SQL如何实现？

[jamesrggg]

直接ORDER BY

[jamesrggg]

Declare @TmpData Table( DNO NVarchar(10), PDNo NVarchar(10), QTY Int) Insert Into @TmpData Select N'A',N'',1 Union Select N'B',N'',1 Union Select N'C',N'',1 Union Select N'A1',N'A',2 Union Select N'A2',N'A',2 Union Select N'B1',N'B',5 Union Select N'B2',N'B',5 Union Select N'C1',N'C',8 Union Select N'A11',N'A1',3 Select Right(N' ',LEN(DNO))+ DNO,PDNo,QTY From @TmpData Order By DNO

[还在加载中灬]

SELECT REPLICATE(' ',Qty)+DocNo AS[DocNo]
	,SrcDocNo,Qty
FROM TB T
ORDER BY T.DocNo

[zhiking3387]

引用 4 楼 ky_min 的回复:

SELECT REPLICATE(' ',Qty)+DocNo AS[DocNo]
	,SrcDocNo,Qty
FROM TB T
ORDER BY T.DocNo

注意：DocNo字段的值不是固定的，是没有规律的，不能简单的根据值去排序，类似于生产的BOM表,SrcDocNo是的DocNo的父级，上面的数据我只是举个例子。

[Tiger_Zhao]

WITH /* 测试数据
table1(DocNo,SrcDocNo,Qty) AS (
    SELECT 'A',NULL,9 UNION ALL
    SELECT 'B',NULL,8 UNION ALL
    SELECT 'C',NULL,7 UNION ALL
    SELECT 'D','A',1 UNION ALL
    SELECT 'E','A',2 UNION ALL
    SELECT 'F','B',3 UNION ALL
    SELECT 'G','B',4 UNION ALL
    SELECT 'H','D',5 UNION ALL
    SELECT 'I','G',6 UNION ALL
    SELECT 'J','F',0
), */
tree AS ( -- 递归求出Level、Path
    SELECT *,
           1 AS Level,
           Convert(varchar(max),DocNo) AS Path
      FROM table1
     WHERE SrcDocNo IS NULL
    UNION ALL
    SELECT c.*,
           p.Level + 1,
           p.Path+'\'+c.DocNo
      FROM tree p
      JOIN table1 c
        ON c.SrcDocNo = p.DocNo
)
-- 用Level缩进、Path排序
    SELECT LEFT(SPACE(Level-1)+DocNo,4) DocNo,
           SrcDocNo,
           Qty
      FROM tree
  ORDER BY Path

DocNo    SrcDocNo         Qty
-------- -------- -----------
A        NULL               9
 D       A                  1
  H      D                  5
 E       A                  2
B        NULL               8
 F       B                  3
  J      F                  0
 G       B                  4
  I      G                  6
C        NULL               7

[zhiking3387]

引用 6 楼 Tiger_Zhao 的回复:

WITH /* 测试数据
table1(DocNo,SrcDocNo,Qty) AS (
    SELECT 'A',NULL,9 UNION ALL
    SELECT 'B',NULL,8 UNION ALL
    SELECT 'C',NULL,7 UNION ALL
    SELECT 'D','A',1 UNION ALL
    SELECT 'E','A',2 UNION ALL
    SELECT 'F','B',3 UNION ALL
    SELECT 'G','B',4 UNION ALL
    SELECT 'H','D',5 UNION ALL
    SELECT 'I','G',6 UNION ALL
    SELECT 'J','F',0
), */
tree AS ( -- 递归求出Level、Path
    SELECT *,
           1 AS Level,
           Convert(varchar(max),DocNo) AS Path
      FROM table1
     WHERE SrcDocNo IS NULL
    UNION ALL
    SELECT c.*,
           p.Level + 1,
           p.Path+'\'+c.DocNo
      FROM tree p
      JOIN table1 c
        ON c.SrcDocNo = p.DocNo
)
-- 用Level缩进、Path排序
    SELECT LEFT(SPACE(Level-1)+DocNo,4) DocNo,
           SrcDocNo,
           Qty
      FROM tree
  ORDER BY Path

DocNo    SrcDocNo         Qty
-------- -------- -----------
A        NULL               9
 D       A                  1
  H      D                  5
 E       A                  2
B        NULL               8
 F       B                  3
  J      F                  0
 G       B                  4
  I      G                  6
C        NULL               7

Tiger_Zhao 是正解，这个是符合要求的。