-- Create table
create table TEST
(
NAME VARCHAR2(10),
N_MONTH NUMBER(10)
);
insert into TEST (NAME, N_MONTH)
values ('A', 201001);
insert into TEST (NAME, N_MONTH)
values ('A', 201002);
insert into TEST (NAME, N_MONTH)
values ('A', 201003);
insert into TEST (NAME, N_MONTH)
values ('A', 201006);
insert into TEST (NAME, N_MONTH)
values ('A', 201009);
insert into TEST (NAME, N_MONTH)
values ('A', 201010);
insert into TEST (NAME, N_MONTH)
values ('A', 201011);
insert into TEST (NAME, N_MONTH)
values ('B', 201001);
insert into TEST (NAME, N_MONTH)
values ('B', 201002);
insert into TEST (NAME, N_MONTH)
values ('B', 201003);
insert into TEST (NAME, N_MONTH)
values ('B', 201006);
insert into TEST (NAME, N_MONTH)
values ('B', 201009);
insert into TEST (NAME, N_MONTH)
values ('B', 201010);
insert into TEST (NAME, N_MONTH)
values ('B', 201011);
commit;想得到结果如下:
name begin_month end_month
---- ------------ -----------
A 201001 201003
A 201006 201006
A 201009 201011
B 201001 201003
B 201006 201006
B 201009 201011即:如果是连续月份,则取最小的月份作为开始月份,最大月份作为结束月份(例如201001和201003),如果前后都不连续则开始月份和结束月份都是当月(例如 201006).
请教。
create table TEST
(
NAME VARCHAR2(10),
N_MONTH NUMBER(10)
);
insert into TEST (NAME, N_MONTH)
values ('A', 201001);
insert into TEST (NAME, N_MONTH)
values ('A', 201002);
insert into TEST (NAME, N_MONTH)
values ('A', 201003);
insert into TEST (NAME, N_MONTH)
values ('A', 201006);
insert into TEST (NAME, N_MONTH)
values ('A', 201009);
insert into TEST (NAME, N_MONTH)
values ('A', 201010);
insert into TEST (NAME, N_MONTH)
values ('A', 201011);
insert into TEST (NAME, N_MONTH)
values ('B', 201001);
insert into TEST (NAME, N_MONTH)
values ('B', 201002);
insert into TEST (NAME, N_MONTH)
values ('B', 201003);
insert into TEST (NAME, N_MONTH)
values ('B', 201006);
insert into TEST (NAME, N_MONTH)
values ('B', 201009);
insert into TEST (NAME, N_MONTH)
values ('B', 201010);
insert into TEST (NAME, N_MONTH)
values ('B', 201011);
commit;想得到结果如下:
name begin_month end_month
---- ------------ -----------
A 201001 201003
A 201006 201006
A 201009 201011
B 201001 201003
B 201006 201006
B 201009 201011即:如果是连续月份,则取最小的月份作为开始月份,最大月份作为结束月份(例如201001和201003),如果前后都不连续则开始月份和结束月份都是当月(例如 201006).
请教。
SELECT NAME, MIN(n_month) begin_month, MAX(n_month) end_month
FROM test t
START WITH NOT EXISTS (SELECT 1
FROM test a
WHERE a.name = t.name
AND a.n_month = t.n_month - 1)
CONNECT BY PRIOR t.name = t.name
AND PRIOR t.n_month = t.n_month - 1
GROUP BY NAME, rownum - LEVEL
ORDER BY NAME, 2