如何使用一个 SQL 查询获得多个计数?

2021-11-20 00:00:00 join sql group-by count mysql

我想知道如何编写此查询.

I am wondering how to write this query.

我知道这个实际的语法是假的，但它会帮助你理解我想要什么.我需要这种格式，因为它是更大查询的一部分.

I know this actual syntax is bogus, but it will help you understand what I am wanting. I need it in this format, because it is part of a much bigger query.

SELECT distributor_id, 
COUNT(*) AS TOTAL, 
COUNT(*) WHERE level = 'exec', 
COUNT(*) WHERE level = 'personal'

我需要在一个查询中全部返回.

I need this all returned in one query.

另外，它需要在一行中，所以以下不起作用:

Also, it need to be in one row, so the following won't work:

'SELECT distributor_id, COUNT(*)
GROUP BY distributor_id'

推荐答案

您可以将 CASE 语句与聚合函数一起使用.这与某些 RDBMS 中的 PIVOT 函数基本相同:

You can use a CASE statement with an aggregate function. This is basically the same thing as a PIVOT function in some RDBMS:

SELECT distributor_id,
    count(*) AS total,
    sum(case when level = 'exec' then 1 else 0 end) AS ExecCount,
    sum(case when level = 'personal' then 1 else 0 end) AS PersonalCount
FROM yourtable
GROUP BY distributor_id

相关文章