极客教程SQL 创建固定大小的数据桶，你想把数据放入若干个大小固定的桶（bucket）里，每个桶的元素个数是事先定好的。桶的个数可能是不确定的，但你希望确保每个桶有 5 个元素。例如，你希望基于 EMPNO 值为 EMP 表里的员工分组，一组最多 5 人，结果集显示如下。
SQL 创建固定大小的数据桶 问题描述
你想把数据放入若干个大小固定的桶（bucket）里，每个桶的元素个数是事先定好的。桶的个数可能是不确定的，但你希望确保每个桶有 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
SQL 创建固定大小的数据桶 解决方案
对于提供了排名函数的数据库而言，很容易解决本问题。在为每一行数据生成了一个序号之后，创建含有 5 个元素的桶的问题就变成了简单的除法问题。做过除法之后，我们只须针对商值向上取整即可。
DB2、Oracle 和 SQL Server
使用窗口函数 ROW_NUMBER OVER，基于 EMPNO 为每个员工生成一个序号。然后用该序号除以 5 即可实现分组（SQL Server 需要调用 CEILING 函数，而不是 CEIL 函数）。
1 select ceil(row_number()over(order by empno)/5.0) grp,
2 empno,
3 ename
4 from emp
PostgreSQL 和 MySQL
使用标量子查询为每个 EMPNO 生成一个序号，然后用该序号除以 5 以创建分组。
1 select ceil(rnk/5.0) as grp,
2 empno, ename
3 from (
4 select e.empno, e.ename,
5 (select count(*) from emp d
6 where e.empno < d.empno)+1 as rnk
7 from emp e
8 ) x
9 order by grp
SQL 创建固定大小的数据桶 扩展知识
DB2、Oracle 和 SQL Server
按照 EMPNO 排序后，窗口函数 ROW_NUMBER OVER 为每一行分配了一个排名或“行号”。
select row_number()over(order by empno) rn,
empno,
ename
from emp
RN EMPNO ENAME
-- ---------- ----------
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
5 7654 MARTIN
6 7698 BLAKE
7 7782 CLARK
8 7788 SCOTT
9 7839 KING
10 7844 TURNER
11 7876 ADAMS
12 7900 JAMES
13 7902 FORD
14 7934 MILLER
将 ROW_NUMBER OVER 函数的返回值除以 5 之后，下一步要调用函数 CEIL（或 CEILING）。理论上，除以 5，会把每 5 行数据划入一组。也就是说，会有 5 个值小于或等于 1，同时会有 5 个值大于 1 但小于或等于 2，剩下的一组则大于 2 但小于或等于 3（这一组由最后的 4 行数据构成，因为 EMP 表一共包含 14 行数据，并非 5 的整数倍）。
CEIL 函数将返回大于参数值的最小的整数。我们需要这样做，因为每一组的编号都是一个整数值。除法计算的结果以及 CEIL 函数的返回值显示如下。你可以按照从左到右、从 RN 到 DIVISION 再到 GRP 的顺序试着做一下运算。
select row_number()over(order by empno) rn,
row_number()over(order by empno)/5.0 division,
ceil(row_number()over(order by empno)/5.0) grp,
empno,
ename
from emp
RN DIVISION GRP EMPNO ENAME
-- ---------- --- ----- ----------
1 .2 1 7369 SMITH
2 .4 1 7499 ALLEN
3 .6 1 7521 WARD
4 .8 1 7566 JONES
5 1 1 7654 MARTIN
6 1.2 2 7698 BLAKE
7 1.4 2 7782 CLARK
8 1.6 2 7788 SCOTT
9 1.8 2 7839 KING
10 2 2 7844 TURNER
11 2.2 3 7876 ADAMS
12 2.4 3 7900 JAMES
13 2.6 3 7902 FORD
14 2.8 3 7934 MILLER
PostgreSQL 和 MySQL
首先借助标量子查询基于 EMPNO 为每一行生成一个序号。
select (select count(*) from emp d
where e.empno < d.empno)+1 as rnk,
e.empno, e.ename
from emp e
order by 1
RNK EMPNO ENAME
--- ---------- ----------
1 7934 MILLER
2 7902 FORD
3 7900 JAMES
4 7876 ADAMS
5 7844 TURNER
6 7839 KING
7 7788 SCOTT
8 7782 CLARK
9 7698 BLAKE
10 7654 MARTIN
11 7566 JONES
12 7521 WARD
13 7499 ALLEN
14 7369 SMITH
上述 RNK 值除以 5 之后，下一步要调用函数 CEIL。理论上，除以 5 会把每 5 行数据划入一组。也就是说，会有 5 个值小于或等于 1，同时会有 5 个值大于 1 但小于或等于 2，剩下的一组则大于 2 但小于或等于 3（这一组由最后的 4 行数据构成，因为 EMP 表一共包含 14 行数据，并非 5 的整数倍）。除法计算的结果以及 CEIL 函数的返回值显示如下。你可以按照从左到右、从 RNK 到 DIVISION 再到 GRP 的顺序试着做一下运算。
select rnk,
rnk/5.0 as division,
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 1
RNK DIVISION GRP EMPNO ENAME
--- ---------- --- ----- -------
1 .2 1 7934 MILLER
2 .4 1 7902 FORD
3 .6 1 7900 JAMES
4 .8 1 7876 ADAMS
5 1 1 7844 TURNER
6 1.2 2 7839 KING
7 1.4 2 7788 SCOTT
8 1.6 2 7782 CLARK
9 1.8 2 7698 BLAKE
10 2 2 7654 MARTIN
11 2.2 3 7566 JONES