Oracle，mysql，sqlserver三种数据库查询表获取表数据的前100条数据与排序时获取指定的条数.

一、oracle

获取表的前100条数据.
(从1行开始取100行数据,第一行到第100行数据)

select * from t_stu_copy  where rownum<=100;

补充:先降序排序再获取第101条到第200条之间的所有记录

select * from t_stu_copy order by stuid desc where rownum between 100 and 200 ;---错误

select * from t_stu_copy where rownum between 100 and 200 order by stuid desc  ;---错误

SELECT * FROM(SELECT ROWNUM AS rowno,t.* FROM t_stu_copy t WHERE ROWNUM <= 200 ORDER BY t.stuid ) a WHERE a.rowno > 100;---正确

select  * from t_stu_copy where stuid between 101 and 200;;---正确

二、mysql

获取表的前100条数据.
(从1行开始取100行数据,第一行到第100行数据)

select * from t_stu_copy limit 0,100;

补充:先降序排序再获取第101条到第200条之间的所有记录
(从101行开始取100行数据,第101行到第200行数据)

select * from t_stu_copy order by stuid  limit 100,100;

select  * from t_stu_copy where stuid between 101 and 200;

三、sqlserver

获取表的前100条数据.

select top 100 * from t_stu_copy ;

补充:先降序排序再获取第101条到第200条之间的所有记录
(三种方法,不过方法a与b得到的结果是将第101条到第200条倒过来显示罢了)
a.

select top 100 * from (select top 200 * from t_stu order by stuid) a order by stuid desc;

b.

select top m * into 临时表(或表变量) from tablename order by columnname  set rowcount n select * from 表变量 order by columnname desc.
 
select top 200 * into xxx from t_stu order by stuid set ROWCOUNT 100 select * from xxx order by stuid desc;

xxx表示临时表变量.

c.

select * from t_stu where stuid between 101 and 200.