备注:测试数据库版本为MySQL 8.0
如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本
一.需求
把数据变为固定数目的桶。
例如，把表EMP中的员工编组为4桶。
其结果集应该如下所示:
+------+-------+--------+
| grp | empno | ename |
+------+-------+--------+
| 1 | 7566 | JONES |
| 1 | 7788 | SCOTT |
| 1 | 7900 | JAMES |
| 2 | 7369 | SMITH |
| 2 | 7654 | MARTIN |
| 2 | 7839 | KING |
| 2 | 7902 | FORD |
| 3 | 7499 | ALLEN |
| 3 | 7698 | BLAKE |
| 3 | 7844 | TURNER |
| 3 | 7934 | MILLER |
| 4 | 7521 | WARD |
| 4 | 7782 | CLARK |
| 4 | 7876 | ADAMS |
+------+-------+--------+
二.解决方案
MySQL 8.0开始支持窗口函数，ntile直接提供了创建"桶"的函数，这个就很简单了。
如果不使用ntile函数，也可以给每行分等级，然后在表达式中使用等级对n的模(n是要创建的同属)，以确定该行落入哪个桶内。
2.1 临时表方法
select mod(count(*),4) +1 as grp,
e.empno,
e.ename
from emp e, emp d
where e.empno >= d.empno
group by e.empno,e.ename
order by 1;
测试记录:
mysql> select mod(count(*),4) +1 as grp,
-> e.empno,
-> e.ename
-> from emp e, emp d
-> where e.empno >= d.empno
-> group by e.empno,e.ename
-> order by 1;
+------+-------+--------+
| grp | empno | ename |
+------+-------+--------+
| 1 | 7566 | JONES |
| 1 | 7788 | SCOTT |
| 1 | 7900 | JAMES |
| 2 | 7369 | SMITH |
| 2 | 7654 | MARTIN |
| 2 | 7839 | KING |
| 2 | 7902 | FORD |
| 3 | 7499 | ALLEN |
| 3 | 7698 | BLAKE |
| 3 | 7844 | TURNER |
| 3 | 7934 | MILLER |
| 4 | 7521 | WARD |
| 4 | 7782 | CLARK |
| 4 | 7876 | ADAMS |
+------+-------+--------+
14 rows in set (0.00 sec)
2.2 MySQL 8.0 窗口函数方法
select mod(row_number() over w, 4) + 1 as grp,
empno,
ename
from emp
window w as (order by empno)
order by 1;
测试记录:
mysql> select mod(row_number() over w, 4) + 1 as grp,
-> empno,
-> ename
-> from emp
-> window w as (order by empno)
-> order by 1;
+------+-------+--------+
| grp | empno | ename |
+------+-------+--------+
| 1 | 7566 | JONES |
| 1 | 7788 | SCOTT |
| 1 | 7900 | JAMES |
| 2 | 7369 | SMITH |
| 2 | 7654 | MARTIN |
| 2 | 7839 | KING |
| 2 | 7902 | FORD |
| 3 | 7499 | ALLEN |
| 3 | 7698 | BLAKE |
| 3 | 7844 | TURNER |
| 3 | 7934 | MILLER |
| 4 | 7521 | WARD |
| 4 | 7782 | CLARK |
| 4 | 7876 | ADAMS |
+------+-------+--------+
14 rows in set (0.00 sec)
2.3 MySQL 8.0 ntile函数方法
select ntile(4) over w as 'grp',
empno,
ename
from emp
window w as (order by empno)
;
测试记录:
mysql> select ntile(4) over w as '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 |
| 2 | 7654 | MARTIN |
| 2 | 7698 | BLAKE |
| 2 | 7782 | CLARK |
| 2 | 7788 | SCOTT |
| 3 | 7839 | KING |
| 3 | 7844 | TURNER |
| 3 | 7876 | ADAMS |
| 4 | 7900 | JAMES |
| 4 | 7902 | FORD |
| 4 | 7934 | MILLER |
+------+-------+--------+
14 rows in set (0.00 sec)
155-2731-8020