备注:测试数据库版本为MySQL 8.0
如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本
一.需求
把数据编组成大小均匀的桶，每桶都包含预定的元素数。
桶的总数可能不确定，但要确保每桶都包含5个元素。
例如，依据empno值把表emp中的员工分为5个一组，下面给出了结果集:
+------+-------+--------+
| grp | empno | ename |
+------+-------+--------+
| 1 | 7369 | SMITH |
| 1 | 7499 | ALLEN |
| 1 | 7521 | WARD |
| 1 | 7566 | JONES |
| 1 | 7654 | MARTIN |
| 2 | 7698 | BLAKE |
| 2 | 7782 | CLARK |
| 2 | 7788 | SCOTT |
| 2 | 7839 | KING |
| 2 | 7844 | TURNER |
| 3 | 7876 | ADAMS |
| 3 | 7900 | JAMES |
| 3 | 7902 | FORD |
| 3 | 7934 | MILLER |
+------+-------+--------+
二.解决方案
通过窗口函数给行分等级，创建包含5个元素的桶就是很简单的出发操作，对商向上取整即可。
2.1 标量子查询方法
select ceil(rnk/5.0) as grp,
empno, ename
from (
select e.empno, e.ename,
(select count(*) from emp d
where e.empno < d.empno) + 1 as rnk
from emp e
) x
order by grp;
测试记录:
mysql> select ceil(rnk/5.0) as grp,
-> empno, ename
-> from (
-> select e.empno, e.ename,
-> (select count(*) from emp d
-> where e.empno < d.empno) + 1 as rnk
-> from emp e
-> ) x
-> order by grp;
+------+-------+--------+
| grp | empno | ename |
+------+-------+--------+
| 1 | 7844 | TURNER |
| 1 | 7876 | ADAMS |
| 1 | 7900 | JAMES |
| 1 | 7902 | FORD |
| 1 | 7934 | MILLER |
| 2 | 7654 | MARTIN |
| 2 | 7698 | BLAKE |
| 2 | 7782 | CLARK |
| 2 | 7788 | SCOTT |
| 2 | 7839 | KING |
| 3 | 7369 | SMITH |
| 3 | 7499 | ALLEN |
| 3 | 7521 | WARD |
| 3 | 7566 | JONES |
+------+-------+--------+
14 rows in set (0.00 sec)
2.2 MySQL 8.0 窗口函数方法
select ceil(row_number() over w/5.0) grp,
empno,
ename
from emp
window w as ( order by empno );
测试记录:
mysql> select ceil(row_number() over w/5.0) grp,
-> empno,
-> ename
-> from emp
-> window w as ( order by empno );
+------+-------+--------+
| grp | empno | ename |
+------+-------+--------+
| 1 | 7369 | SMITH |
| 1 | 7499 | ALLEN |
| 1 | 7521 | WARD |
| 1 | 7566 | JONES |
| 1 | 7654 | MARTIN |
| 2 | 7698 | BLAKE |
| 2 | 7782 | CLARK |
| 2 | 7788 | SCOTT |
| 2 | 7839 | KING |
| 2 | 7844 | TURNER |
| 3 | 7876 | ADAMS |
| 3 | 7900 | JAMES |
| 3 | 7902 | FORD |
| 3 | 7934 | MILLER |
+------+-------+--------+
14 rows in set (0.00 sec)
155-2731-8020