php - 一个 SQL 语句中的 2 个 COUNT，每个条件不同(MYSQL)

Question

我一直在寻找一种方法来做到这一点，但给出的帮助总是有问题。 (MYSQL不接受，我条件比提问者多等)

我希望能够为员工计算两件事。我有 3 个不同的条件。这怎么可能做到？？

下面的 COUNT 代码非常错误，但它是为了说明我想要它做什么。我正在输出 2 行，因此我需要它们具有不同的名称。

仅供引用:(它适用于一个计数)

谢谢

SELECT employees.employees_ID,
       employees.name,
       employees.country_count_ID,
       employees.department_ID, 
       employees.initials,
       country.country_initials,

COUNT(distinct clients.retailer_ID when clients.progress_ID = 6) as aRows,
COUNT(distinct case when clients.progress_ID = 7) as bRows

FROM employees

LEFT OUTER JOIN clients ON employees.employees_ID = clients.sales_employees_ID
LEFT OUTER JOIN country ON employees.country_count_ID = country.count_ID

WHERE employees.department_ID = 1

GROUP BY employees.employees_ID,
         employees.name,
         employees.country_count_ID,
         employees.department_ID,
         employees.initials,
         country.country_initials

Answer 1

您可以将 case 与 count(distinct) 一起使用:

COUNT(distinct case when clients.progress_ID = 6 then clients.retailer_ID  end) as aRows,
COUNT(distinct case when clients.progress_ID = 7 then clients.retailer_ID  end) as bRows,