动态日期,按日月统计的问题...

[hailong326]

table a(qty, type, datetime);
qty type datetime
3 apple 07/01/12
4 peach 07/15/12
5 watermelon 09/01/12
........

我现在要按日/月统计水果销售情况,sql如下, 现在存在这样一个问题.
例如: 我要查看 1月到5月的统计情况.但是只有3月和4月有数据. 我其实要显示所有月份的数据.没有的设置为0. 同样的问题存在按日统计里.
我在程序里调用下面的sql,只要传入dateformat(mm/yy, dd/mm/yy) 和 startDate&endDate,一个sql就可以搞定.
我现在想写一个sql解决我上面提到的问题.如何实现,日期是动态的.
例如:
1. dateformat='mm/yy', startDate='01/01/12', endDate='10/01/12', 统计1月到10月的所有数据.
2. dateformat='dd/mm/yy', startDate='01/01/12', endDate='02/01/12', 统计1月1号到2月1号的所有数据.

-- 按月/日统计
select sum(nvl(qty,0)) cnt, type, to_char(a.createtime, ':dateformat')
from a where datetime between :startDate and :endDate
group by type, to_char(a.createtime, ':dateformat');

[hailong326]

select decode('mm/yy','dd/mm/yy',to_date('01/01/12','dd/mm/yy')+level-1,add_months(date'01/01/12',level-1)) a_date
from dual
connect by level <= decode('mm/yy','dd/mm/yy',
to_date('01/02/12','dd/mm/yy') - to_date('01/01/12','dd/mm/yy') +1,
months_between(to_date('01/02/12','dd/mm/yy'),to_date('01/01/12','dd/mm/yy'))+1);

我想给日子格式化成 dd/mm/yy 怎么不行啊

[大明湖畔_帅锅]

根据传入的参数类型判断 是创建日期还是月份

select decode(dateformat,'dd/mm/yy',
              to_date('2012-01-01','yyyy-mm-dd')+level-1,
              add_months(to_date('2012-01-01','yyyy-mm-dd'),level-1)) a_date
from dual
connect by level <= decode(dateformat,'dd/mm/yy',
                         to_date('2012-02-01','yyyy-mm-dd') - to_date('2012-01-01','yyyy-mm-dd') +1,
                         months_between(to_date('2012-02-01','yyyy-mm-dd'),to_date('2012-01-01','yyyy-mm-dd'))+1)

[hailong326]

[Quote=引用 12 楼 的回复:]

根据传入的参数类型判断 是创建日期还是月份

SQL code

