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

时间:2020-12-27 10:48:47

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;