一个存储邮件的表,结构大致如下:
emailid(邮件编号INT) sender(发件人vnchar) receiver(收件人vnchar) content(内容等vnchar)
1 [email protected] [email protected];[email protected] *******
2 [email protected] [email protected];[email protected]如何查询出所有和[email protected]有关的发件人和收件人,并计算出其与[email protected]收发信件的数量?
难度主要在:
[email protected]有可能是发件人,也有可能是收件人
2.一封邮件可能有多个收件人,在表内存储时用“;”分割
我现在的思路是先找到所有和[email protected]相关的收件人和发件人,但是后面的sum语句还是不会写啊,大虾帮忙,小弟先谢谢了
emailid(邮件编号INT) sender(发件人vnchar) receiver(收件人vnchar) content(内容等vnchar)
1 [email protected] [email protected];[email protected] *******
2 [email protected] [email protected];[email protected]如何查询出所有和[email protected]有关的发件人和收件人,并计算出其与[email protected]收发信件的数量?
难度主要在:
[email protected]有可能是发件人,也有可能是收件人
2.一封邮件可能有多个收件人,在表内存储时用“;”分割
我现在的思路是先找到所有和[email protected]相关的收件人和发件人,但是后面的sum语句还是不会写啊,大虾帮忙,小弟先谢谢了
declare @t table (emailid int,sender varchar(20),receiver varchar(20))
insert into @t
select 1,'[email protected];[email protected]','[email protected];[email protected]' union all
select 2,'[email protected];[email protected]','[email protected];[email protected]' union all
select 3,'[email protected];[email protected]','[email protected];[email protected]' union all
select 4,'[email protected];[email protected]','[email protected];[email protected]' union all
select 5,'[email protected];[email protected]','[email protected];[email protected]' union all
select 6,'[email protected];[email protected]','[email protected];[email protected]'declare @bl varchar(20) set @bl='[email protected]'select
sum(case when charindex(';'+@bl+';',';'+sender+';')>0 then 1 else 0 end) as 发件人,
sum(case when charindex(';'+@bl+';',';'+receiver+';')>0 then 1 else 0 end) as 收件人
from @t
/*
发件人 收件人
----------- -----------
2 4
*/
邮箱名 向[email protected]发件 从[email protected]收件
----------- ------------------------------
[email protected] 2 4
[email protected] 3 3
*/