備注:測(cè)試數(shù)據(jù)庫版本為MySQL 8.0
如需要scott用戶下建表及錄入數(shù)據(jù)語句画机，可參考:
scott建表及錄入數(shù)據(jù)sql腳本
一.需求
把數(shù)據(jù)變?yōu)楣潭〝?shù)目的桶讨永。
例如，把表EMP中的員工編組為4桶膳算。
其結(jié)果集應(yīng)該如下所示:
+------+-------+--------+
| 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開始支持窗口函數(shù)驶乾，ntile直接提供了創(chuàng)建"桶"的函數(shù)办绝，這個(gè)就很簡(jiǎn)單了冒黑。
如果不使用ntile函數(shù)试伙，也可以給每行分等級(jí)嘁信，然后在表達(dá)式中使用等級(jí)對(duì)n的模(n是要?jiǎng)?chuàng)建的同屬)，以確定該行落入哪個(gè)桶內(nèi)疏叨。
2.1 臨時(shí)表方法
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;
測(cè)試記錄:
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 窗口函數(shù)方法
select mod(row_number() over w, 4) + 1 as grp,
empno,
ename
from emp
window w as (order by empno)
order by 1;
測(cè)試記錄:
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函數(shù)方法
select ntile(4) over w as 'grp',
empno,
ename
from emp
window w as (order by empno)
;
測(cè)試記錄:
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)
