sql中跑出账户1到账户6的所有路径

Question

实体 A 标志 交易金额 实体 B
账户 1 出 2 账户 11
账户 1 出 1 账户 12
账户 1 出 2 账户 12
账户 1 出 3 账户 12
账户 1 出 1 账户 2
账户 1 进 2 账户 2
账户 10 出 8 账户 6
账户 10 进 5 账户 11
账户 12 进 22 账户 10
账户 3 进 2 账户 2
账户 3 出 5 账户 7
账户 3 出 7 账户 7
账户 3 出 5 账户 7
账户 3 出 4 账户 9
账户 6 进 7 账户 4
账户 6 进 8 账户 10
账户 6 出 5 账户 10
账户 6 出 7 账户 11
账户 6 进 5 账户 7
账户 8 出 6 账户 4
账户 9 出 3 账户 8

如何在sql中跑出账户1到账户6的所有路径账户及每个路径中账户之间的单向汇总转账金额，如图上，转回的路径要剔除。

Answer 1

你题目里写错一行数据，坑了我一把。。。

账户 12 进 22 账户 10

应该为

账户 12 出 22 账户 10

with t as (
SELECT '账户 1' 实体A,'出' 标志,2 交易金额,'账户 11' 实体B UNION ALL
SELECT '账户 1', '出',1,'账户 12' UNION ALL
SELECT '账户 1', '出',2,'账户 12' UNION ALL
SELECT '账户 1', '出',3,'账户 12' UNION ALL
SELECT '账户 1', '出',1,'账户 2' UNION ALL
SELECT '账户 1', '进',2,'账户 2' UNION ALL
SELECT '账户 10', '出',8,'账户 6' UNION ALL
SELECT '账户 10', '进',5,'账户 11' UNION ALL
SELECT '账户 12', '出',22,'账户 10' UNION ALL
SELECT '账户 3', '进',2,'账户 2' UNION ALL
SELECT '账户 3', '出',5,'账户 7' UNION ALL
SELECT '账户 3', '出',7,'账户 7' UNION ALL
SELECT '账户 3', '出',5,'账户 7' UNION ALL
SELECT '账户 3', '出',4,'账户 9' UNION ALL
SELECT '账户 6', '进',7,'账户 4' UNION ALL
SELECT '账户 6', '进',8,'账户 10' UNION ALL
SELECT '账户 6', '出',5,'账户 10' UNION ALL
SELECT '账户 6', '出',7,'账户 11' UNION ALL
SELECT '账户 6', '进',5,'账户 7' UNION ALL
SELECT '账户 8', '出',6,'账户 4' UNION ALL
SELECT '账户 9', '出',3,'账户 8' ),
tt as (SELECT 
CASE 标志 when '出' then 实体A ELSE 实体B end id1,
CASE 标志 when '出' then 实体B ELSE 实体A end id2,
sum(交易金额) 交易金额 FROM T 
group by 
CASE 标志 when '出' then 实体A ELSE 实体B end,
CASE 标志 when '出' then 实体B ELSE 实体A end),
cte as (
select distinct cast('0' AS  VARCHAR(1000)) id1,id1 id2,cast(id1 as VARCHAR(100)) root_node,cast(id1+'/' as VARCHAR(1000)) path, 0 lvl,0 amt from tt where id1='账户 1'
union all
select cast(tt.id1 AS  VARCHAR(1000)) ,tt.id2,cte.root_node,cast(cte.path+tt.id2+'/'  as VARCHAR(1000)) path,lvl+1,amt+交易金额 from tt  join  cte on tt.id1=cte.id2
where  PATH NOT LIKE '%'+tt.id2+'/%'
)
select  root_node,id2,path,amt from cte where id2='账户 6' ;

你那个汇总转账金额不知道是不是指的每2个节点之间的转账都要先汇总，就是tt表里面那个sum