我想知道如何编写这个查询。
我知道这个实际的语法是假的,但它会帮助你理解我想要什么。我需要这种格式,因为它是更大查询的一部分。
SELECT distributor_id,
COUNT(*) AS TOTAL,
COUNT(*) WHERE level = 'exec',
COUNT(*) WHERE level = 'personal'
我需要在一个查询中返回所有这些。
此外,它需要在一行中,因此以下内容不起作用:
'SELECT distributor_id, COUNT(*)
GROUP BY distributor_id'
SELECT distributor_id, COUNT(*) AS TOTAL, COUNT(*) WHERE level = 'exec', COUNT(*) WHERE level = 'personal'
您可以将 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 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♦ 的答案。我要离开这个,所以人们可以理解他们的选择。
sum(case...)
解决方案。
group by
,其好处是用简单的 count(*)
替换整个嵌套查询,如@Mihai 所示 - 进一步简化 MySQL 语法.
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
。
distributor_id
?它总共显示 1 行。
基于其他已发布的答案。
这两个都会产生正确的值:
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 次聚集索引搜索,但它们仍然比单个聚集索引扫描更昂贵。我在一个有 800 万行的表上尝试了同样的事情,而第二个 SELECT 仍然要贵得多。
对于 MySQL,这可以缩短为:
SELECT distributor_id,
COUNT(*) total,
SUM(level = 'exec') ExecCount,
SUM(level = 'personal') PersonalCount
FROM yourtable
GROUP BY distributor_id
好吧,如果您必须在一个查询中获得所有信息,则可以进行联合:
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;
您将获得每个级别的计数,并且需要将它们全部加起来以获得总数。
UNION
在生成包含多个 COUNT(*)
函数实例的报告时非常有用。
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') FROM distributors UNION SELECT COUNT() AS EXEC_COUNT FROM distributors WHERE ' at line 1
。
我做了这样的事情,我只是给每个表一个字符串名称以在 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 |
-------------------
a query that I created makes ...
- 该查询在哪里?
基于 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') )
COUNT
将计数distributor_id
明智。不是表的所有行,对吗?