大家好!现我有一表A,其中数据为:
SAVETIME MOORMT STATUS
1 2009-7-9 13:28:37 1 DELIVRD
2 2009-7-9 13:28:35 0
3 2009-6-22 16:18:48 1 DELIVRD
4 2009-6-22 16:18:48 0
5 2009-6-22 11:18:52 1 FAIL
6 2009-6-22 11:18:52 0
7 2009-6-22 9:47:56 1 DELIVRD
8 2009-6-22 9:47:56 0
9 2009-6-22 9:44:05 1 DELIVRD
现,我想查出按日期to_char(savetime,'yyyy-mm-dd')格式统计,在每天中moormt为1的条数,moormt为0的条数,及moormt为1且status为'DELIVRD'的条数,结果模式如下:
日期 mo mt success
2009-7-9 1 1 1
2009-6-22 4 4 3
请问该SQL该怎样写?
SAVETIME MOORMT STATUS
1 2009-7-9 13:28:37 1 DELIVRD
2 2009-7-9 13:28:35 0
3 2009-6-22 16:18:48 1 DELIVRD
4 2009-6-22 16:18:48 0
5 2009-6-22 11:18:52 1 FAIL
6 2009-6-22 11:18:52 0
7 2009-6-22 9:47:56 1 DELIVRD
8 2009-6-22 9:47:56 0
9 2009-6-22 9:44:05 1 DELIVRD
现,我想查出按日期to_char(savetime,'yyyy-mm-dd')格式统计,在每天中moormt为1的条数,moormt为0的条数,及moormt为1且status为'DELIVRD'的条数,结果模式如下:
日期 mo mt success
2009-7-9 1 1 1
2009-6-22 4 4 3
请问该SQL该怎样写?
select to_char(savetime,'yyyy-mm-dd')日期,
count(decode(moormt,1,1))mo,
count(decode(moormt,0,1))mt,
count(case when moormt=1 and status='DELIVRD' then 1 end)success
from a
group by to_char(savetime,'yyyy-mm-dd')
order by 日期
select to_char(savetime,'yyyy-mm-dd')日期,
sum(case when moormt=1 then 1 else 0 end )mo,
sum(case when moormt=0 then 1 else 0 end )mt,
sum(case when moormt=1 and status='DELIVRD' then 1 end)success
from a
group by to_char(savetime,'yyyy-mm-dd')
order by 日期