select to_char(a.sale_date,'yyyy-mm-dd'), sum(b.sale_num) from yourtable a, yourtable b where b.sale_date <= a.sale_date group by a.sale_date
select to_char(a.sale_date,'yyyy-mm-dd') 日期, sum(select sale_num from table_name where sale_date<=a.sale_date) 月销售量 from table_name a gourp by sale_date;
用函数来写,通过传入当前日期返回小于此日期的销售量之和。CREATE OR REPLACE FUNCTION add_sale( date1 in date) return number is b NUMBER(4); begin b:=0; select sum(销售量) into b from 销售表 where 日期<=date1; return b; end; / show error调用查询sql: select 日期,add_sale(日期) from 销售表;
真是麻烦。分析函数(窗口函数)就可以解决。 select 日期,销售量,sum(销售量)over(order by 日期) as 月累计量 from table. 去看看吧。不过,要8i支持。
select to_char(a.sale_date,'yyyy-mm-dd') 日期, sum(select sale_num from table_name where sale_date<=a.sale_date and sale_date>=to_date(to_char(a.sale_date,'yyyy-mm')||'-01','yyyy-mm-dd')) 月累计销售量 from table_name a group by sale_date;
select to_char(sale_date, 'yyyy-mm') as sale_date, sum(sale_qty) as sale_qty from sale_flow group by sale_date;
sorry! It must be:Select A.sale_date, Sum(A.sale_qty) from (select to_char(sale_date,'yyyy-mm') as sale_date, sale_qty from sale_flow) A group by A.sale_date;
from yourtable a, yourtable b
where b.sale_date <= a.sale_date
group by a.sale_date
sum(select sale_num from table_name where sale_date<=a.sale_date) 月销售量
from table_name a gourp by sale_date;
date1 in date)
return number is b NUMBER(4);
begin
b:=0;
select sum(销售量) into b from 销售表 where 日期<=date1;
return b;
end;
/
show error调用查询sql:
select 日期,add_sale(日期) from 销售表;
select 日期,销售量,sum(销售量)over(order by 日期) as 月累计量
from table.
去看看吧。不过,要8i支持。
sum(select sale_num from table_name where sale_date<=a.sale_date and
sale_date>=to_date(to_char(a.sale_date,'yyyy-mm')||'-01','yyyy-mm-dd')) 月累计销售量
from table_name a group by sale_date;
from sale_flow
group by sale_date;
It must be:Select A.sale_date, Sum(A.sale_qty)
from (select to_char(sale_date,'yyyy-mm') as sale_date, sale_qty from sale_flow) A group by A.sale_date;