查找列值连接 [英] Find the columns values connectivity
本文介绍了查找列值连接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有下表包含示例数据:
I have the following table with sample data:
表:tbl_nodes
create table tbl_nodes
(
nod1 varchar(50),
nod2 varchar(50),
nod_lenght float
);
示例数据:
insert into tbl_nodes values('A','B',1600);
insert into tbl_nodes values('G','H',45000);
insert into tbl_nodes values('B','C',2300);
insert into tbl_nodes values('C','D',2640);
insert into tbl_nodes values('B','D',2840);
insert into tbl_nodes values('C','E',2940);
insert into tbl_nodes values('D','F',2340);
insert into tbl_nodes values('M','N',9000);
insert into tbl_nodes values('E','A',100000);
预期结果:
Connectivity Total_Length
----------------------------------
A-B-C-D-F 8880
A-B-C-E-A 106840
A-B-D-F 6780
注意:我试图找出节点之间的连接性和节点之间的长度总和.
Note: I am trying to find out the nodes connectivity and sum of the length between the nodes.
我尝试了以下查询:
我的尝试:
;WITH CTE AS
(
SELECT nod1,nod2 ,sum(nod_lenght) as Total_length
from tbl_nodes T1
where EXISTS (select 1 from tbl_nodes T2 where T1.nod2 =T2.nod1) OR
EXISTS (select 1 from tbl_nodes T3 WHERE T1.nod1 =T3.nod2)
GROUP BY nod1,nod2
)
SELECT STUFF((select '-'+ case when c1.nod2 = c2.nod1 then c1.nod1 else c2.nod2 end
from CTE c1
where c1.nod2 =c2.nod1 or c1.nod1 =c2.nod2
for xml path('')),1,1,''),Total_length
FROM CTE c2
无法得到预期的结果.
推荐答案
我很确定您需要一个递归 CTE.但是,您的示例结果没有意义.
I'm pretty sure you need a recursive CTE. However, your sample results doesn't make sense.
以下基本上可以满足您的要求:
The following basically does what you want:
with cte AS (
select nod1, nod2, nod_length as Total_length,
convert(varchar(max), concat('-', nod1, '-', nod2, '-')) as nodes, 1 as lev
from tbl_nodes n
where nod1 = 'A'
union all
select cte.nod1, n.nod2, cte.Total_length + n.nod_length,
convert(varchar(max), concat(cte.nodes, n.nod2, '-')) as nodes, lev + 1
from cte join
tbl_nodes n
on cte.nod2 = n.nod1
where nodes not like concat('%-', n.nod2, '-%')
)
select nodes, total_length
from cte
where not exists (select 1
from cte cte2
where cte2.nodes like concat(cte.nodes, '_%')
);
这里是db<>fiddle.
Here is a db<>fiddle.
这篇关于查找列值连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文