mysql多条件count_mysql 不同条件count ，多条件count()
create table abc(A int,B int)
Select A,count(B) as total from ABC group by A
Select A,count(B) as total1 from ABC where B > 30 group by A
Select A,count(B) as totlal2 from ABC where B > 20 group by A
如何合并这三个查询？
得到一个查询结果：
A,total,total1,total2
答：
Select A,
count(B) as total,
sum(case when B > 30 then 1 else 0 end) as total1,
sum(case when B > 20 then 1 else 0 end) as total2
from ABC group by A
自己的例子
select count(1) from origin_crx_data where create_time like ‘2019-02-23%‘ and source=‘12345‘
select count(1) from origin_crx_data where active_time like ‘2019-02-23%‘ and source=‘12345‘
合并为一条sql
SELECT sum(case when create_time like ‘2019-02-23%‘ then 1 else 0 end) as inst_count,sum(case when active_time like ‘2019-02-23%‘ then 1 else 0 end) as active_count FROM origin_crx_data where source=‘12345‘
create table abc(A int,B int) Select A,count(B) as total from ABC group by A Select A,count(B) as total1 from ABC where B > 30 group by A Select A,count(B) as totlal2 from ABC where B > 20 group by A 如何合并这三个查询？ 得到一个查询结果： A,total,total1,total2 答： Select A, count(B) as total, sum(case when B > 30 then 1 else 0 end) as total1, sum(case when B > 20 then 1 else 0 end) as total2 from ABC group by A 自己的例子 select count(1) from origin_crx_data where create_time like ‘2019-02-23%‘ and source=‘12345‘ select count(1) from origin_crx_data where active_time like ‘2019-02-23%‘ and source=‘12345‘ 合并为一条sql SELECT sum(case when create_time like ‘2019-02-23%‘ then 1 else 0 end) as inst_count,sum(case when active_time like ‘2019-02-23%‘ then 1 else 0 end) as active_count FROM origin_crx_data where source=‘12345‘