如何进行SQL查询以获取组中的父子数据 [英] How to make SQL query to get parent child data in group

问题描述

你好，



我有一个客户表，并且在父表和子客户的同一表中有ParentId字段关系。表格如下。

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

我想要的是来自MS SQL Query所以它保持父母和孩子一起记录，如下输出：

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

可以有人请给我一个提示如何用单个查询来做这个吗？



提前付款



< b>我尝试了什么：



我正在尝试使用



< pre lang =SQL> SELECT ROW_NUMBER（） OVER （ PARTITION BY [ParentId] ORDER BY [客户名称] ASC ） AS [R]，[CustomerId]，[ParentId]， [客户名称] FROM [客户] 订单 按 [R ]，[ParentId]

但是没有得到如何正确使用

解决方案

一种方法是利用公用表表达式。使用分层查询包括结果中最顶层的父ID，并将其用作主要排序字段。



例如类似

< pre lang =sql> WITH
CustCTE（CustId，CustName，ParentId，UltimateParent） AS （
SELECT t1.CustId，
t1.CustName，
t1.ParentId，
t1.CustId
FROM 客户t1
WHERE ParentId = 0
UNION 所有
SELECT t2.CustId，
t2.CustName，
t2.ParentId，
cte.UltimateParent
FROM CustCTE cte，
客户t2
WHERE t2。 ParentId = cte.CustId
）
SELECT cte.CustId，
cte.CustName，
cte.ParentId
FROM CustCTE cte
ORDER BY cte.UltimateParent，
cte.ParentId

我找到了解决方案



当ParentId = 0时按案例排序那么CustID ELSE ParentId END ASC

，当ParentId = 0时为'0'ELSE CustName END ASC

Hello,

I have one customer table, and have parent and sub customer in same table with "ParentId" field relation. Table as below.

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

What I want is from MS SQL Query so it keep parent and child record together, Like output as below:

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

Can anybody please give me a hint how to do it with single query?

Thanks in advance

What I have tried:

I am trying to use the

SELECT	ROW_NUMBER() OVER(PARTITION BY [ParentId] ORDER BY  [CustomerName] ASC) AS [R],[CustomerId],[ParentId],[CustomerName] FROM	[Customer] Order by [R],[ParentId]

But not getting how to exactly use that

解决方案

One way is to utilize Common Table Expression. Using a hierarchical query include the topmost parent id in the result and use that as a primary sorting field.

For example something like

WITH 
CustCTE (CustId, CustName, ParentId, UltimateParent) AS (
   SELECT t1.CustId, 
          t1.CustName, 
          t1.ParentId, 
          t1.CustId
   FROM  Customer t1
   WHERE ParentId = 0
   UNION ALL 
   SELECT t2.CustId, 
          t2.CustName, 
          t2.ParentId, 
          cte.UltimateParent
   FROM CustCTE  cte,
        Customer t2
   WHERE t2.ParentId = cte.CustId
)
SELECT cte.CustId, 
       cte.CustName, 
       cte.ParentId
FROM CustCTE cte
ORDER BY cte.UltimateParent, 
         cte.ParentId

I found the solution

ORDER BY CASE WHEN ParentId = 0 THEN CustID ELSE ParentId END ASC
, CASE WHEN ParentId = 0 THEN '0' ELSE CustName END ASC

这篇关于如何进行SQL查询以获取组中的父子数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！