同表中的父子關係

我有一個儲存父/子記錄的表，如下所示：

+-------+------------+---------+---------+------------+-----------+
|custid | custname   | deptid  | company |parentcustid| enrolled  |
+=======+============+=========+=========+============+===========+
| 7060  | Sally      |   AB1   | comp1   |  null      |     1     |
| 6953  | Ajit       |   AB7   | comp2   |  7060      |     1     |
| 6957  | Rahul      |   DE1   | comp3   |  7060      |     1     |
| 6958  | uday       |   TG6   | comp4   |  7060      |     1     |
| 6959  | john       |   HY7   | comp5   |  7060      |     1     |
| 6960  | netaji     |   HY5   | comp6   |  7060      |     1     |
| 6961  | prakriti   |   GT6   | comp7   |  7060      |     1     |
| 6962  | sachin     |   KL7   | comp8   |  7060      |     0     |
| 6963  | santosh    |   KK5   | comp9   |  7060      |     1     |
| 6964  | Ravi       |   PP0   | comp10  |  7060      |     1     |
+-------+------------+---------+---------+------------+-----------+

是否可以返回父記錄已註冊且相關子記錄未註冊的記錄？

這將返回我需要的 1 個特定客戶：

select a.custid, a.custname, a.deptid, a.company, a.parentcustid, a.enrolled
from customer a
where a.company = 'comp1' and a.Enrolled = 1
union all
select a.custid, a.custname, a.deptid, a.company, a.parentcustid, a.enrolled
from customer a
where a.parentcustid= 7060 and b.Enrolled = 0

+-------+------------+---------+---------+------------+-----------+
|custid | custname   | deptid  | company |parentcustid| enrolled  |
+=======+============+=========+=========+============+===========+
| 7060  | Sally      |   AB1   | comp1   |  null      |     1     |
| 6962  | sachin     |   KL7   | comp8   |  7060      |     0     |
+-------+------------+---------+---------+------------+-----------+

如何構造查詢以返回表中所有父子記錄的那種類型的結果集？

如果您只有一個級別的孩子，您可以加入表格

SELECT
   a.custid, a.custname, a.deptid, a.company,
   b.custid AS bcustid, b.custname AS bcustname, b.deptid AS bdeptid, b.company AS bcompany
FROM
   customer a
   LEFT JOIN customer b
       ON a.custid = b.parentcustid AND b.Enrolled = 0
WHERE
   a.parentcustid  IS NULL AND a.Enrolled = 1

像這樣，您不會失去父記錄和子記錄之間的關係。

由於您知道父母已註冊而孩子未註冊，因此重新添加該enrolled列不會添加新資訊。

---

如果要保持原始結果表形狀，請使用附加列進行排序

SELECT
   custid, custname, deptid, company, parentcustid, enrolled,
   10 * custid AS orderKey
FROM customer
WHERE parentcustid IS NULL AND Enrolled = 1
UNION ALL
SELECT
   custid, custname, deptid, company, parentcustid, enrolled,
   10 * parentcustid + 1 AS orderKey
FROM customer
WHERE parentcustid IS NOT NULL AND Enrolled = 0
ORDER BY orderKey, custid

請注意，此 ORDER BY 應用於整個聯合，而不僅僅是第二個 SELECT。像這樣，孩子總是列在父母的下方。

順便說一句，將 key 乘以 2 足以獲得 order key 的預期效果。如果您乘以 10，它只會顯示得更好。如果您的客戶 ID 始終最多為 4 位，您也可以將10000 * custid其用於父母和10000 * parentcustid + custid孩子，並僅orderKey訂購以獲得有序的記錄。

引用自：https://dba.stackexchange.com/questions/194131