SQL从电子邮件地址计算电子邮件域

Question

查询的目的是计算具有特定域的关联电子邮件地址的数量。我认为，虽然我已经从它应该是相反的，因为我的结果集产生一个零计数的两个字段。

电子邮件地址可能看起来像:[e-mail]。我想做一个计数所有的电子邮件地址，共享一个微软电子邮件域(和相同的AOL/雅虎)。我不知道该怎么办，有什么主意吗?

select
count(case when Email in ('%microsoft.com','%live.com','%outlook.com','%hotmail.com','%msn.com','%passport.com') 
then Email end) as 'Microsoft',
count(case when Email in ('%yahoo.com','%ymail.com','%yahoomail.com','%aol.com','%cs.com','%netscape.com','%verizon.net','%verizon.com','%bellatlantic.net','%gte.net','%managedmail.com') 
then Email end) as 'AOL_Yahoo',
'2021-02-15' as [Date]
from
IP_Warming_Day_1_New_Registrants

Answer 1

出于这个目的，我将分割域:

select sum(case when v.domain in ('%microsoft.com', '%live.com', '%outlook.com', '%hotmail.com', '%msn.com', '%passport.com') 
then 1 else 0
end) as Microsoft,
sum(case when v.domain in ('yahoo.com', 'ymail.com','yahoomail.com','aol.com','cs.com','netscape.com','verizon.net','verizon.com','bellatlantic.net','gte.net','managedmail.com') 
then 1 else 0
end) as AOL_Yahoo,
'2021-02-15' as [Date]
from IP_Warming_Day_1_New_Registrants nr cross apply
(values (stuff(nr.email, 1, charindex('@', nr.email), '')) ) v(domain)

您也可以使用like，但这更麻烦:

select sum(case when v.email like '[e-mail]' or
v.email like '[e-mail]' or
v.email like '[e-mail]' or
v.email like '[e-mail]' or
v.email like '[e-mail]' or
v.email like '[e-mail]')
then 1 else 0
end) as Microsoft,