uid date sale (date-sum-sale)
1 2010-05-01 100 100
1 2010-05-02 200 300
1 2010-05-03 300 600
1 2010-05-04 400 1000uid是销售人员编号,date是销售日期,sale是金额;现在要用SQL查询出上面的结果,date-sum-sale是原来表没有的字段,它用来记录累计的销售金额!
怎么写sql??
1 2010-05-01 100 100
1 2010-05-02 200 300
1 2010-05-03 300 600
1 2010-05-04 400 1000uid是销售人员编号,date是销售日期,sale是金额;现在要用SQL查询出上面的结果,date-sum-sale是原来表没有的字段,它用来记录累计的销售金额!
怎么写sql??
2 (select 2 uids,date '2010-5-1' dates,100 sale from dual
3 union all
4 select 2,date '2010-5-2' ,200 from dual
5 union all
6 select 2,date '2010-5-3' ,300 from dual
7 union all
8 select 2,date '2010-5-4' ,400 from dual
9 union all
10 select 3,date '2010-5-2' ,200 from dual
11 union all
12 select 3,date '2010-5-3' ,300 from dual)
13 select uids,dates,sale,sum(sale) over(partition by uids order by dates) date_sum_sale
14 from tmp;
UIDS DATES SALE DATE_SUM_SALE
---------- ----------- ---------- -------------
2 2010/5/1 100 100
2 2010/5/2 200 300
2 2010/5/3 300 600
2 2010/5/4 400 1000
3 2010/5/2 200 200
3 2010/5/3 300 500
6 rows selected
SQL> with tb2 as
2 (select 2 uids,date '2010-5-1' dates,100 sale from dual
3 union all
4 select 2,date '2010-5-2' ,200 from dual
5 union all
6 select 2,date '2010-5-3' ,300 from dual
7 union all
8 select 2,date '2010-5-4' ,400 from dual
9 union all
10 select 3,date '2010-5-2' ,200 from dual
11 union all
12 select 3,date '2010-5-3' ,300 from dual)
13 select uids,to_char(dates,'yyyy-mm-dd') dates,sale,
14 (select sum(sale) from tb2 where uids=a.uids and dates<=a.dates) date_sum_sale
15 from tb2 a
16 / UIDS DATES SALE DATE_SUM_SALE
---------- ---------- ---------- -------------
2 2010-05-01 100 100
2 2010-05-02 200 300
2 2010-05-03 300 600
2 2010-05-04 400 1000
3 2010-05-02 200 200
3 2010-05-03 300 500已选择6行。