Oracle SQL查询

create table  test1
(start_date date,
amount number)

insert into test1 values('18-jan-17', 27000);
insert into test1 values('19-jan-17', 27000);
insert into test1 values('20-jan-17', 30000);
insert into test1 values('21-jan-17', 30000);
insert into test1 values('22-jan-17', 30000);
insert into test1 values('23-jan-17', 29000);
insert into test1 values('24-jan-17', 29000);
insert into test1 values('25-jan-17', 30000);
insert into test1 values('26-jan-17', 30000);

commit;

START_DATE         AMOUNT
18-JAN-17 00:00:00 27000
19-JAN-17 00:00:00 27000
20-JAN-17 00:00:00 30000
21-JAN-17 00:00:00 30000
22-JAN-17 00:00:00 30000
23-JAN-17 00:00:00 29000
24-JAN-17 00:00:00 29000
25-JAN-17 00:00:00 30000
26-JAN-17 00:00:00 30000

START_DATE   END_DATE
18-JAN-17    19-JAN-17
20-JAN-17    22-JAN-17
23-JAN-17    24-JAN-17
25-JAN-17    26-JAN-17

WITH tab AS
  (SELECT rownum rn, a.* FROM test1 a ORDER BY start_date
  )

SELECT *
FROM
  (SELECT x.start_date ,
    CASE
      WHEN x.amount = y.amount
      THEN y.start_date
    END AS end_date
  FROM tab x,
    tab y
  WHERE x.rn+1 = y.rn(+)
  )
WHERE end_date IS NOT NULL.

START_DATE              END_DATE
18-JAN-17 00:00:00 19-JAN-17 00:00:00
20-JAN-17 00:00:00 21-JAN-17 00:00:00 --> THIS ROW IS NOT DESIRED (instead it should be 20-jan-17    22-jan-17)
21-JAN-17 00:00:00 22-JAN-17 00:00:00
23-JAN-17 00:00:00 24-JAN-17 00:00:00
25-JAN-17 00:00:00 26-JAN-17 00:00:00

WITH tab AS
  (SELECT a.*, 
          row_number() over (order by start_date) -
          row_number() over (partition by amount order by start_date) grp
   FROM test1 a 
  )
 select min(start_date), max(start_date), amount from tab
 group  by grp, amount
 order  by 1;

MIN(START_DATE)       MAX(START_DATE)       AMOUNT  
18-JAN-0017 00:00:00  19-JAN-0017 00:00:00  27000   
20-JAN-0017 00:00:00  22-JAN-0017 00:00:00  30000   
23-JAN-0017 00:00:00  24-JAN-0017 00:00:00  29000   
25-JAN-0017 00:00:00  26-JAN-0017 00:00:00  30000