查找列值连接 [英] 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屋！