MySQL 创建固定大小的桶

备注:测试数据库版本为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)