MySQL根据条件进行计数和求和 [英] MySQL Count and sum based on condition
本文介绍了MySQL根据条件进行计数和求和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
是否有一种方法可以为每个PN计数SN并根据条件(在以下情况下为Loc)对它进行求和?
Is there a way to count SN for each PN and sum it based on a condition (in below case Loc)?
create table table1 (
code int(10) primary key,
PN varchar(10) not null,
SN varchar(10) not null,
Loc varchar(10));
insert into table1 values (1,'T1','a1','a');
insert into table1 values (2,'T1','a2','a');
insert into table1 values (3,'T1','a3','a');
insert into table1 values (4,'T1','a4','b');
insert into table1 values (5,'T1','a5','b');
insert into table1 values (6,'T1','a6','b');
insert into table1 values (7,'T2','a1','a');
insert into table1 values (8,'T2','a2','a');
insert into table1 values (9,'T2','a3','a');
insert into table1 values (10,'T2','a4','b');
insert into table1 values (11,'T2','a5','b');
insert into table1 values (12,'T2','a6','b');
insert into table1 values (13,'T2','a7','b');
我试图达到的结果是:
PN a b
T1 3 3
T2 3 4
推荐答案
这只是条件聚合:
select pn, sum(loc = 'a') as a, sum(loc = 'b') as b
from table1
group by pn;
如果您有loc
值的未知列表,则可能需要动态查询. Google"MySQL动态枢纽".
If you have an unknown list of loc
values, then you might need a dynamic query. Google "MySQL dynamic pivot".
这篇关于MySQL根据条件进行计数和求和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文