34,576
社区成员
发帖
与我相关
我的任务
分享
-- data
if object_id('tempdb.dbo.#') is not null drop table #
create table #(c1 varchar(8), c2 varchar(8), qty int)
insert into #
select 'A', 'B1', 0 union all
select 'A', 'B2', 0 union all
select 'A', 'B3', 1 union all
select 'B1', 'C1', 1 union all
select 'B1', 'C2', 1 union all
select 'B2', 'D1', 0 union all
select 'B2', 'D2', 1 union all
select 'D1', 'E1', 1
-- result
;with cte as
(
select x=c1, y=c2, * from #
union all
select x, y, b.* from cte a join # b on a.c2=b.c1
)
select c1=x, c2=y, qty=sum(qty) from cte group by x, y
/*
c1 c2 qty
-------- -------- -----------
A B1 2
A B2 2
A B3 1
B1 C1 1
B1 C2 1
B2 D1 1
B2 D2 1
D1 E1 1
*/