设有一个表TEST(ID,收到任务时间,处理任务时间,任务金额):
create table TEST
(
ID VARCHAR2(5) ,
RE_DATE VARCHAR2(10),
CL_DATE VARCHAR2(10),
MONEY NUMBER
);insert into test (ID, RE_DATE,CL_DATE,MONEY) values ('1', '2012-02-10', '2012-02-20', '20');
insert into test (ID, RE_DATE,CL_DATE,MONEY) values ('2', '2012-02-10', '2012-02-20', '30');
insert into test (ID, RE_DATE,CL_DATE,MONEY) values ('3', '2012-02-16', '', '40');
insert into test (ID, RE_DATE,CL_DATE,MONEY) values ('4', '2012-04-15', '2012-05-20', '50');
如何求下列格式数据的sql,求大侠指教!
月份-----对应月已完成条数--对应月已完成总金额--对应月累计未完成条数--对应月累计未完成总金额
2012-02--2------------------50------------------1---------------------40
2012-04--0------------------0-------------------2---------------------90
2012-05--1------------------50------------------1---------------------40
2012-06--0------------------0-------------------1---------------------40列解释:
1、月份:来源于RE_DATE,CL_DATE两列,即取这两列所涉及到的所有非重复月份(substr(re_date,1,7)),若月份中没有当前月,则在最后补上当前月一行;
2、对应月已完成条数:即处理日期月份等于收到日期月份的记录条数;
3、对应月已完成总金额:即处理日期月份等于收到日期月份的记录金额之和;
4、对应月累计未完成条数:截止到该月份历史未完成的记录条数;
5、对应月累计未完成总金额:截止到该月份历史未完成记录的总金额;
create table TEST
(
ID VARCHAR2(5) ,
RE_DATE VARCHAR2(10),
CL_DATE VARCHAR2(10),
MONEY NUMBER
);insert into test (ID, RE_DATE,CL_DATE,MONEY) values ('1', '2012-02-10', '2012-02-20', '20');
insert into test (ID, RE_DATE,CL_DATE,MONEY) values ('2', '2012-02-10', '2012-02-20', '30');
insert into test (ID, RE_DATE,CL_DATE,MONEY) values ('3', '2012-02-16', '', '40');
insert into test (ID, RE_DATE,CL_DATE,MONEY) values ('4', '2012-04-15', '2012-05-20', '50');
如何求下列格式数据的sql,求大侠指教!
月份-----对应月已完成条数--对应月已完成总金额--对应月累计未完成条数--对应月累计未完成总金额
2012-02--2------------------50------------------1---------------------40
2012-04--0------------------0-------------------2---------------------90
2012-05--1------------------50------------------1---------------------40
2012-06--0------------------0-------------------1---------------------40列解释:
1、月份:来源于RE_DATE,CL_DATE两列,即取这两列所涉及到的所有非重复月份(substr(re_date,1,7)),若月份中没有当前月,则在最后补上当前月一行;
2、对应月已完成条数:即处理日期月份等于收到日期月份的记录条数;
3、对应月已完成总金额:即处理日期月份等于收到日期月份的记录金额之和;
4、对应月累计未完成条数:截止到该月份历史未完成的记录条数;
5、对应月累计未完成总金额:截止到该月份历史未完成记录的总金额;
sum(xds) over(order by dt) -sum(wcs) over(order by dt) ljwwcs,
sum(xdm) over(order by dt) -sum(wcm) over(order by dt) ljwwcm
from(
select dt,sum(case when status=1 or status = 2 then 1 else 0 end) xds ,
sum(case when status=1 or status = 2 then money else 0 end) xdm,
sum(case when status=1 or status = 3 then 1 else 0 end) wcs,
sum(case when status=1 or status = 3 then money else 0 end ) wcm
from(
select t.id,
case when substr(t.re_date, 1, 7) = substr(t.cl_date, 1, 7) then substr(t.re_date, 1, 7)
when t.cl_date is not null then substr(t.cl_date, 1, 7) else substr(t.re_date, 1, 7)
end dt, t.money,
case when substr(t.re_date, 1, 7) = substr(t.cl_date, 1, 7) then 1
when t.cl_date is not null then 3 else 2
end status
from test t
union all
select t.id, substr(t.re_date, 1, 7) dt, t.money, 2 status
from test t
where substr(t.re_date, 1, 7) <> substr(t.cl_date, 1, 7)
and t.cl_date is not null
order by 2) tab
group by dt
order by dt) x)
select * from t
union all
select to_char(trunc(sysdate,'MM'),'YYYY-MM'),0,0,min(ljwwcs),min(ljwwcm )
from t group by dt
having dt = (select max(dt) from t ) and max(dt) <> to_char(trunc(sysdate,'MM'),'YYYY-MM');给分吧!!!