Db2 例子
case 1: 取前10条不同id记录,如果最后1条记录的ID 还有相同的,都要取出来。
select * from mynumber where id in(select distinct id from mynumber fetch first 10 rows only)
case 2:取前10条记录,如果第10条记录的ID 还有相同的,都要取出来。
select * from mynumber where id in(select id from mynumber fetch first 10 rows only)
case 3:取前10条记录
select id from mynumber fetch first 10 rows only
oralce 例子
1. 最好还是利用分析函数
比如想取出100-150条记录,按照tname排序
select tname,tabtype from (
select tname,tabtype,row_number() over ( order by tname ) rn from tab
)
where rn between 100 and 150;
2. 直接使用rownum 虚列
select tname,tabtype from (
select tname,tabtype,rownum rn from tab where rownum <= 150
)
where rn >= 100;
cas1: 取前10条不同id记录,如果最后1条记录的ID 还有相同的,都要取出来。
select id,name from mynumber where id in (select id from (select distinct id from mynumber) tt where rownum<=10);
case 2:取前10条记录,如果第10条记录的ID 还有相同的,都要取出来。
select * from mynumber where id in(select id from mynumber where rownum <=10);
case 3:取前10条记录
select id,name from mynumber where rownum <=10;
select id,name from (select id,name,rownum rn from mynumber where rownum <= 10 ) where rn >= 5;
Mysql 例子
select id from mytable order by update_date desc limit 0,10