mysql给一个结果集分页_MySQL 按结果集分页

备注:测试数据库版本为MySQL 8.0

一.需求

给结果集分页或滚动显示整个结果集

例如，先返回表emp中的前5行工资，然后是下5行，依此类推。目标是允许用户一次能够查看5个记录，每次单击"下一页"按钮都会向前桂东5个记录。

二.解决方案

在SQL中，由于没有“第一个”、“最后一个”及“下一个”的概念，所以必须对要处理的行按某种方式排序，只有如此，才会准确地返回一定范围内记录。

-- MySQL limit offset解决方案

-- offset表示跳过几行

select sal

from emp

order by sal

limit 5 offset 0

;

select sal

from emp

order by sal

limit 5 offset 5

;

-- MySQL 8.0 分析函数解决方案

select sal

from (

select row_number() over (order by sal) as rn,

sal

from emp

) x

where rn between 1 and 5;

select sal

from (

select row_number() over (order by sal) as rn,

sal

from emp

) x

where rn between 6 and 10;

测试记录

mysql> select sal

-> from emp

-> order by sal

-> limit 5 offset 0

-> ;

+---------+

| sal |

+---------+

| 800.00 |

| 950.00 |

| 1100.00 |

| 1250.00 |

| 1250.00 |

+---------+

5 rows in set (0.00 sec)

mysql> select sal

-> from emp

-> order by sal

-> limit 5 offset 5

-> ;

+---------+

| sal |

+---------+

| 1300.00 |

| 1500.00 |

| 1600.00 |

| 2450.00 |

| 2850.00 |

+---------+

5 rows in set (0.00 sec)

mysql>

mysql> select sal

-> from (

-> select row_number() over (order by sal) as rn,

-> sal

-> from emp

-> ) x

-> where rn between 1 and 5;

+---------+

| sal |

+---------+

| 800.00 |

| 950.00 |

| 1100.00 |

| 1250.00 |

| 1250.00 |

+---------+

5 rows in set (0.00 sec)

mysql> select sal

-> from (

-> select row_number() over (order by sal) as rn,

-> sal

-> from emp

-> ) x

-> where rn between 6 and 10;

+---------+

| sal |

+---------+

| 1300.00 |

| 1500.00 |

| 1600.00 |

| 2450.00 |

| 2850.00 |

+---------+

5 rows in set (0.00 sec)