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

How to get multiple counts with one SQL query?

提问by Crobzilla

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'

回答by Taryn

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

您可以将CASE语句与聚合函数一起使用。这与PIVOT某些 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

回答by NotMe

One way which works for sure

一种肯定有效的方法

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 ;

EDIT:
See @KevinBalmforth's break down of performance for why you likely don't want to use this method and instead should opt for @Taryn?'s answer. I'm leaving this so people can understand their options.

编辑：
请参阅 @KevinBalmforth 的性能分解，了解为什么您可能不想使用此方法而应选择 @Tayn? 的答案。我要离开这个，所以人们可以理解他们的选择。

回答by Majid Laissi

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

COUNTonly counts non nullvalues and the DECODEwill return non null value 1only if your condition is satisfied.

COUNT仅计算non null值，并且仅当您的条件满足时DECODE才会返回非空值1。

回答by Kevin Balmforth

Building on other posted answers.

以其他已发布的答案为基础。

Both of these will produce the right values:

这两个都会产生正确的值：

select distributor_id,
    count(*) total,
    sum(case when level = 'exec' then 1 else 0 end) ExecCount,
    sum(case when level = 'personal' then 1 else 0 end) PersonalCount
from yourtable
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 myTable a ; 

However, the performance is quite different, which will obviously be more relevant as the quantity of data grows.

然而，性能却大不相同，随着数据量的增长，这显然会更加相关。

I found that, assuming no indexes were defined on the table, the query using the SUMs would do a single table scan, while the query with the COUNTs would do multiple table scans.

我发现，假设没有在表上定义索引，使用 SUM 的查询将执行单个表扫描，而使用 COUNT 的查询将执行多个表扫描。

As an example, run the following script:

例如，运行以下脚本：

IF OBJECT_ID (N't1', N'U') IS NOT NULL 
drop table t1

create table t1 (f1 int)


    insert into t1 values (1) 
    insert into t1 values (1) 
    insert into t1 values (2)
    insert into t1 values (2)
    insert into t1 values (2)
    insert into t1 values (3)
    insert into t1 values (3)
    insert into t1 values (3)
    insert into t1 values (3)
    insert into t1 values (4)
    insert into t1 values (4)
    insert into t1 values (4)
    insert into t1 values (4)
    insert into t1 values (4)


SELECT SUM(CASE WHEN f1 = 1 THEN 1 else 0 end),
SUM(CASE WHEN f1 = 2 THEN 1 else 0 end),
SUM(CASE WHEN f1 = 3 THEN 1 else 0 end),
SUM(CASE WHEN f1 = 4 THEN 1 else 0 end)
from t1

SELECT 
(select COUNT(*) from t1 where f1 = 1),
(select COUNT(*) from t1 where f1 = 2),
(select COUNT(*) from t1 where f1 = 3),
(select COUNT(*) from t1 where f1 = 4)

Highlight the 2 SELECT statements and click on the Display Estimated Execution Plan icon. You will see that the first statement will do one table scan and the second will do 4. Obviously one table scan is better than 4.

突出显示 2 个 SELECT 语句并单击 Display Estimated Execution Plan 图标。您将看到第一个语句将执行一次表扫描，第二个语句将执行 4 次。显然一次表扫描比 4 次要好。

Adding a clustered index is also interesting. E.g.

添加聚集索引也很有趣。例如

Create clustered index t1f1 on t1(f1);
Update Statistics t1;

The first SELECT above will do a single Clustered Index Scan. The second SELECT will do 4 Clustered Index Seeks, but they are still more expensive than a single Clustered Index Scan. I tried the same thing on a table with 8 million rows and the second SELECT was still a lot more expensive.

上面的第一个 SELECT 将执行单个聚集索引扫描。第二个 SELECT 将执行 4 次聚集索引搜索，但它们仍然比单个聚集索引扫描更昂贵。我在一个有 800 万行的表上尝试了同样的事情，第二个 SELECT 仍然要贵得多。

回答by Mihai

For MySQL, this can be shortened to:

对于 MySQL，这可以缩短为：

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

回答by CrazyCasta

Well, if you must have it all in one query, you could do a union:

好吧，如果您必须在一个查询中获得所有内容，您可以进行联合：

SELECT distributor_id, COUNT() FROM ... UNION
SELECT COUNT() AS EXEC_COUNT FROM ... WHERE level = 'exec' UNION
SELECT COUNT(*) AS PERSONAL_COUNT FROM ... WHERE level = 'personal';

Or, if you can do after processing:

或者，如果您可以在处理后执行：

SELECT distributor_id, COUNT(*) FROM ... GROUP BY level;

You will get the count for each level and need to sum them all up to get the total.

您将获得每个级别的计数，并需要将它们全部相加以获得总数。

回答by Frantumn

I do something like this where I just give each table a string name to identify it in column A, and a count for column. Then I union them all so they stack. The result is pretty in my opinion - not sure how efficient it is compared to other options but it got me what I needed.

我做这样的事情，我只是给每个表一个字符串名称以在 A 列中标识它，并为列提供一个计数。然后我将它们全部联合起来，这样它们就会堆叠起来。结果在我看来相当不错——不确定它与其他选项相比的效率如何，但它让我得到了我需要的东西。

select 'table1', count (*) from table1
union select 'table2', count (*) from table2
union select 'table3', count (*) from table3
union select 'table4', count (*) from table4
union select 'table5', count (*) from table5
union select 'table6', count (*) from table6
union select 'table7', count (*) from table7;

Result:

结果：

-------------------
| String  | Count |
-------------------
| table1  | 123   |
| table2  | 234   |
| table3  | 345   |
| table4  | 456   |
| table5  | 567   |
-------------------

回答by mentorrory

Based on Bluefeet's accepted response with an added nuance using OVER():

基于 Bluefeet 接受的响应，使用以下添加了细微差别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

Using OVER()with nothing in the () will give you the total count for the whole dataset.

OVER()在 () 中不使用任何内容将为您提供整个数据集的总数。

回答by Sinte

I think this can also works for you select count(*) as anc,(select count(*) from Patient where sex='F')as patientF,(select count(*) from Patient where sex='M') as patientM from anc

我认为这也适用于你 select count(*) as anc,(select count(*) from Patient where sex='F')as patientF,(select count(*) from Patient where sex='M') as patientM from anc

and also you can select and count related tables like this select count(*) as anc,(select count(*) from Patient where Patient.Id=anc.PatientId)as patientF,(select count(*) from Patient where sex='M') as patientM from anc

并且您也可以像这样选择和计算相关表 select count(*) as anc,(select count(*) from Patient where Patient.Id=anc.PatientId)as patientF,(select count(*) from Patient where sex='M') as patientM from anc