如何实现按Deptno数值重复返回EMP表对应相同记录？

嘿，这个需求我之前也碰到过，核心思路就是生成一个连续数字序列，然后和你的EMP表做关联，让每条记录根据Deptno的值重复对应次数。下面给你整理了几个主流数据库的实现方案，你可以根据自己用的数据库来选：

MySQL 实现 #

如果你的MySQL版本是8.0及以上（支持递归CTE），可以用下面的写法：

WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < (SELECT MAX(Deptno) FROM EMP)
)
SELECT e.Eid, e.Ename, e.Deptno
FROM EMP e
JOIN nums n ON n.n <= e.Deptno
WHERE e.Eid = 1; -- 替换成你要查询的Eid即可

要是你的MySQL版本低于8.0不支持CTE，也可以手动构造一个数字辅助表来关联：

SELECT e.Eid, e.Ename, e.Deptno
FROM EMP e
JOIN (
    SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
) nums ON nums.n <= e.Deptno
WHERE e.Eid = 1;

Oracle 实现 #

Oracle可以用CONNECT BY快速生成序列，写法更简洁：

SELECT e.Eid, e.Ename, e.Deptno
FROM EMP e
CONNECT BY LEVEL <= e.Deptno
    AND PRIOR e.Eid = e.Eid
    AND PRIOR SYS_GUID() IS NOT NULL -- 防止出现循环关联
WHERE e.Eid = 1;

当然也支持递归CTE的写法，和MySQL类似：

WITH nums(n) AS (
    SELECT 1 FROM DUAL
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < (SELECT MAX(Deptno) FROM EMP)
)
SELECT e.Eid, e.Ename, e.Deptno
FROM EMP e
JOIN nums ON nums.n <= e.Deptno
WHERE e.Eid = 1;

SQL Server 实现 #

SQL Server同样用递归CTE解决，注意如果Deptno的最大值很大，需要开启MAXRECURSION选项：

WITH nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < (SELECT MAX(Deptno) FROM EMP)
)
SELECT e.Eid, e.Ename, e.Deptno
FROM EMP e
JOIN nums n ON n.n <= e.Deptno
WHERE e.Eid = 1
OPTION (MAXRECURSION 0);

原理说明 #

所有方案的核心逻辑都是：先生成一个从1开始的连续数字序列，然后让EMP表的每条记录，和序列中小于等于自身Deptno值的数字做关联，这样每条记录就会被重复Deptno次，完全符合你的需求~

内容的提问来源于stack exchange，提问作者z_1_p