Oracle/SQL Server/MySql/PostgreSQL的分页语句

Oracle

newsql = "SELECT * FROM ( SELECT A.*, ROWNUM RN FROM 表名 A ORDER BY id) WHERE RN BETWEEN " + ((页数-1)*每页数量+1) + " and " + 页数*每页数量;

SELECT * FROM ( SELECT A.*, ROWNUM RN FROM tablename A ORDER BY id) WHERE RN BETWEEN  ((1-1)*20+1)  and  1*20;

SQL Server

newsql = "SELECT * FROM ( SELECT A.*, ROW_NUMBER() OVER(Order by " + 排序列 + ") as RN FROM (" + 表名 + ") A) as b WHERE b.RN BETWEEN "+ ((页数-1)*每页数量+1) + " and " + 页数*每页数量 ;

SELECT * FROM ( SELECT A.*, ROW_NUMBER() OVER(Order by id) as RN FROM tablename A) as b WHERE b.RN BETWEEN ((1-1)*20+1)  and  1*20 ;

MySql

@Select("SELECT * FROM 表名 ORDER BY 排序字段 DESC LIMIT #{页数},#{每页数量}")

SELECT * FROM changetable ORDER BY chan_application_time DESC LIMIT 1,20;

PostgreSql

sql.append("SELECT * from " + 表名 + " order by id desc limit "+每页数量+" offset " + ((页数-1)* 每页数量));

SELECT *  from tablename order by id desc limit 20 offset 0;