复杂sql语句累加求和
两个表,一个部门投资表,一个节假日表,数据如下
create table T_DEPARTMENT_INVEST
(
AREA_DEPARTMENT_NAME VARCHAR2(60),
PLAN_INVEST_DATE VARCHAR2(4000),
TOTAL_MONEY NUMBER
)
;
prompt Loading T_DEPARTMENT_INVEST...
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-01', 10);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-02', 10);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-03', 947);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-04', 254.4);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-05', 520.695741);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-06', 448);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-07', 5);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-08', 0);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-09', 285);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-10', 134234556.8444);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-11', 441.43);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-12', 390);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-13', 305);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-14', 60);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-15', 0);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-16', 505.3);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-17', 240.5);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-18', 331.4);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-19', 501.5);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-20', 812.7);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-21', 100);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-22', 0);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-23', 146);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-24', 70);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-25', 160.2991);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-26', 309.6664);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-27', 284);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-28', 150);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-29', 10);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-30', 0);
insert into T_DEPARTMENT_INVEST (AREA_DEPARTMENT_NAME, PLAN_INVEST_DATE, TOTAL_MONEY)
values ('一部', '2011-05-31', 639.3);
commit;
create table TL_CALENDAR
(
ID NUMBER(11) default 0 not null,
YEAR VARCHAR2(12),
MONTH VARCHAR2(12),
DAY VARCHAR2(12),
DESCRIPTION VARCHAR2(150) default ' ',
OPERATOR NUMBER(11) default 0,
OPER_DATE VARCHAR2(19) default ' ',
CREATOR NUMBER(11) default 0,
CREATE_DATE VARCHAR2(19) default ' ',
REMARK VARCHAR2(150) default ' '
)
;
comment on table TL_CALENDAR
is '该表内记录的数据都为休息日';
alter table TL_CALENDAR
add constraint CALENDAR_KEY primary key (ID);
prompt Loading TL_CALENDAR...
insert into TL_CALENDAR (ID, YEAR, MONTH, DAY, DESCRIPTION, OPERATOR, OPER_DATE, CREATOR, CREATE_DATE, REMARK)
values (140, '2011', '05', '01', null, null, null, 999999999, '2010-08-24', '系统初始化');
insert into TL_CALENDAR (ID, YEAR, MONTH, DAY, DESCRIPTION, OPERATOR, OPER_DATE, CREATOR, CREATE_DATE, REMARK)
values (141, '2011', '05', '07', null, null, null, 999999999, '2010-08-24', '系统初始化');
insert into TL_CALENDAR (ID, YEAR, MONTH, DAY, DESCRIPTION, OPERATOR, OPER_DATE, CREATOR, CREATE_DATE, REMARK)
values (142, '2011', '05', '08', null, null, null, 999999999, '2010-08-24', '系统初始化');
insert into TL_CALENDAR (ID, YEAR, MONTH, DAY, DESCRIPTION, OPERATOR, OPER_DATE, CREATOR, CREATE_DATE, REMARK)
values (143, '2011', '05', '14', null, null, null, 999999999, '2010-08-24', '系统初始化');
insert into TL_CALENDAR (ID, YEAR, MONTH, DAY, DESCRIPTION, OPERATOR, OPER_DATE, CREATOR, CREATE_DATE, REMARK)
values (144, '2011', '05', '15', null, null, null, 999999999, '2010-08-24', '系统初始化');
insert into TL_CALENDAR (ID, YEAR, MONTH, DAY, DESCRIPTION, OPERATOR, OPER_DATE, CREATOR, CREATE_DATE, REMARK)
values (145, '2011', '05', '21', null, null, null, 999999999, '2010-08-24', '系统初始化');
insert into TL_CALENDAR (ID, YEAR, MONTH, DAY, DESCRIPTION, OPERATOR, OPER_DATE, CREATOR, CREATE_DATE, REMARK)
values (146, '2011', '05', '22', null, null, null, 999999999, '2010-08-24', '系统初始化');
insert into TL_CALENDAR (ID, YEAR, MONTH, DAY, DESCRIPTION, OPERATOR, OPER_DATE, CREATOR, CREATE_DATE, REMARK)
values (147, '2011', '05', '28', null, null, null, 999999999, '2010-08-24', '系统初始化');
insert into TL_CALENDAR (ID, YEAR, MONTH, DAY, DESCRIPTION, OPERATOR, OPER_DATE, CREATOR, CREATE_DATE, REMARK)
values (148, '2011', '05', '29', null, null, null, 999999999, '2010-08-24', '系统初始化');
insert into TL_CALENDAR (ID, YEAR, MONTH, DAY, DESCRIPTION, OPERATOR, OPER_DATE, CREATOR, CREATE_DATE, REMARK)
values (1149, '2011', '05', '03', ' ', 0, ' ', 999999999, '2010-08-24', '系统初始化');
insert into TL_CALENDAR (ID, YEAR, MONTH, DAY, DESCRIPTION, OPERATOR, OPER_DATE, CREATOR, CREATE_DATE, REMARK)
values (1172, '2011', '05', '02', null, null, null, 9, '2011-04-19 13:30:21', null);
commit;
现在要实现如下需求,上面部门的投资金额不显示节假日的投资,但是要把节假日的投资金额累加到节假日后的第一个工作日。例如:2011-05-07 投资5万 2011-05-08 投资0 ,节假日中2011-05-07 和 2011-05-08是节假日则,上面的部门投资表不显示节假日的投资,2011-05-09投资金额显示290万。
PS:优先考虑用sql查询实现,如果不行才考虑用存储过程。