，求ID相同的连续日期的最大值和最小值
日期：2014-05-17 浏览次数：20879 次
在线等，求ID相同的连续日期的最大值和最小值
希望得到的结果：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
------解决方案--------------------
SELECT id0,
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
------解决方案--------------------
支持跨年
- SQL code
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
------解决方案--------------------
SELECT id0,
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
------解决方案--------------------
支持跨年
- SQL code
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'));
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
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
