create table tabname1 (id1 int ,month1 DATE);create table tabname1 (id0 INT,id1 int ,month1 DATE);insert into tabname1 values(1,1,to_date('2009-01-01','yyyy-mm-dd'));
insert into tabname1 values(1,1,to_date('2009-02-01','yyyy-mm-dd'));
insert into tabname1 values(1,1,to_date('2009-03-01','yyyy-mm-dd'));
insert into tabname1 values(1,2,to_date('2009-04-01','yyyy-mm-dd'));
insert into tabname1 values(1,2,to_date('2009-05-01','yyyy-mm-dd'));
insert into tabname1 values(1,2,to_date('2009-06-01','yyyy-mm-dd'));
insert into tabname1 values(1,1,to_date('2009-07-01','yyyy-mm-dd'));
insert into tabname1 values(1,1,to_date('2009-08-01','yyyy-mm-dd'));insert into tabname1 values(2,1,to_date('2009-05-01','yyyy-mm-dd'));
insert into tabname1 values(2,1,to_date('2009-06-01','yyyy-mm-dd'));
insert into tabname1 values(2,3,to_date('2009-08-01','yyyy-mm-dd'));
insert into tabname1 values(2,3,to_date('2009-09-01','yyyy-mm-dd'));
希望得到的结果:ID0相同,ID1相同时,month1日期连续的最大值最小值,如下:
ID0,id1,min(month1 ),max(month1 )
1 1 20090101 20090301
1 2 20090401 20090601
1 1 20090701 20090801
2 1 20090501 20090601
2 1 20090801 20090901
insert into tabname1 values(1,1,to_date('2009-02-01','yyyy-mm-dd'));
insert into tabname1 values(1,1,to_date('2009-03-01','yyyy-mm-dd'));
insert into tabname1 values(1,2,to_date('2009-04-01','yyyy-mm-dd'));
insert into tabname1 values(1,2,to_date('2009-05-01','yyyy-mm-dd'));
insert into tabname1 values(1,2,to_date('2009-06-01','yyyy-mm-dd'));
insert into tabname1 values(1,1,to_date('2009-07-01','yyyy-mm-dd'));
insert into tabname1 values(1,1,to_date('2009-08-01','yyyy-mm-dd'));insert into tabname1 values(2,1,to_date('2009-05-01','yyyy-mm-dd'));
insert into tabname1 values(2,1,to_date('2009-06-01','yyyy-mm-dd'));
insert into tabname1 values(2,3,to_date('2009-08-01','yyyy-mm-dd'));
insert into tabname1 values(2,3,to_date('2009-09-01','yyyy-mm-dd'));
希望得到的结果:ID0相同,ID1相同时,month1日期连续的最大值最小值,如下:
ID0,id1,min(month1 ),max(month1 )
1 1 20090101 20090301
1 2 20090401 20090601
1 1 20090701 20090801
2 1 20090501 20090601
2 1 20090801 20090901
解决方案 »
- 求一条UPDATE语句!! 谢谢!!!!!!!!!!!!!!!!!!!!11111!!!!!!!!!!11
- 谁能给我一份oracle 9i的class12.zip?
- sql与Orcle的对比转换???
- 关于连接oracle报错ORA-12500的问题
- 有什么专业工具可以对oracle 8i数据库进行辅助分析、并可以清楚了解到表表之间对应关系?
- 跨用户的触发器
- 为什么?
- 各位好,小弟初学Oracle,请各位指点哪有相关电子书下载,多谢!
- 安装oracle8.1.7 在 TURBO LINUX 6。0 下安装一半就自动退出,怎么解决?
- 关于Oracle和OCCI的几个问题,主要是绑定批量存取方面的,请指教
- oracle存储过程返回结果集问题
- ORA-00959: 表空间 'MC_DATA' 不存在
为啥不在写这个表数据的时候同时直接填(id0,id1,min,max)?
id1,
MIN(month1) AS maxMonth,
MAX(month1) AS minMonth
FROM
(--先利用rownum做出分组信息,然后分组即可
SELECT ID0,
ID1,
MONTH1,
TO_CHAR(month1,'yyyymm') - ROWNUM AS GROUPID
FROM TABNAME1
ORDER BY ID0,
ID1,
MONTH1
)
GROUP BY id0,
id1,
groupid--结果
ID0 ID1 MAXMONTH MINMONTH
---------------------- ---------------------- ------------------------- -------------------------
2 1 2009-05-01 00:00:00 2009-06-01 00:00:00
2 3 2009-08-01 00:00:00 2009-09-01 00:00:00
1 2 2009-04-01 00:00:00 2009-06-01 00:00:00
1 1 2009-01-01 00:00:00 2009-08-01 00:00:00
insert into tabname1 values(1,1,to_date('2009-02-01','yyyy-mm-dd'));
insert into tabname1 values(1,1,to_date('2009-03-01','yyyy-mm-dd'));
insert into tabname1 values(1,2,to_date('2009-04-01','yyyy-mm-dd'));
insert into tabname1 values(1,2,to_date('2009-05-01','yyyy-mm-dd'));
insert into tabname1 values(1,2,to_date('2009-06-01','yyyy-mm-dd'));
insert into tabname1 values(1,1,to_date('2009-07-01','yyyy-mm-dd'));
insert into tabname1 values(1,1,to_date('2009-08-01','yyyy-mm-dd'));insert into tabname1 values(2,1,to_date('2009-05-01','yyyy-mm-dd'));
insert into tabname1 values(2,1,to_date('2009-06-01','yyyy-mm-dd'));
insert into tabname1 values(2,3,to_date('2009-08-01','yyyy-mm-dd'));
insert into tabname1 values(2,3,to_date('2009-09-01','yyyy-mm-dd'));insert into tabname1 values(2,4,to_date('2009-12-01','yyyy-mm-dd'));
insert into tabname1 values(2,4,to_date('2010-01-01','yyyy-mm-dd'));select id0,id1,min(month1),max(month1)
from
(SELECT id0,id1,month1,COUNT(1) over(PARTITION BY id0,id1,dr) dr FROM
(SELECT id0,id1,
month1,
add_months(month1,- DENSE_RANK() OVER(PARTITION BY id0,id1 ORDER BY month1)) dr
FROM tabname1
)
)
group by id0,id1,dr
order by id0,id1 id0 id1 min(month1) max(month1)
--------------------------------------------------------
1 1 1 2009/7/1 2009/8/1
2 1 1 2009/1/1 2009/3/1
3 1 2 2009/4/1 2009/6/1
4 2 1 2009/5/1 2009/6/1
5 2 3 2009/8/1 2009/9/1
6 2 4 2009/12/1 2010/1/1
FROM (SELECT ID0,
ID1,
MONTH1,
ADD_MONTHS(MONTH1,
-ROW_NUMBER() OVER(PARTITION BY ID0,
ID1 ORDER BY ID0,
ID1,
MONTH1)) RN
FROM TABNAME1
ORDER BY ID0, ID1, MONTH1)
GROUP BY id0,ID1, RN
ORDER BY id0, MIN(MONTH1)