oracle学习笔记之asktom

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Create table bills ( Contract_id Number(10),Bill_id number(10),Bill_status
varchar(10),Bill_End_Date Date )

Insert into bills values(1,11111,'paid',to_date('31-12-2003','dd-mm-yyyy'));
Insert into bills values(1,1111,'paid',to_date('31-12-2003','dd-mm-yyyy'));
Insert into bills values(1,2222,'paid',to_date('31-01-2004','dd-mm-yyyy'));
Insert into bills values(1,3333,'paid',to_date('29-02-2004','dd-mm-yyyy'));
Insert into bills values(1,4444,'closed',to_date('31-03-2004','dd-mm-yyyy'));
Insert into bills values(1,5555,'paid',to_date('30-04-2004','dd-mm-yyyy'));
Insert into bills values(1,6666,'closed',to_date('31-05-2004','dd-mm-yyyy'));
Insert into bills values(1,7777,'paid',to_date('30-06-2004','dd-mm-yyyy'));
Insert into bills values(1,8888,'paid',to_date('31-07-2004','dd-mm-yyyy'));
Insert into bills values(1,9999,'paid',to_date('31-08-2004','dd-mm-yyyy'));
Insert into bills values(1,1010,'closed',to_date('30-09-2004','dd-mm-yyyy'));
Insert into bills values(1,111,'closed',to_date('31-10-2004','dd-mm-yyyy'));
Insert into bills values(1,1212,'closed',to_date('30-11-2004','dd-mm-yyyy'));
Insert into bills values(1,1313,'Open',to_date('31-12-2004','dd-mm-yyyy'));
Insert into bills values(1,1112,'paid',to_date('31-12-2003','dd-mm-yyyy'));
Insert into bills values(1,2221,'paid',to_date('31-01-2004','dd-mm-yyyy'));
Insert into bills values(1,3331,'paid',to_date('29-02-2004','dd-mm-yyyy'));
Insert into bills values(1,4441,'closed',to_date('31-03-2004','dd-mm-yyyy'));
Insert into bills values(1,5551,'paid' ,to_date('30-04-2004','dd-mm-yyyy'));
Insert into bills values(1,6661,'closed',to_date('31-05-2004','dd-mm-yyyy'));
Insert into bills values(1,7771,'paid',to_date('30-06-2004','dd-mm-yyyy'));
Insert into bills values(1,8881,'paid',to_date('31-07-2004','dd-mm-yyyy'));
Insert into bills values(1,9991,'paid',to_date('31-08-2004','dd-mm-yyyy'));
Insert into bills values(1,1013,'closed',to_date('30-09-2004','dd-mm-yyyy'));
Insert into bills values(1,1113,'closed',to_date('31-10-2004','dd-mm-yyyy'));
Insert into bills values(1,1214,'closed',to_date('30-11-2004','dd-mm-yyyy'));
Insert into bills values(1,1315,'Open',to_date('31-12-2004','dd-mm-yyyy'));

I will get all these Bills ( 4444,6666,1010,1111,1212,4441,6661,1013,1113,1214)
These are all nonpaid bills and generated .

I Want to get only these bills ( 4444,6666,4441,6661) .Our table of bills
contains million of generated bills mixed of Paid and Closed 

I want an SQL that will scan bill table and get me only bills that are closed
and between two paid bills ONLY without  using
Bill ID only Status and Bill End Date can be used

select * from bills
where bill_status = 'closed'
and bill_end_date between (select min(bill_end_date) from bills where bill_status = 'paid')
                      and (select max(bill_end_date) from bills where bill_status = 'paid')

 
CONTRACT_ID    BILL_ID BILL_STATU BILL_END_
----------- ---------- ---------- ---------
          1       4444 closed     31-MAR-04
          1       6666 closed     31-MAY-04
          1       4441 closed     31-MAR-04
          1       6661 closed     31-MAY-04
 
You'd most likely want to have an index available on
bills(bill_status,bill_end_date);

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Create table t1( cn number(8))

insert into t1 values ( 1 );
insert into t1 values ( 2 );
insert into t1 values ( 4 );
insert into t1 values ( 3 );
insert into t1 values ( 9 );
insert into t1 values ( 11 );
insert into t1 values ( 13 );
insert into t1 values ( 14 );
insert into t1 values ( 15 );

select * from t1;

cn
---------------------------
1
2
4
3
9
11
13
14
15

select min(cn), max(cn)
  from (select cn, max(grp) over(order by cn) grp
          from (select cn,
                       case
                         when nvl(lag(cn) over(order by cn), cn) <> cn - 1 then
                          cn
                       end grp
                  from t1))
 group by grp;

min(cn) max(cn)
---------------------------
1       4
9       9
11      11
13      15