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
怎么动态的一个sql 啊??????这是我关注的重点....
月份用到了addmonths, 你一的意思是根据类型(日,月)来封装sql,最后查询?
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搞定 貌似有难度了
其实相当于下面的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');
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)
明白,但是怎么把这里的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)
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; 怎么这个只查出来一个,.,
老大,又是你啊.多谢帮忙. 关键我想一个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
将日期改成参数就可以了 月份也差不多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
你一的意思是根据类型(日,月)来封装sql,最后查询?
connect by level <= to_date('2012-02-01','yyyy-mm-dd') - to_date('2012-01-01','yyyy-mm-dd') +1
这段sql,怎么变成按月的?
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搞定 貌似有难度了
其实相当于下面的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');
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)
明白,但是怎么把这里的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)
from dual connect by level <= to_date('01/06/12','dd/mm/yy') - to_date('01/08/12','dd/mm/yy') +1;
怎么这个只查出来一个,.,