我想知道如何写这个查询。
我知道这个实际的语法是虚假的，但它将帮助您理解我想要的东西。
我需要这种格式，因为它是一个更大查询的一部分。
SELECT distributor_id,
COUNT(*) AS TOTAL,
COUNT(*) WHERE level = 'exec',
COUNT(*) WHERE level = 'personal'
我需要在一个查询中返回这一切。
此外，它需要在一行中，所以下面的将不起作用:
'SELECT distributor_id, COUNT(*)
GROUP BY distributor_id'
当前回答
如果您的SQL风格支持它，您可以使用COUNT_IF()基于条件进行计数。
SELECT
distributor_id,
COUNT(*) AS total_count,
COUNT_IF(level = 'exec') AS exec_count,
COUNT_IF(level = 'personal') AS personal_count
FROM table_name
GROUP BY distributor_id
其他回答
有一种方法肯定有效
SELECT a.distributor_id,
(SELECT COUNT(*) FROM myTable WHERE level='personal' and distributor_id = a.distributor_id) as PersonalCount,
(SELECT COUNT(*) FROM myTable WHERE level='exec' and distributor_id = a.distributor_id) as ExecCount,
(SELECT COUNT(*) FROM myTable WHERE distributor_id = a.distributor_id) as TotalCount
FROM (SELECT DISTINCT distributor_id FROM myTable) a ;
编辑: 查看@KevinBalmforth对性能的分析，了解为什么你可能不想使用这种方法，而应该选择@Taryn♦的答案。我这样做是为了让人们明白他们的选择。
在Oracle中，你可以这样做
SELECT
(SELECT COUNT(*) FROM schema.table1),
(SELECT COUNT(*) FROM schema.table2),
...
(SELECT COUNT(*) FROM schema.tableN)
FROM DUAL;
最近添加的PIVOT功能可以完全满足您的需要:
SELECT *
FROM ( SELECT level from your_table )
PIVOT ( count(*) for level in ('exec', 'personal') )
基于Taryn的回应，使用OVER()增加了细微差别:
SELECT distributor_id,
COUNT(*) total,
SUM(case when level = 'exec' then 1 else 0 end) OVER() ExecCount,
SUM(case when level = 'personal' then 1 else 0 end) OVER () PersonalCount
FROM yourtable
GROUP BY distributor_id
使用OVER()而不使用()将为您提供整个数据集的总计数。
如果您的SQL风格支持它，您可以使用COUNT_IF()基于条件进行计数。
SELECT
distributor_id,
COUNT(*) AS total_count,
COUNT_IF(level = 'exec') AS exec_count,
COUNT_IF(level = 'personal') AS personal_count
FROM table_name
GROUP BY distributor_id
