复杂sql语句累加求和

feiyu107 2011-05-28 02:06:06
两个表,一个部门投资表,一个节假日表,数据如下
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查询实现,如果不行才考虑用存储过程。
...全文
294 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
feiyu107 2011-05-30
  • 打赏
  • 举报
回复
还是用存储过程把日期表同步了,呵呵,谢谢各位
bobfang 2011-05-30
  • 打赏
  • 举报
回复
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
tangren 2011-05-30
  • 打赏
  • 举报
回复
可以把tl_calendar同步到本地,并且把假日字段建立为一日期字段,增加索引。
vber1010 2011-05-30
  • 打赏
  • 举报
回复
测一下这个的速度呢?

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

feiyu107 2011-05-29
  • 打赏
  • 举报
回复
高手,哈哈,那个节假日表是别人设计的,我只是通过同义词查询,谢谢!

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秒左右才能出来,能不能优化呢?
feiyu107 2011-05-29
  • 打赏
  • 举报
回复
1000多条记录,在别的用户下,不知道他们怎么考虑这么建表的,哈哈,我在想应该可以弄个触发器,然后把他们数据拿过来,改成date类型,一个字段,如何分时间段处理呢?
tangren 2011-05-29
  • 打赏
  • 举报
回复
你的节假日表tl_calendar有多少数据?
如果太多,可以分时间段处理。
tangren 2011-05-28
  • 打赏
  • 举报
回复
--写得有点复杂了,期待有人能简化,另外表结构设计得不太好
--日期尽量定义为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(+)

3,492

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