日期:2014-05-17  浏览次数:21134 次

问个sql语句
现在数据库中的某个字段的数据是这样的
GENERATE
SUBMIT
APPROVE
APPROVAL_REJECT
SUBMIT
APPROVE
APPROVE
APPROVAL_REJECT
SUBMIT
APPROVE
APPROVE
APPROVE
我如何从最后一次SUBMIT开始取数据呢?(如上面,会取出最后四行)


------解决方案--------------------
测试数据:
SQL code

CREATE  TABLE T29
(
    ID      VARCHAR2(20),
    des     VARCHAR2(20)
);

INSERT INTO T29 VALUES('1', 'GENERATE');
INSERT INTO T29 VALUES('2', 'SUBMIT');
INSERT INTO T29 VALUES('3', 'APPROVE');
INSERT INTO T29 VALUES('4', 'APPROVAL_REJECT');
INSERT INTO T29 VALUES('5', 'SUBMIT');
INSERT INTO T29 VALUES('6', 'APPROVE');
INSERT INTO T29 VALUES('7', 'APPROVE');
INSERT INTO T29 VALUES('8', 'APPROVAL_REJECT');
INSERT INTO T29 VALUES('9', 'SUBMIT');
INSERT INTO T29 VALUES('10', 'APPROVE');
INSERT INTO T29 VALUES('11', 'APPROVE');
INSERT INTO T29 VALUES('12', 'APPROVE');

------解决方案--------------------
SQL code
with t as (
select 'GENERATE'  a     from dual  union all 
select 'SUBMIT'          from dual  union all 
select 'APPROVE'         from dual  union all 
select 'APPROVAL_REJECT' from dual  union all 
select 'SUBMIT'          from dual  union all 
select 'APPROVE'         from dual  union all 
select 'APPROVE'         from dual  union all 
select 'APPROVAL_REJECT' from dual  union all 
select 'SUBMIT'          from dual  union all 
select 'APPROVE'         from dual  union all 
select 'APPROVE'         from dual  union all 
select 'APPROVE'         from dual            

) select a from  
(select  a,row_number() over (order by rownum) rn from t  ) c 
where c.rn>=
( select  max(rn) from (
select a,row_number() over (order by rownum) rn from t )  
where a='SUBMIT' )