sql server数据库中的两个ID之间是否存在联系

Question

ID1 是否认识 ID2
张三 认识 李四
王五 认识 李四
马六 认识 王五

跪. 求，大。神，类似这种数据，如果数据比较多的情况下如何查询张三与马六之间的关联

Answer 1

可以用with递归,注意环的处理,比如

with t as (
select '3' id1,'4' id2 union all
select '5' id1,'4' id2 union all
select '6' id1,'5' id2),
tt as (
select id1,id2 from t
union all
select id2,id1 from t
),
cte as (
select distinct '0' id1,id1 id2,id1 root_node, 0 lvl from tt where id1='3'
union all
select tt.id1 ,tt.id2,cte.root_node,lvl+1 from tt  join  cte on tt.id1=cte.id2
where lvl<10
)
select distinct cte.root_node,id2 from cte where id2='6'

上面这个sql就是用'3'来找是否和'6'有关系,只看10层,不设限的话可能会有无限循环。
有必要的话,这个sql稍加改造,可以拼个关系链出来

---

下面这个就是从张3到马6的10层以内最短路径

with t as (
select '3' id1,'4' id2 union all
select '5' id1,'4' id2 union all
select '6' id1,'5' id2),
tt as (
select id1,id2 from t
union all
select id2,id1 from t
),
cte as (
select distinct '0' id1,id1 id2,cast(id1 as VARCHAR(100)) root_node,cast(id1 as VARCHAR(100)) path, 0 lvl from tt where id1='3'
union all
select tt.id1 ,tt.id2,cte.root_node,cast(cte.path+'/'+tt.id2  as VARCHAR(100)) path,lvl+1 from tt  join  cte on tt.id1=cte.id2
where lvl<10
)
select top 1 root_node,id2,path from cte where id2='6' order by lvl;