按逗号连接列上的 sql 查询分组 [英] Group by sql query on comma joined column

问题描述

我的表结构如下，邮件"列可以包含多个用逗号连接的电子邮件

数据(整数)

邮件(varchar(200))

<块引用>

[数据] [邮件]

---

<块引用>

1 [e-mail],[e-mail]

2 [e-mail],[e-mail]

&我需要生成如下报告，计算每封电子邮件的每一行

<块引用>

[邮件] [计数]

---

<块引用>

[e-mail] 1

[e-mail] 2

[e-mail] 1

那么像上面那样生成的 sql(server) 查询是什么?我也无法更改表结构.

解决方案

仅使用 CHARINDEX 而没有 XML 或 CTE 的字符串拆分速度更快.

示例表

create table #tmp ([Data] int, [Mail] varchar(200))插入#tmp SELECT 1,'[e-mail],[e-mail],other,longer@test, Fifth'UNION ALL SELECT 2,'[e-mail],[e-mail]'UNION ALL SELECT 3,'[e-mail]'UNION ALL SELECT 4,''UNION ALL SELECT 5,null

查询

选择单个，count(*) [Count]从(选择 ltrim(rtrim(substring(t.mail, v.number+1,isnull(nullif(charindex(',',t.mail,v.number+1),0)-v.number-1,200)))) 单来自#tmp t内连接 master..spt_values v on v.type='p'和 v.number <= len(t.Mail)和 (substring(t.mail,v.number,1) = ',' 或 v.number=0)) X单人分组

您提供的唯一零件是

• #tmp:您的表名
• #mail:列名

My table structure is like below, "Mail" column can contain multiple email joined by comma

Data(int)

Mail(varchar(200))

[Data] [Mail]

---

1          [e-mail],[e-mail]

2          [e-mail],[e-mail]

& I need to generate the report like below, counting each row per each email

[Mail]                   [Count]

---

[e-mail]          1

[e-mail]       2

[e-mail]            1

So what will be the sql(server) query to generate like above? Also I can't change the table structure.

解决方案

String splitting is faster using only CHARINDEX without XML or CTE.

Sample table

create table #tmp ([Data] int, [Mail] varchar(200))
insert #tmp SELECT 1,'[e-mail],[e-mail],other, longer@test, fifth'
UNION ALL   SELECT 2,'[e-mail],[e-mail]'
UNION ALL   SELECT 3,'[e-mail]'
UNION ALL   SELECT 4,''
UNION ALL   SELECT 5,null

The query

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

The only parts you supply are

• #tmp: your table name
• #mail: the column name

这篇关于按逗号连接列上的 sql 查询分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！