单表，有4个字段 SYSTEMTRACE-流水号-主键，responsecode0返回值（00，11，98，。。。N种），time-时间，money
现在要查询出每天的00有多少条,11有多少条，98的有多少条
请问该怎么写
------解决方案--------------------
- SQL code
select to_char(to_date(systemtime,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd') as datetime,sum(case when responsecode='00'and money>0 then 1 else 0 end) successcount,sum(case when responsecode<>'00' and money<=0 then 1 else 0 end) failercount,sum(case when responsecode='98' then 1 else 0 end) yinlianerrorfrom translinegroup by to_char(to_date(systemtime,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd')order by to_char(to_date(systemtime,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd')
------解决方案--------------------
- SQL code
create table t1 (SYSTEMTRACE number(5),responsecode varchar2(10),t_time varchar2(20),money number(10,2));insert into t1 values (1,'00','20120101121212',100);insert into t1 values (2,'98','20120101121212',200);insert into t1 values (3,'00','20120101121212',300);insert into t1 values (4,'11','20120101121212',150);insert into t1 values (5,'98','20120101121212',250);insert into t1 values (6,'00','20120102121212',500);insert into t1 values (7,'11','20120102121212',200);insert into t1 values (8,'00','20120103121212',300); insert into t1 values (9,'00','20120103121212',100);insert into t1 values (10,'11','20120103121212',700);commit;select to_date(substr(t_time,1,8),'yyyy-mm-dd') time, sum(decode(responsecode,'00',1,0)) c1, sum(decode(responsecode,'11',1,0)) c2, sum(decode(responsecode,'98',1,0)) c3from t1group by substr(t_time,1,8)order by substr(t_time,1,8) time c1 c2 c3--------------------------------------------1 2012/1/1 2 1 22 2012/1/2 1 1 03 2012/1/3 2 1 0