我的表结构如下所示,“邮件”列可以包含多个用逗号连接的电子邮件
数据(INT)
邮件(VARCHAR(200))
[Data] [Mail]
1 m1@gmail.com,m2@hotmail.com
2 m2@hotmail.com,m3@test.com
[Mail] [Count]
m1@gmail.com 1
m2@hotmail.com 2
m3@test.com 1
解决方法
仅使用没有XML或CTE的CHARINDEX,字符串拆分更快.
样本表
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
你提供的唯一部件是
> #tmp：你的桌名> #mail：列名
