计算另一个计数值，但前提是它足够高

Question

我正在尝试提取 2 个数字。第一个是 1 个月内总共有多少医生（dr 表）有超过 10 个答案（答案表），无论日期如何，总共有 75 个答案。另一个数字是相同的，但是在过去 3 个月内而不是 1 个月内。

我使用下面的这个答案来提出这个查询

SELECT D.name, 
    count(DISTINCT case when A.created > DATE_SUB(NOW(), INTERVAL 1 MONTH) then A.id end) as '1 month', 
    count(DISTINCT case when A.created > DATE_SUB(NOW(), INTERVAL 3 MONTH) then A.id end) as '1 quarter', 
    count(DISTINCT A.id) as total
FROM dr D
JOIN answer A ON A.dr_id=D.id AND A.status=3
GROUP BY D.id

：为我提供了所需的原始信息，但我不知道如何通过将它们与 10 和 75 个答案要求进行比较来计算给出的计数。

Answer 1

我想是这样的：

SELECT
  COUNT(CASE WHEN total >= 75 AND `1month`   > 10 THEN name END) AS `10+ per month count`,
  COUNT(CASE WHEN total >= 75 AND `1quarter` > 10 THEN name END) AS `10+ per quarter count`
FROM (
  SELECT D.name, 
    count(DISTINCT case when A.created > DATE_SUB(NOW(), INTERVAL 1 MONTH) then A.id end) as `1month`, 
    count(DISTINCT case when A.created > DATE_SUB(NOW(), INTERVAL 3 MONTH) then A.id end) as `1quarter`, 
    count(DISTINCT A.id) as total
  FROM dr D
    JOIN answer A ON A.dr_id=D.id AND A.status=3
  GROUP BY D.id
) s

Answer 2

您可能需要稍微玩一下这个查询，但它应该会为您提供您正在寻找的内容。基本上将您的查询用作派生表，并使用 group by/having 进一步总结它。

;with DrCounts as (
    SELECT D.id, 
      count(DISTINCT case when A.created > DATE_SUB(NOW(), INTERVAL 1 MONTH) then A.id end) as '1month', 
      count(DISTINCT case when A.created > DATE_SUB(NOW(), INTERVAL 3 MONTH) then A.id end) as '1quarter', 
      count(DISTINCT A.id) as total
    FROM dr D
    JOIN answer A ON A.dr_id=D.id AND A.status=3
    GROUP BY D.id)

select count(distinct D.id) as Dr1075
from  DrCounts D
group by D.Id
having D.total >= 75 and D.1month >= 10
union
select count(distinct D.id) as Dr1075
from  DrCounts D
group by D.Id
having D.total >= 75 and D.1quarter >= 10