比如
给个sysdate.
要求已sysdate前6个月的数据.
给个sysdate.
要求已sysdate前6个月的数据.
解决方案 »
- 动态查询的问题
- oracle验证身份证(抛砖引玉)
- 如何配置才能让oracle产生redirect数据包?
- oracle9i 误删除表
- 请教oracle问题?
- 求助:按月份累计问题
- Windows XP + VMWare Server 1.0.6 + CentOS 5.2 + Oracle 10g Rac (10.2.0.1)安装文档
- oracle 一个事务分解成多事务的效率问题?
- 做个清除Oracle历史数据的工具,在程序架构上想请高手指点指点!
- 我在oms中导出数据库数据,遇到错误如下:已经添加了节点首选项证明,以及将备份用户设置为作为批处理用户登陆阿
- oracle 数据库查询
- oracle 关于建表时表名的问题~~
create table t1 (d_date date,d_count number);insert into t1 values (date'2012-01-01',100);
insert into t1 values (date'2012-01-02',200);
insert into t1 values (date'2012-01-08',300);
insert into t1 values (date'2012-02-19',100);
insert into t1 values (date'2011-12-01',100);
insert into t1 values (date'2011-11-01',100);
insert into t1 values (date'2011-09-01',100);
select a.c1 月份,nvl(sum(b.d_count),0) 数量 from
(SELECT to_char(ADD_MONTHS(sysdate,-LEVEL),'yyyy-mm') AS c1
FROM DUAL
CONNECT BY LEVEL <= 6) a left join t1 b on a.c1=to_char(b.d_date,'yyyy-mm')
group by a.c1
order by a.c1
月份 数量
--------------------------------
1 2011-09 100
2 2011-10 0
3 2011-11 100
4 2011-12 100
5 2012-01 600
6 2012-02 100
(SELECT to_char(ADD_MONTHS(sysdate,-(LEVEL-1)),'yyyy-mm') AS c1
FROM DUAL
CONNECT BY LEVEL <= 6) a left join t1 b on a.c1=to_char(b.d_date,'yyyy-mm')
group by a.c1
order by a.c1
(SELECT to_char(ADD_MONTHS(sysdate,-(LEVEL-1)),'yyyy-mm') AS c1
FROM DUAL
CONNECT BY LEVEL <= 6) a left join t1 b on a.c1=to_char(b.d_date,'yyyy-mm')
group by a.c1
order by a.c1 月份 数量
--------------------------------
1 2011-10 0
2 2011-11 1
3 2011-12 1
4 2012-01 3
5 2012-02 1
6 2012-03 0