select decode(dateformat,'dd/mm/yy',
to_date('2012-01-01','yyyy-mm-dd')+level-1,
add_months(to_date('2012-01-01','yyyy-mm-dd'),level-1)……
[/Quote]

明白,但是怎么把这里的yyyy-mm-dd 格式化成 dd/mm/yy

[大明湖畔_帅锅]

select decode(dateformat,'dd/mm/yy',
              to_date('01/01/2012','dd/mm/yy')+level-1,
              add_months(to_date('01/01/2012','dd/mm/yy'),level-1)) a_date
from dual
connect by level <= decode(dateformat,'dd/mm/yy',
                           to_date('01/02/2012','dd/mm/yy') - to_date('01/01/2012','dd/mm/yy') +1,
                           months_between(to_date('01/02/2012','dd/mm/yy'),to_date('01/01/2012','dd/mm/yy'))+1)

[hailong326]

明白了.结账....

[hailong326]

select to_char(to_date('01/06/12','dd/mm/yy')+level-1,'dd/mm/yy') a_date
from dual connect by level <= to_date('01/06/12','dd/mm/yy') - to_date('01/08/12','dd/mm/yy') +1;


怎么这个只查出来一个,.,

[hailong326]

哈哈,我的错.sorry

[大明湖畔_帅锅]

一眼看上去 6-8 查询出来肯定只有当天了

[大明湖畔_帅锅]

构造日期表关联就可以了

[hailong326]

[Quote=引用 1 楼 的回复:]

构造日期表关联就可以了
[/Quote]

老大,又是你啊.多谢帮忙. 关键我想一个sql支持按月按日统计.怎么实现?

[大明湖畔_帅锅]

with t1 as
(
     select date'2012-03-01' c1,100 c2 from dual
     union all
     select date'2012-03-14' c1,200 c2 from dual
     union all
     select date'2012-04-03' c1,300 c2 from dual
     union all
     select date'2012-04-22' c1,400 c2 from dual
)

select to_char(a_date,'yyyy-mm') a_date,nvl(sum(c2),0) c2
from 
(
select add_months(date'2012-01-01',level-1) a_date
from dual
connect by level <= 5
) a left join t1 b on to_char(a_date,'yyyy-mm') = to_char(c1,'yyyy-mm')
group by to_char(a_date,'yyyy-mm')
order by a_date

      a_date   c2
---------------------------
1	2012-01	0
2	2012-02	0
3	2012-03	300
4	2012-04	700
5	2012-05	0

[大明湖畔_帅锅]

按日

with t1 as
(
     select date'2012-01-01' c1,100 c2 from dual
     union all
     select date'2012-01-14' c1,200 c2 from dual
     union all
     select date'2012-01-03' c1,300 c2 from dual
     union all
     select date'2012-01-22' c1,400 c2 from dual
)

select a_date,nvl(sum(c2),0) c2
from 
(
select date'2012-01-01'+level-1 a_date
from dual
connect by level <= 32
) a left join t1 b on a_date= c1
group by a_date
order by a_date

[大明湖畔_帅锅]

可以在程序里面判断 传起始日期和一个类型(0 按月 1按日)

然后判断类型 来查询sql


将日期改成参数就可以了 月份也差不多

select a_date,nvl(sum(c2),0) c2
from 
(
select to_date('2012-01-01','yyyy-mm-dd')+level-1 a_date
from dual
connect by level <= to_date('2012-02-01','yyyy-mm-dd') - to_date('2012-01-01','yyyy-mm-dd') +1
) a left join t1 b on a_date= c1
group by a_date
order by a_date

[hailong326]

怎么动态的一个sql 啊??????

这是我关注的重点....

[hailong326]

月份用到了addmonths,
你一的意思是根据类型(日,月)来封装sql,最后查询?

[hailong326]

select to_date('2012-01-01','yyyy-mm-dd')+level-1 a_date from dual
connect by level <= to_date('2012-02-01','yyyy-mm-dd') - to_date('2012-01-01','yyyy-mm-dd') +1


这段sql,怎么变成按月的?

[大明湖畔_帅锅]

根据传入的参数类型 构造日期或者月份

将'a'替换dateformat 'b' 替换 'dd/mm/yy' or 'mm/yy'

select decode('a','b',to_date('2012-01-01','yyyy-mm-dd')+level-1,add_months(date'2012-01-01',level-1)) a_date
from dual
connect by level <= decode('a','b',
                           to_date('2012-02-01','yyyy-mm-dd') - to_date('2012-01-01','yyyy-mm-dd') +1,
                           months_between(to_date('2012-02-01','yyyy-mm-dd'),to_date('2012-01-01','yyyy-mm-dd'))+1)

关键问题是 表里面是日期类型 如何和这个 不知道是日期还是月份的表来关联呢 ？ 一个sql搞定 貌似有难度了

[hailong326]

[Quote=引用 9 楼 的回复:]

根据传入的参数类型 构造日期或者月份

将'a'替换dateformat 'b' 替换 'dd/mm/yy' or 'mm/yy'

SQL code

select decode('a','b',to_date('2012-01-01','yyyy-mm-dd')+level-1,add_months(date'2012-01-01',level-1)) a_date
from ……
[/Quote]

其实相当于下面的sql 跟你的sql join, 日期都已经格式化了,应该没问题,你觉得呢.
我现在有两个思路, 用下面的sql,在java程序里封装, 还有就是现在在sql里就封装好了.


select sum(nvl(qty,0)) cnt, type, to_char(a.createtime, ':dateformat')
from a where datetime between :startDate and :endDate
group by type, to_char(a.createtime, ':dateformat');