有个test表
day(日期) class(类别) price(价格)
2008-9-1 1 50
2008-9-1 2 40
2008-9-2 1 30
2008-9-2 2 20 需要按日期对price汇总求和,并且需要对该月内截止到当前日期,所有类别的价格求和, 要求最后显示的结果是:
日期求和 本月内截止到当前天所有值求和
2008-9-1 90 90 --1号所有记录求和
2008-9-2 50 140 --1号和2号所有记录求和请教这个怎么写,
day(日期) class(类别) price(价格)
2008-9-1 1 50
2008-9-1 2 40
2008-9-2 1 30
2008-9-2 2 20 需要按日期对price汇总求和,并且需要对该月内截止到当前日期,所有类别的价格求和, 要求最后显示的结果是:
日期求和 本月内截止到当前天所有值求和
2008-9-1 90 90 --1号所有记录求和
2008-9-2 50 140 --1号和2号所有记录求和请教这个怎么写,
SELECT DAY, SUM(PRICE), S
FROM (SELECT S.*,
SUM(PRICE) OVER( ORDER BY DAY RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) S
FROM TESTX S)
GROUP BY DAY, S
ORDER BY 1;
输出:
DAY SUM(PRICE) S
2008-9-1 90 90
2008-9-2 50 140
WITH TEST AS
(SELECT to_date('2008-9-1','yyyy-mm-dd') day, 1 class,50 price
FROM DUAL
UNION ALL
SELECT to_date('2008-9-1','yyyy-mm-dd') day, 2 class,40 price
FROM DUAL
UNION ALL
SELECT to_date('2008-9-2','yyyy-mm-dd') day, 1 class,30 price
FROM DUAL
UNION ALL
SELECT to_date('2008-9-2','yyyy-mm-dd') day, 2 class,20 price
FROM DUAL
)
select day,
sum(price) 日期求和,
jj 本月内所有值求和
from
(
select t.*,
sum(price) over (order by to_char(day,'yyyy-mm-dd')) jj
from test t
)
group by day,jj;
sum(t.price) OVER(PARTITION BY t.day),
sum(t.price) OVER(PARTITION BY to_char(day, 'yyyy-mm') ORDER BY t.day)
from test t
SQL> select * from t;DAY CLASS PRICE
------------------------------ --------------------------------------- ---------------------------------------
2008-9-1 1 50
2008-9-1 2 40
2008-9-2 1 30
2008-9-2 2 20SQL>
SQL> select day,sum1,lag(sum1,1,0)over(order by day)+sum1 as sum2 from (
2 select day,sum(price) sum1 from t group by day
3 );DAY SUM1 SUM2
------------------------------ ---------- ----------
2008-9-1 90 90
2008-9-2 50 140SQL>
OPER@tl>select * from test;DAY CLASS PRICE
------------------- ---------- ----------
2008-09-01 00:00:00 1 50
2008-09-01 00:00:00 2 40
2008-09-02 00:00:00 1 30
2008-09-02 00:00:00 2 20
OPER@tl>select day,nvl(lag(sum_fee) over(order by day),0)+sum_fee
2 from (
3 select distinct day,sum(price) over(partition by day) sum_fee
4 from test);DAY NVL(LAG(SUM_FEE)OVER(ORDERBYDAY),0)+SUM_FEE
------------------- -------------------------------------------
2008-09-01 00:00:00 90
2008-09-02 00:00:00 140