表id 产品 销售额 日期
1 a 100 2007-03-05
2 b 200 2007-03-05
3 c 45 2007-03-08
4 d 56 2007-03-09
5 e 44 2007-03-11
想要这样一个效果:日期 销售额
2007-03-05 300
2007-03-06 0
2007-03-07 0
2007-03-08 45
2007-03-09 56
依次类推把这个月每天的都列出来,没有的为0
1 a 100 2007-03-05
2 b 200 2007-03-05
3 c 45 2007-03-08
4 d 56 2007-03-09
5 e 44 2007-03-11
想要这样一个效果:日期 销售额
2007-03-05 300
2007-03-06 0
2007-03-07 0
2007-03-08 45
2007-03-09 56
依次类推把这个月每天的都列出来,没有的为0
id pro amt date选择条件为'2008-03'的字符串传入input_dateselect aa.date 日期,nvl(bb.amt,0) 销售额
from
(select to_date(input_date||'-01','yyyy-mm-dd)+rownum-1 date
from table a
where rownum<=to_char(last_day(to_date(input_date||'-01','yyyy-mm-dd)),'dd')
) aa,
(
select a.date,sum(a.amt) amt
from table a
where to_char(date,'yyyy-mm-dd')>=input_date||'-01'
and to_char(date,'yyyy-mm-dd')<=to_char(last_day(to_date(input_date||'-01','yyyy-mm-dd)),'yyyy-mm-dd')
group by a.date) bb
where aa.date=bb.date(+)
order by 1这样出来每个月就会有28-31条纪录不等,当天没有金额的就为0
不过这个语句有个前提,就是你的表的纪录至少要大于31条
select aa.date 日期,nvl(bb.amt,0) 销售额
from
(select to_date(input_date||'-01','yyyy-mm-dd')+rownum-1 date
from table a
where rownum<=to_char(last_day(to_date(input_date||'-01','yyyy-mm-dd')),'dd')
) aa,
(
select a.date,sum(a.amt) amt
from table a
where to_char(date,'yyyy-mm-dd')>=input_date||'-01'
and to_char(date,'yyyy-mm-dd')<=to_char(last_day(to_date(input_date||'-01','yyyy-mm-dd)),'yyyy-mm-dd')
group by a.date) bb
where aa.date=bb.date(+)
order by 1