SQL分页语句

Mysql

1
2
3
4
5
6

# 只需在查询语句后面加上 limit 
{sql} LIMIT {offset}, {pageSize}
``` 


## Oracle

SELECT * FROM (SELECT _rn.*, ROWNUM _rnum FROM ({sql}) _rn WHERE ROWNUM<={offset+pageSize}) WHERE _rnum> {offset}

1
2

## Sql Server

SELECT TOP {pageSize} _rn.* FROM (SELECT row_number() over({order by 语句}) AS _rnum,* FROM ({sql}) AS _rf ) AS _rn WHERE _rnum> {offset}

1
2
3
4

## Sql Server 2012
Sql Server 2012 对 sql 分页增加了新的支持

{sql} OFFSET ROW FETCH　NEXT {pageSize} ROWS only

1
2

语法：

ORDER BY order_by_expression
[ COLLATE collation_name ]
[ ASC | DESC ]
[ ,…n ]
[ ]

::=
{
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
[
FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
]
}

1
2

## PostgreSQL

{sql} LIMIT {pageSize} OFFSET {offset}