如何获取包含公司(ID)的公司列表

Question

重要。。。在我的RL项目中，ID不是INT，而是GUID，所以我的数据不是分层的

我有一张公司表和一张公司之间的链接表。

我需要能够从特定的公司ID检索公司列表。

这是我的测试代码。。。

CREATE TABLE ##corporations
(  
CorporationID INT NOT NULL,  
CorporationName NVARCHAR(20) NOT NULL
);  
CREATE TABLE ##corporationLinks
(  
FromCorporationID INT NOT NULL,  
ToCorporationID INT NOT NULL
);  
INSERT INTO ##corporations (CorporationID, CorporationName) VALUES (1, 'Nike')
INSERT INTO ##corporations (CorporationID, CorporationName) VALUES (2, 'Cocal Cola')
INSERT INTO ##corporations (CorporationID, CorporationName) VALUES (3, 'Apple')
INSERT INTO ##corporations (CorporationID, CorporationName) VALUES (4, 'Google')
INSERT INTO ##corporations (CorporationID, CorporationName) VALUES (5, 'Amazon')
INSERT INTO ##corporations (CorporationID, CorporationName) VALUES (6, 'Samsung')
INSERT INTO ##corporationLinks (FromCorporationID, ToCorporationID) VALUES (1, 2)
INSERT INTO ##corporationLinks (FromCorporationID, ToCorporationID) VALUES (2, 3)
INSERT INTO ##corporationLinks (FromCorporationID, ToCorporationID) VALUES (4, 5)
INSERT INTO ##corporationLinks (FromCorporationID, ToCorporationID) VALUES (4, 6)
SELECT * FROM ##corporationLinks WHERE FromCorporationID = 2 OR ToCorporationID = 2
/** 
Organisations (##corporationLinks) are...
Nike + Coca Cola + Apple + Marcy
and...
Google + Amazon + Samsung
**/
-- How do I eg get a list of companies where Coca Cola is in ... that is where FromCorporationID = 2 OR ToCorporationID = 2 ... result should be Nike, Coca Cola and Apple?
-- How do I eg get a list of companies where Samsung is in ... that is where FromCorporationID = 6 OR ToCorporationID = 6 ... result should be Google, Amazon Cola and Samsung?
DROP TABLE ##corporationLinks 
DROP TABLE ##corporations

UDPATE:

如果我需要找到可口可乐所属的公司，那么我会负责招聘。。。

选择*FROM##公司链接，其中FromCorporationID=2或ToCorporationID＝2

然后我会得到2个结果。。。

FromCorporationID   ToCorporationID
-----------------------------------
1                   2
2                   3

在这里，我需要调查一下哪些公司的结果是。。。

SELECT * FROM ##corporationLinks WHERE FromCorporationID = 1 OR ToCorporationID = 1
SELECT * FROM ##corporationLinks WHERE FromCorporationID = 3 OR ToCorporationID = 3

然后我会得到另一个公司(7(：

FromCorporationID   ToCorporationID
-----------------------------------
3                   7

然后我需要深入了解哪些公司与7…有关

SELECT * FROM ##corporationLinks WHERE FromCorporationID = 7 OR ToCorporationID = 7

并深入研究这个结果(猜测它被称为递归(。等

更新2:

我已经更新了上面的样本，增加了一家公司，如果搜索是可口可乐，应该返回。

上述查询(可口可乐(的预期结果：

CorporationID:
--------------
2
1
3
7

Answer 1

使用递归CTE:

with 
root as (select CorporationID id from companies where CorporationName = 'Coca Cola'),
cte as (
select 
case when r.id = cl.FromCorporationID then cl.ToCorporationID else cl.FromCorporationID end id,
case when r.id = cl.FromCorporationID then cl.FromCorporationID else cl.ToCorporationID end otherid      
from corporationLinks cl inner join root r
on r.id in (cl.FromCorporationID, cl.ToCorporationID)
union all
select 
case when c.id = cl.FromCorporationID then cl.ToCorporationID else cl.FromCorporationID end id,
case when c.id = cl.FromCorporationID then cl.FromCorporationID else cl.ToCorporationID end otherid            
from corporationLinks cl inner join cte c
on c.id in (cl.FromCorporationID, cl.ToCorporationID)
and c.otherid not in (cl.FromCorporationID, cl.ToCorporationID)
)
select id CorporationID from root
union all
select id from cte

请参阅演示
结果：

> | CorporationID |
> | ------------: |
> |             2 |
> |             1 |
> |             3 |
> |             7 |

Answer 2

left加入##公司，包括其公司ID，或者您可以在查询中包括公司名称，然后按公司ID分组，您应该能够看到它属于的哪家公司

Answer 3

只需使用递归CTE

WITH cte
AS
(
SELECT l1.FromCorporationID,l1.ToCorporationID
FROM ##corporationLinks l1
UNION ALL
SELECT cte.FromCorporationID,l3.ToCorporationID
FROM ##corporationLinks l3
JOIN cte 
ON cte.ToCorporationID = l3.FromCorporationID
)
SELECT cte.ToCorporationID--parent
FROM cte
WHERE cte.FromCorporationID =2
UNION
SELECT cte.FromCorporationID--child
FROM cte
WHERE cte.ToCorporationID =2
UNION
SELECT c.CorporationID--self
FROM ##corporations c
WHERE c.CorporationID = 2