我想知道如何写这个查询。

我知道这个实际的语法是虚假的,但它将帮助您理解我想要的东西。

我需要这种格式,因为它是一个更大查询的一部分。

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

我需要在一个查询中返回这一切。

此外,它需要在一行中,所以下面的将不起作用:

'SELECT distributor_id, COUNT(*)
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♦的答案。我这样做是为了让人们明白他们的选择。

其他回答

基于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()而不使用()将为您提供整个数据集的总计数。

SELECT 
    distributor_id, 
    COUNT(*) AS TOTAL, 
    COUNT(IF(level='exec',1,null)),
    COUNT(IF(level='personal',1,null))
FROM sometable;

COUNT只计算非空值,DECODE只在满足条件时才返回非空值1。

在Oracle中,你可以这样做

SELECT
    (SELECT COUNT(*) FROM schema.table1),
    (SELECT COUNT(*) FROM schema.table2),
    ...
    (SELECT COUNT(*) FROM schema.tableN)
FROM DUAL;

对于MySQL,这可以缩短为:

SELECT distributor_id,
    COUNT(*) total,
    SUM(level = 'exec') ExecCount,
    SUM(level = 'personal') PersonalCount
FROM yourtable
GROUP BY distributor_id

可以将CASE语句与聚合函数一起使用。这基本上和一些RDBMS中的PIVOT函数是一样的:

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