如何制作SQL查询以将父子数据组为小组

Question

我有一个客户表，并将父母和子客户放在同一表格中，并带有" parendId"字段关系。表如下。

CustId      CustName    ParentId
----------------------------------
1           Cust1         0 
2           Cust2         0
3           Sub2Cust1     1
4           Cust3         0
5           Sub1Cust1     1
6           Sub1Cust2     2
7           Sub2Cust2     2
8           Sub4Cust1     1
9           Sub1Cust3     4
10          Sub3Cust1     1

我想要的是MS SQL查询，因此它将父母和子女记录保持在一起，如以下输出：

CustId      CustName    ParentId
----------------------------------
1           Cust1          0 
5           Sub1Cust1      1
3           Sub2Cust1      1
10          Sub3Cust1      1
8           Sub4Cust1      1
2           Cust2          0
6           Sub1Cust2      2
7           Sub2Cust2      2
4           Cust3          0
9           Sub1Cust3      4

任何人都可以给我一个提示如何使用单个查询？

预先感谢

Answer 1

ORDER BY CASE  WHEN ParentId = 0 THEN CustID ELSE ParentId END ASC
,  CASE WHEN ParentId = 0 THEN 0 ELSE CustId END ASC  --to put the parent on top of the children, and keep the children in order

要按名称订购孩子，而不是ID，只是这样做：

ORDER BY CASE  WHEN ParentId = 0 THEN CustID ELSE ParentId END ASC
,  CASE WHEN ParentId = 0 THEN '0' ELSE CustName END ASC  --to put the parent on top of the children, and keep the children in order

Answer 2

根据您的评论，您可能需要递归CTE。

从技术上讲不是一个查询，但这将支持可变深度和您所需的排序

示例

;with cteP as (
      Select CustId
            ,ParentId 
            ,CustName 
            ,PathStr = cast(CustName as varchar(max))
      From   YourTable 
      Where  ParentId=0
      Union  All
      Select CustId  = r.CustId
            ,ParentId  = r.ParentId 
            ,CustName   = r.CustName
            ,HierID = P.PathStr+'>'+r.CustName
      From   YourTable r
      Join   cteP p on r.ParentId  = p.CustId )
Select CustId
      ,CustName 
      ,ParentId
 From cteP A
 Order By A.PathStr

返回

CustId  CustName    ParentId
1       Cust1       0
3       Sub1Cust1   1
5       Sub2Cust1   1
8       Sub3Cust    1
10      Sub4Cust1   1
2       Cust2       0
6       Sub1Cust2   2
7       Sub2Cust2   2
4       Cust3       0
9       Sub1Cust3   4