各数据库分页SQL

用户信息列表，按用户昵称排序

【SQLServer】

SELECT TOP 10 * FROM t_front_um_user_base WHERE c_user_id NOT IN (SELECT TOP 20 c_user_id FROM t_front_um_user_base ORDER BY t_user_nick_name ASC) ORDER BY t_user_nick_name ASC

 

【PostgreSQL】

SELECT * FROM t_front_um_user_base ORDER BY t_user_nick_name ASC LIMIT 10 OFFSET 20

 

【Oracle】

SELECT * FROM (SELECT temp.*, ROWNUM rn FROM (SELECT * FROM t_front_um_user_base ORDER BY t_user_nick_name ASC) temp WHERE ROWNUM<30) WHERE rn>=20

 

【DB2】

SELECT * FROM (*,rownumber() over(ORDER BY t_user_nick_name ASC) AS rn FROM t_front_um_user_base) AS temp WHERE temp.rn BETWEEN 20 AND 30

 

【MySQL】

SELECT * FROM t_front_um_user_base ORDER BY t_user_nick_name ASC LIMIT 20,10