简单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()))
)回答 3
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用户
发布于 2018-10-16 09:04:30
当不需要多个子查询时用例
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.EmailAddressVerificationIDStack Overflow用户
发布于 2018-10-16 09:06:52
您可以使用条件聚合：
SELECT
COUNT(DISTINCT CASE WHEN DateVerified IS NULL
THEN c.ContactRef END) AS UnverifiedEmails,
COUNT(DISTINCT CASE WHEN DateVerified IS NOT NULL
THEN c.ContactRef END) AS VerifiedEmails,
COUNT(DISTINCT CASE WHEN DateVerified IS NOT NULL AND
DATEPART(m, e.DateAdded) = DATEPART(m, DATEADD(m, -1, GETDATE()))
THEN c.ContactRef END) AS LastMonthVerified,
COUNT(DISTINCT CASE WHEN DateVerified IS NULL AND
DATEPART(m, e.DateAdded) = DATEPART(m, DATEADD(m, -1, GETDATE()))
THEN c.ContactRef END) AS LastMonthUnverified
FROM ContactEmailAddressVerification c
LEFT JOIN EmailAddressVerification e
ON e.EmailAddressVerificationID = c.EmailAddressVerificationID;这里的想法是对联接的表进行一次传递，然后根据原始查询的每个WHERE子句中的逻辑有条件地取计数/和。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52831696
复制相关文章
相似问题
腾讯云开发者
