简单SQL查询
简单SQL查询
提问于 2018-10-16 08:59:42
我有一个SQL脚本，并且希望根据不同的where子句有多个计数，而不是重复这样的语句，这样我就可以简化了吗？
select
UnverifiedEmails =
(
select count(distinct c.ContactRef)
from ContactEmailAddressVerification c
LEFT JOIN EmailAddressVerification e
ON e.EmailAddressVerificationID = c.EmailAddressVerificationID
WHERE DateVerified IS NULL
),
VerifiedEmails =
(
select count(distinct c.ContactRef)
from ContactEmailAddressVerification c
LEFT JOIN EmailAddressVerification e
ON e.EmailAddressVerificationID = c.EmailAddressVerificationID
WHERE DateVerified IS NOT NULL
),
LastMonthVerified =
(
select count(distinct c.ContactRef)
from GDPR_ContactEmailAddressVerification c
LEFT JOIN EmailAddressVerification e
ON e.EmailAddressVerificationID = c.EmailAddressVerificationID
WHERE DateVerified IS NOT NULL
AND DATEPART(m, e.DateAdded) = DATEPART(m, DATEADD(m, -1, getdate()))
),
LastMonthUnverified =
(
select count(distinct c.ContactRef)
from ContactEmailAddressVerification c
LEFT JOIN EmailAddressVerification e
ON e.EmailAddressVerificationID = c.EmailAddressVerificationID
WHERE DateVerified IS NULL
AND DATEPART(m, e.DateAdded) = DATEPART(m, DATEADD(m, -1, getdate()))
)Stack Overflow用户
回答已采纳
发布于 2018-10-16 09:06:14
您可以使用单个SELECT语句进行聚合：
SELECT COUNT(DISTINCT CASE WHEN DateVerified IS NULL THEN c.ContactRef END) UnverifiedEmails,
COUNT(DISTINCT CASE WHEN DateVerified IS NOT NULL THEN c.ContactRef END) VerifiedEmails,
COUNT(DISTINCT CASE WHEN (DateVerified IS NOT NULL AND
DATEPART(m, e.DateAdded) = DATEPART(m, DATEADD(m, -1, getdate()))
)
THEN c.ContactRef
END) LastMonthVerified,
COUNT(DISTINCT CASE WHEN (DateVerified IS NULL AND
DATEPART(m, e.DateAdded) = DATEPART(m, DATEADD(m, -1, getdate()))
)
THEN c.ContactRef
END) LastMonthUnverified
FROM ContactEmailAddressVerification c LEFT JOIN
EmailAddressVerification e
ON e.EmailAddressVerificationID = c.EmailAddressVerificationID;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52831696
复制相关文章
相似问题
腾讯云开发者
