Oracle:分割字符串 取TOP N条记录

oracle数据库，表数据如下:

ids                           id

3,4,5                        7

13,14,15,16             17

想要使用sql,实现将ids按照逗号分割后查询到如下记录：

ids                           id

3                              7

4                              7

5                              7

13                            17

14                            17

15                            17

16                            17 

在Oracle10g以上版本中，可以使用regexp_substr实现。具体sql语句如下：

select id,ids from(

  select regexp_substr(ids, '[^,]+',1,lvl) ids, lvl, id from tbl,

  (select level lvl from dual connect by

  level < =(select max(length(regexp_replace(ids,'[^,]','')))+1 max_tokens from tbl))

) where ids is not null order by lvl

 

10g取TOP N条记录

方法1：利用ROW_NUMBER函数

取出前10条记录：

SELECT NO FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY NO) RNO, NO FROM lz_countriescity
)WHERE RNO <= 10  ORDER BY NO ;

取出中间10条记录：

SELECT NO FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY NO) RNO, NO FROM lz_countriescity
) WHERE RNO BETWEEN 10 AND 20 ORDER BY NO;

取出前10条记录：

SELECT NO FROM (
    SELECT NO FROM lz_countriescity  ORDER BY NO
)WHERE ROWNUM <= 10  ORDER BY NO ;


取出中间10条记录：

SELECT NO  FROM (
    SELECT NO, ROWNUM RNO FROM (
        SELECT NO FROM lz_countriescity  ORDER BY NO
    )
) WHERE RNO BETWEEN 5 AND 15  ORDER BY NO;