仅使用CHARINDEX而不使用XML或CTE，字符串拆分更快。
样品表
create table #tmp ([Data] int, [Mail] varchar(200))
insert #tmp SELECT 1,'m1@gmail.com,m2@hotmail.com,other, longer@test, fifth'
UNION ALL SELECT 2,'m2@hotmail.com,m3@test.com'
UNION ALL SELECT 3,'m3@single.com'
UNION ALL SELECT 4,''
UNION ALL SELECT 5,null
select single, count(*) [Count]
from
(
select ltrim(rtrim(substring(t.mail, v.number+1,
isnull(nullif(charindex(',',t.mail,v.number+1),0)-v.number-1,200)))) single
from #tmp t
inner join master..spt_values v on v.type='p'
and v.number <= len(t.Mail)
and (substring(t.mail,v.number,1) = ',' or v.number=0)
) X
group by single
您提供的唯一零件是
602392714
清零编程群