我有一張桌子，公司：
CompanyId MotherCompanyId CompanyName
---------- ------------ ------------
1 NULL HpTopMother
2 1 HpTopDaughter1
3 1 HpTopDaughter2
4 NULL HpTopDaughter3
5 2 HpTopDaughter4
6 1 HpTopDaughter5
我想要做的是，從一個 Id 回傳那個 Id 的父級（如果有的話）、它的子級和它的兄弟姐妹。所以如果我有 CompanyId = 1，查詢將回傳我
編號：2,3,6
如果我有 CompanyId = 2，查詢將回傳我
編號：1,3,5,6
我試過類似的東西：
DECLARE @cmpId BIGINT
SET @cmpId = 14085;
WITH CTE_FAMILY as(
-- To get the siblings
select CompanyId
from Company
where MotherCompanyId = (select MotherCompanyId from Company where CompanyId = @cmpId)
-- To get the daughters
UNION ALL
SELECT CompanyId
FROM Company
WHERE MotherCompanyId = @corId
)
SELECT *
FROM CTE_FAMILY
但是當我有 CompanyId = 2 時，這僅回傳 Id: 3,6。
知道如何做我想做的事嗎？
uj5u.com熱心網友回復：
找到您需要的每種型別并union all一起
WITH CTE_FAMILY as
(
select CompanyId = c.MotherCompanyId, type = 'Parent'
from Company c
where c.CompanyId = @cmpId
and c.MotherCompanyId is not null
union all
select CompanyId = s.CompanyId, type = 'Sibling'
from Company c
inner join Company s on c.MotherCompanyId = s.MotherCompanyId
where c.CompanyId = @cmpId
and c.MotherCompanyId is not null
and c.CompanyId <> s.CompanyId
union all
select CompanyId = c.CompanyId, type = 'Child'
from Company c
where c.MotherCompanyId = @cmpId
)
select *
from CTE_FAMILY
uj5u.com熱心網友回復：
像替代方案一樣，查詢可以是這樣的
WITH subject AS (
SELECT *
FROM Company
WHERE CompanyId = @cmpId
),
parent AS (
SELECT Company.*
FROM Company, subject
WHERE Company.CompanyId = subject.MotherCompanyId
),
direct_children AS (
SELECT Company.*
FROM Company, subject
WHERE Company.MotherCompanyId = subject.CompanyId
),
siblings AS (
SELECT Company.*
FROM Company, subject, parent
WHERE Company.MotherCompanyId = parent.CompanyId AND Company.CompanyId != subject.CompanyId
),
family AS (
SELECT * FROM parent
UNION ALL
SELECT * FROM direct_children
UNION ALL
SELECT * FROM siblings
)
SELECT * FROM family
演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/492337.html
上一篇：你能幫我在sql中更正這個查詢嗎
下一篇：如果不屬于組，則選擇唯一記錄