WITH t AS ( SELECT 1 id,1 value FROM dual UNION SELECT 2 id,2 value FROM dual UNION SELECT 3 id,1 value FROM dual UNION SELECT 4 id,2 value FROM dual UNION SELECT 5 id,1 value FROM dual UNION SELECT 6 id,2 value FROM dual UNION SELECT 7 id,2 value FROM dual UNION SELECT 8 id,1 value FROM dual UNION SELECT 9 id,1 value FROM dual UNION SELECT 10 id,1 value FROM dual ) select max(le) from (SELECT t.*,level le FROM t where value=1 connect by (prior id+1=id and prior value=1 and value=1))
那如果是 SELECT 111 id,'a' value FROM dual UNION SELECT 32 id,'a' value FROM dual UNION SELECT 33 id,'a' value FROM dual UNION SELECT 34 id,2 value FROM dual UNION SELECT 45 id,1 value FROM dual UNION SELECT 62 id,2 value FROM dual UNION SELECT 75 id,2 value FROM dual UNION SELECT 82 id,'a' value FROM dual UNION SELECT 92 id,1 value FROM dual UNION SELECT 10 id,1 value FROM dual ) 怎么找‘a’的最大记录
那就这样呗..... WITH t AS ( SELECT 1 id,1 value FROM dual UNION SELECT 2 id,2 value FROM dual UNION SELECT 3 id,1 value FROM dual UNION SELECT 4 id,2 value FROM dual UNION SELECT 5 id,1 value FROM dual UNION SELECT 6 id,2 value FROM dual UNION SELECT 7 id,2 value FROM dual UNION SELECT 34 id,2 value FROM dual UNION SELECT 9 id,2 value FROM dual UNION SELECT 13 id,1 value FROM dual ) select max(le) from (SELECT t2.*,level le FROM (select t.*,rownum rid from t) t2 where value=2 connect by (prior rid+1=rid and prior value=2 and value=2))
SELECT 1 id,1 value FROM dual
UNION
SELECT 2 id,2 value FROM dual
UNION
SELECT 3 id,1 value FROM dual
UNION
SELECT 4 id,2 value FROM dual
UNION
SELECT 5 id,1 value FROM dual
UNION
SELECT 6 id,2 value FROM dual
UNION
SELECT 7 id,2 value FROM dual
UNION
SELECT 8 id,1 value FROM dual
UNION
SELECT 9 id,1 value FROM dual
UNION
SELECT 10 id,1 value FROM dual
)
select max(le) from (SELECT t.*,level le FROM t where value=1 connect by (prior id+1=id and prior value=1 and value=1))
SELECT 111 id,'a' value FROM dual
UNION
SELECT 32 id,'a' value FROM dual
UNION
SELECT 33 id,'a' value FROM dual
UNION
SELECT 34 id,2 value FROM dual
UNION
SELECT 45 id,1 value FROM dual
UNION
SELECT 62 id,2 value FROM dual
UNION
SELECT 75 id,2 value FROM dual
UNION
SELECT 82 id,'a' value FROM dual
UNION
SELECT 92 id,1 value FROM dual
UNION
SELECT 10 id,1 value FROM dual
)
怎么找‘a’的最大记录
那就这样呗.....
WITH t AS (
SELECT 1 id,1 value FROM dual
UNION
SELECT 2 id,2 value FROM dual
UNION
SELECT 3 id,1 value FROM dual
UNION
SELECT 4 id,2 value FROM dual
UNION
SELECT 5 id,1 value FROM dual
UNION
SELECT 6 id,2 value FROM dual
UNION
SELECT 7 id,2 value FROM dual
UNION
SELECT 34 id,2 value FROM dual
UNION
SELECT 9 id,2 value FROM dual
UNION
SELECT 13 id,1 value FROM dual
)
select max(le) from
(SELECT t2.*,level le FROM
(select t.*,rownum rid from t) t2
where value=2 connect by (prior rid+1=rid and prior value=2 and value=2))