数据库表 A 字段id task_id sm_ser_id flag 数据: 1 001 01 y
2 001 01 n
3 001 02 y
4 001 03 n
5 001 03 n
6 001 03 y其中,task_id 是任务id ,sm_ser_id是业务id flag是业务是否成功标识,输入条件 task_id 要求:用一条语句 统计出一个任务 下每个业务的总条数,成功条数和失败条数
2 001 01 n
3 001 02 y
4 001 03 n
5 001 03 n
6 001 03 y其中,task_id 是任务id ,sm_ser_id是业务id flag是业务是否成功标识,输入条件 task_id 要求:用一条语句 统计出一个任务 下每个业务的总条数,成功条数和失败条数
sum(decode(flag,'n',1,0)) 失败条数 from a group by task_id
--insert into ttt select 1, '001', '01', 'y' from dual;
--insert into ttt select 2, '001', '01', 'n' from dual;
--insert into ttt select 3, '001', '02', 'y' from dual;
--insert into ttt select 4, '001', '03', 'n' from dual;
--insert into ttt select 5, '001', '03', 'n' from dual;
--insert into ttt select 6, '001', '03', 'y' from dual;select task_id,sm_ser_id, count(*) as num, SUM(decode(FLAG,'y', 1,'n',0)) AS T,SUM(decode(FLAG,'y', 0,'n',1)) as F from ttt group by task_id,sm_ser_id
TASK_ID SM_SER NUM T F
001 03 3 1 2
001 01 2 1 1
001 02 1 1 0
select task_id,sm_ser_id,count(case when flag='y' then 1 end) 成功条数,
count(case when flag='n' then 1 end) 失败条数
from tb
group by task_id,sm_ser_id
select task_id,sm_ser_id,count(decode(flag,'y',1)) 成功条数,
count(decode(flag,'n',1)) 失败条数
from tb
group by task_id,sm_ser_id
T 成功条数,F 失败条数,
round(T/num,2)*100 成功百分比,
round(f/num,2)*100 失败百分比
from(
select task_id,sm_ser_id, count(*) as num,
SUM(decode(FLAG,'y', 1,'n',0)) AS T,
SUM(decode(FLAG,'y', 0,'n',1)) as F
from ttt group by task_id,sm_ser_id)