SQL> select * from b;客户名 消费起始日期 消费终止日期 消费金额 日均消费额
---------- ------------ ------------ ---------- ----------
A 2005-9-2 2005-9-11 90 10
B 2005-9-5 2005-9-10 100 20
C 2005-9-11 2005-9-18 140 20
D 2005-9-8 2005-9-13 200 40Executed in 0.015 secondsSQL> select i "日期",(select sum((case when b4 <= i and i <=b5 then b3 else 0 end )) from b) as "收入"
from
(select d+r-1 i,d,m from
(select min(b4) d,max(b5) m from b),
(select rownum r from test where rownum <= (select max(b5)-min(b4)+1 from b))
) k
left join b on k.i=b4
order by i日期 收入
----------- ----------
2005-9-2 10
2005-9-3 10
2005-9-4 10
2005-9-5 30
2005-9-6 30
2005-9-7 30
2005-9-8 70
2005-9-9 70
2005-9-10 70
2005-9-11 70
2005-9-12 60
2005-9-13 60
2005-9-14 20
2005-9-15 20
2005-9-16 20
2005-9-17 20
2005-9-18 2017 rows selectedExecuted in 0.046 secondsSQL> 字符串连接的部分,duanzilin有一份详细的说明帖子。
---------- ------------ ------------ ---------- ----------
A 2005-9-2 2005-9-11 90 10
B 2005-9-5 2005-9-10 100 20
C 2005-9-11 2005-9-18 140 20
D 2005-9-8 2005-9-13 200 40Executed in 0.015 secondsSQL> select i "日期",(select sum((case when b4 <= i and i <=b5 then b3 else 0 end )) from b) as "收入"
from
(select d+r-1 i,d,m from
(select min(b4) d,max(b5) m from b),
(select rownum r from test where rownum <= (select max(b5)-min(b4)+1 from b))
) k
left join b on k.i=b4
order by i日期 收入
----------- ----------
2005-9-2 10
2005-9-3 10
2005-9-4 10
2005-9-5 30
2005-9-6 30
2005-9-7 30
2005-9-8 70
2005-9-9 70
2005-9-10 70
2005-9-11 70
2005-9-12 60
2005-9-13 60
2005-9-14 20
2005-9-15 20
2005-9-16 20
2005-9-17 20
2005-9-18 2017 rows selectedExecuted in 0.046 secondsSQL> 字符串连接的部分,duanzilin有一份详细的说明帖子。
feng2(蜀山风云),真是高人,高不可攀啊
b5:终止日期
d:是最小日期
r:是序列(1.2.3.4......)
i:就是结果集中的"日期"
feng2(蜀山风云) 真是高手,
drop table t_test;create table t_test(
customer_name varchar(50) null,
sdate date null,
edate date null,
amount number(14,2) null,
avg_amount number(14,2) null
) ; insert into t_test values ('A','09/02/2005','09/11/2005',90,10);
insert into t_test values ('B','09/05/2005','09/10/2005',90,10);
insert into t_test values ('C','09/11/2005','09/18/2005',140,20);
insert into t_test values ('D','09/08/2005','09/13/2005',200,40);select i "日期",(select sum((case when sdate <= i and i <=edate then avg_amount else 0 end )) from t_test) as "收入"
from
(select d+r-1 i,d,m from
(select min(sdate) d,max(edate) m from t_test),
(select rownum r from t_bi_area where rownum <= (select max(edate)-min(sdate)+1 from t_test))
) k
left join t_test on k.i=sdate
order by i日期 金额
09/02/2005 00:00:00 10
09/03/2005 00:00:00 10
09/04/2005 00:00:00 10
09/05/2005 00:00:00 30
09/06/2005 00:00:00 30
09/07/2005 00:00:00 30
09/08/2005 00:00:00 70
09/09/2005 00:00:00 70
09/10/2005 00:00:00 70
09/11/2005 00:00:00 70
09/12/2005 00:00:00 60
09/13/2005 00:00:00 60
09/14/2005 00:00:00 20
09/15/2005 00:00:00 20
09/16/2005 00:00:00 20
09/17/2005 00:00:00 20
09/18/2005 00:00:00 20
楼主,不要给我分啊。我只是表白一下。
这种sql要我做没有2个小时都不一定有思路的,学习呀!
create table CUSTOMER
(
ID VARCHAR2(10),
START_TIME DATE,
END_TIME DATE,
TOTAL_MONEY FLOAT,
AVG_MONEY FLOAT
)-- Create table
create table STAT
(
DAY DATE,
INCOME FLOAT
)
insert into CUSTOMER (ID, START_TIME, END_TIME, TOTAL_MONEY, AVG_MONEY)
values ('A', to_date('02-09-2005', 'dd-mm-yyyy'), to_date('11-09-2005', 'dd-mm-yyyy'), 90, 10);
insert into CUSTOMER (ID, START_TIME, END_TIME, TOTAL_MONEY, AVG_MONEY)
values ('B', to_date('05-09-2005', 'dd-mm-yyyy'), to_date('10-09-2005', 'dd-mm-yyyy'), 100, 20);
insert into CUSTOMER (ID, START_TIME, END_TIME, TOTAL_MONEY, AVG_MONEY)
values ('C', to_date('11-09-2005', 'dd-mm-yyyy'), to_date('18-09-2005', 'dd-mm-yyyy'), 140, 20);
insert into CUSTOMER (ID, START_TIME, END_TIME, TOTAL_MONEY, AVG_MONEY)
values ('D', to_date('08-09-2005', 'dd-mm-yyyy'), to_date('13-09-2005', 'dd-mm-yyyy'), 200, 40);
commit;我用存储过程写了一个,如果要定期进行统计的话,就加一个Job
create or replace procedure pro_income
as
minStartTime date;
maxEndTime date;
type customer_cursor is ref cursor;
c_cursor customer_cursor;
c_sql char(150);
type rec_customer is record (start_time date,
end_time date,
vag_money float);
customer rec_customer;
income stat.income%type;begin
select min(start_time) into minStartTime from customer;
select max(end_time) into maxEndTime from customer;
c_sql:='select c.start_time,c.end_time,avg_money from customer c';
for i in 1..(maxEndTime-minStartTime) loop
open c_cursor for c_sql;
fetch c_cursor into customer;
income:=0;
while c_cursor%found loop
if minStartTime<customer.end_time and minStartTime>=customer.start_time then
income:=income+customer.vag_money;
end if;
fetch c_cursor into customer;
end loop;
close c_cursor;
insert into stat values(minStartTime,income);
commit;
minStartTime:=minStartTime+1;
end loop;
end pro_income;
DAY INCOME
----------- --------------------------------------------------------------------------------
2005-9-2 10
2005-9-3 10
2005-9-4 10
2005-9-5 30
2005-9-6 30
2005-9-7 30
2005-9-8 70
2005-9-9 70
2005-9-10 50
2005-9-11 60
2005-9-12 60
2005-9-13 20
2005-9-14 20
2005-9-15 20
2005-9-16 20
2005-9-17 20
我把你的脚本分析了一下,但是我看不懂t_bi_area,这是个什么表啊,还是试图啊,请您给给说明一下,本人正在学习中,谢谢!能把你的思路说一下吗?
FROM T,
(SELECT ROWNUM + (SELECT MIN(start_date) FROM T) - 1 AS the_date
FROM user_tables
) S
WHERE the_date >= start_date
AND the_date <= end_date
GROUP BY the_date这样子可以吧。
T-----------------------------楼主的主表名
money-------------------------T表中的日均消费额
start_date,end_date----------分别对应T表中的开始日 和 结束日上面四个替换跑一下试试?我现在还没环境搞。
user_tables 无所谓,不够就换成足够多记录的表。
如果要严谨一点,可以在里面的子查询加个where 限制日期 小于 end_date