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

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

我知道这个实际的语法是假的，但它会帮助你理解我想要的。 我需要这种格式，因为它是一个更大的查询的一部分 
SELECT distributor_id,
COUNT(*) AS TOTAL,
COUNT(*) WHERE level = 'exec',
COUNT(*) WHERE level = 'personal'

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

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

'SELECT distributor_id, COUNT(*)
GROUP BY distributor_id'
576387 次浏览
小开

有一种方法肯定有效

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 ;
< p > 编辑: < br > 查看@KevinBalmforth对性能的分析，了解为什么你可能不想使用这种方法，而应该选择@Taryn♦的答案。我这样做是为了让人们明白他们的选择
小开

好吧，如果你必须在一个查询中所有这些，你可以做一个联合:

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';

或者，如果处理后可以做:

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

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

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

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

小开
最佳答案

可以在聚合函数中使用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
小开

以其他的回答为基础。

这两种方法都会产生正确的值:

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 ;

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

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

以执行以下脚本为例:

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)

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

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

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

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

小开

对于MySQL，这可以缩短为:

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

我做了一些类似的事情，我只是给每个表一个字符串名称来在列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;

结果:

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

基于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

使用OVER()而不使用()将为您提供整个数据集的总计数。

小开

我认为这也可以为你工作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 Patient.Id=anc.PatientId)as patientF,(select count(*) from Patient where sex='M') as patientM from anc这样选择和计数相关的表

小开

在Oracle中，你可以这样做

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

如果你的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
小开

最近添加的PIVOT功能可以完全满足您的需要:

SELECT *
FROM ( SELECT level from your_table )
PIVOT ( count(*) for level in ('exec', 'personal') )

提交答案