单表,有4个字段 SYSTEMTRACE-流水号-主键,responsecode0返回值(00,11,98,N种),time-时间,money现在要查询出每天的00有多少条,11有多少条,98的有多少条请问该怎么写
解决方案 »
- datalength函数计算nvarchar2类型字符串的长度
- oracle中job运行时间比间隔时间还要长
- 着急,sql优化问题~求高手
- 一个数据库远程任务的问题
- 求教一条sql语句
- select t.* from table1 t where t.c >(select avg(t2.c) from table1 t2 where t1.d=t2.d)
- 视图中为何不能同时用order by 与 with read only?
- 特急!!100分,在线等!!关于OPEN CURSOR的问题!!
- 如何计算相邻行之间的最大差值(具体到里边看)
- 我即将学习Oracle,请版主和各位大侠给予指导!谢谢!!
- plsql求答
- 菜鸟又来求助了,关于cast的问题
group by responsecode0,time
这里的时间格式要主意下,如果不是 YYYY-MM-dd 的类型的话,你需要用to_date 做套下表就OK了
group by responsecode0,time
嗯 就 是时间的问题 我数据库中的是varchar2 我想要取到年月日sql:select substr(to_date(systemtime,'yyyy-mm-dd hh24:mi:ss'),1,2) 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)
yinlianerror
from transline
group by substr(to_date(systemtime,'yyyy-mm-dd hh24:mi:ss'),1,2)
order by substr(to_date(systemtime,'yyyy-mm-dd hh24:mi:ss'),1,2)substr(to_date(systemtime,'yyyy-mm-dd hh24:mi:ss'),1,2)这里只取到月 我现在要取年月日
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)
yinlianerror
from transline
group 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')
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)) c3
from t1
group by substr(t_time,1,8)
order by substr(t_time,1,8) time c1 c2 c3
--------------------------------------------
1 2012/1/1 2 1 2
2 2012/1/2 1 1 0
3 2012/1/3 2 1 0