eg:表a,有字段date,money要求:显示如下 :
日期 金额
2010-06-07 10.00
2010-06-08 0
2010-06-09 0
2010-06-10 100
直接select t.date,sum(t.money) from a t group by t.date;
结果如下:
日期 金额
2010-06-07 10.00
2010-06-10 100
语句怎么写才能达到我想要的效果,请各位高手帮忙。
日期 金额
2010-06-07 10.00
2010-06-08 0
2010-06-09 0
2010-06-10 100
直接select t.date,sum(t.money) from a t group by t.date;
结果如下:
日期 金额
2010-06-07 10.00
2010-06-10 100
语句怎么写才能达到我想要的效果,请各位高手帮忙。
select dt,sum(money)
from t
where money <> 0
group by dt
--要产生一个每天的视图,然后跟你A表外连接
--举个例子:
with a as(
select to_date('2010-06-07','yyyy-mm-dd') dat, 10 money from dual
union all
select to_date('2010-06-10','yyyy-mm-dd'),100 from dual
)
,
b as(
select mindat+level-1 dat from (select min(dat) mindat ,max(dat) maxdat from a)
connect by level<=maxdat-mindat+1
)
select b.dat,Sum(Nvl(a.money,0)) from b left join a on b.dat=a.dat
group by b.dat
, SUM(A.money) AS 金额
FROM (
SELECT TO_DATE('2010-03-01', 'YYYY-MM-DD') + (ROWNUM - 1) AS DATE_DAY
FROM ALL_OBJECTS
WHERE ROWNUM <= TO_DATE('2010-03-15', 'YYYY-MM-DD') - TO_DATE('2010-03-01', 'YYYY-MM-DD') + 1
) B
, A
WHERE B.DATE_DAY = A.DATE(+)
GROUP BY B.DATE_DAY