复杂sql语句累加求和

Question

两个表，一个部门投资表，一个节假日表，数据如下
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查询实现，如果不行才考虑用存储过程。

Answer 1

--写得有点复杂了，期待有人能简化，另外表结构设计得不太好
--日期尽量定义为date型，并且不要分成year,month,day来存储，除非有更好的理由
WITH tt AS
 (SELECT to_date(t.year || t.month || t.day, 'yyyymmdd') cal
    FROM tl_calendar t)
SELECT f.area_department_name,
       f.plan_invest_date,
       nvl(e.total_money, f.total_money) total_money
  FROM (SELECT d.area_department_name,
               c.e_date + 1 e_date,
               SUM(d.total_money) total_money
          FROM (SELECT MIN(cal) s_date, MAX(cal) e_date
                  FROM tt a
                 START WITH NOT EXISTS
                 (SELECT 1 FROM tt b WHERE b.cal = a.cal - 1)
                CONNECT BY PRIOR a.cal = a.cal - 1
                 GROUP BY rownum - LEVEL) c,
               T_DEPARTMENT_INVEST d
         WHERE to_date(d.plan_invest_date, 'yyyy-mm-dd') BETWEEN c.s_date AND
               c.e_date + 1
         GROUP BY d.area_department_name, c.e_date) e,
       (SELECT d.area_department_name,
               to_date(d.plan_invest_date, 'yyyy-mm-dd') plan_invest_date,
               d.total_money
          FROM T_DEPARTMENT_INVEST d
         WHERE NOT EXISTS
         (SELECT 1
                  FROM tt a
                 WHERE d.plan_invest_date = to_char(a.cal, 'yyyy-mm-dd'))) f
 WHERE f.area_department_name = e.area_department_name(+)
   AND plan_invest_date = e.e_date(+)

Answer 2

高手，哈哈，那个节假日表是别人设计的，我只是通过同义词查询，谢谢！WITH tt AS
 (SELECT to_date(t.year || t.month || t.day, 'yyyymmdd') cal
    FROM tl_calendar t)SELECT MIN(cal) s_date, MAX(cal) e_date
                  FROM tt a
                  CONNECT BY PRIOR a.cal = a.cal - 1
                 START WITH NOT EXISTS
                 (SELECT 1 FROM tt b WHERE b.cal = a.cal - 1)
                
                 GROUP BY rownum - LEVEL上面这一段查询有点慢，要4秒左右才能出来，能不能优化呢？

Answer 3

你的节假日表tl_calendar有多少数据？
如果太多，可以分时间段处理。

Answer 4

1000多条记录，在别的用户下，不知道他们怎么考虑这么建表的，哈哈，我在想应该可以弄个触发器，然后把他们数据拿过来，改成date类型，一个字段，如何分时间段处理呢？

Answer 5

可以把tl_calendar同步到本地，并且把假日字段建立为一日期字段，增加索引。

Answer 6

WITH tt AS
 (SELECT to_date(t.year || t.month || t.day, 'yyyymmdd') cal
  FROM tl_calendar t)SELECT MIN(cal) s_date, MAX(cal) e_date
  FROM (select cal, cal-rank() over(order by cal)+1 diff from tt) a
  group by diff

Answer 7

测一下这个的速度呢？with vw_d as
 (select t.year || '-' || t.month || '-' || t.day v_date,
         t1.area_department_name,
         t1.total_money
    from tl_calendar t, t_department_invest t1
   where t.year || '-' || t.month || '-' || t.day = t1.plan_invest_date)select v1.area_department_name,
       v1.plan_invest_date,
       (select nvl(sum(vw_d.total_money), 0)
          from vw_d
         where vw_d.v_date between v1.ld and plan_invest_date
           and vw_d.area_department_name = v1.area_department_name) +
       nvl(v1.total_money, 0)
  from (select t.*,
               nvl(lag(t.plan_invest_date) over(order by t.plan_invest_date),
                   '1000-01-01') ld
          from t_department_invest t
         where not exists
         (select null from vw_d where vw_d.v_date = t.plan_invest_date)) v1