楼主,看看这样可以不:--演示数据 create table tb(id int, tel varchar(100)) insert tb select 1, '13000000001,13000000002' insert tb select 2, '13000000002,13000000003' insert tb select 3, '13000000003,13000000004' insert tb select 4, '13000000005' insert tb select 5, '13000000006' insert tb select 6, '13000000007,13000000008' --我加的 insert tb select 7, '13000000008,13000000009' --我加的--分拆后,放入临时表 create table #t(id int, tel varchar(20)) insert #t select a.id, b.tel from tb a cross apply ( select tel=SUBSTRING(a.tel,number,CHARINDEX(',',a.tel+',',number+1)-number) from master..spt_values where CHARINDEX(',',','+a.tel,number) = number AND type = 'P' ) b--递归 ;with cte as ( select no=a.id, a.id, a.tel, bid = b.id, cn = case when b.id is null then 1 else 2 end from #t a left join #t b on a.id < b.id and a.tel = b.tel union all select a.no, b.id, b.tel, bid = c.id, cn = a.cn + 1 from cte a join #t b on a.bid = b.id join #t c on a.bid < c.id and b.tel = c.tel where a.bid is not null-- and a.no not in (select bid from t2) ) --得出结果 select id=a.no,count=max(cn) from cte a where not exists(select 1 from cte where bid = a.no) group by a.no/* id count ----------- ----------- 1 3 4 1 5 1 6 2(4 行受影响) */
create table tb(id int, tel varchar(100))
insert tb select 1, '13000000001,13000000002'
insert tb select 2, '13000000002,13000000003'
insert tb select 3, '13000000003,13000000004'
insert tb select 4, '13000000005'
insert tb select 5, '13000000006'
insert tb select 6, '13000000007,13000000008' --我加的
insert tb select 7, '13000000008,13000000009' --我加的--分拆后,放入临时表
create table #t(id int, tel varchar(20))
insert #t
select a.id, b.tel
from tb a
cross apply (
select tel=SUBSTRING(a.tel,number,CHARINDEX(',',a.tel+',',number+1)-number)
from master..spt_values
where CHARINDEX(',',','+a.tel,number) = number AND type = 'P'
) b--递归
;with cte as
(
select no=a.id, a.id, a.tel, bid = b.id, cn = case when b.id is null then 1 else 2 end
from #t a
left join #t b on a.id < b.id and a.tel = b.tel
union all
select a.no, b.id, b.tel, bid = c.id, cn = a.cn + 1 from cte a join #t b on a.bid = b.id join #t c on a.bid < c.id and b.tel = c.tel
where a.bid is not null-- and a.no not in (select bid from t2)
)
--得出结果
select id=a.no,count=max(cn)
from cte a
where not exists(select 1 from cte where bid = a.no)
group by a.no/*
id count
----------- -----------
1 3
4 1
5 1
6 2(4 行受影响)
*/