MySQL 创建固定大小的桶

迷南。 2022-10-26 03:47 78阅读 0赞

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

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][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)

[scott_sql]: https://www.jianshu.com/p/532fe68924cb