在Oracle PL/SQL中复制SAS DO循环

Question

我正在尝试将DO循环从SAS复制到Oracle PL/SQL中。基本上，此 DO 循环遍历表并为单个员工创建多行。我对PL/SQL中的循环不是很熟悉，因此非常感谢任何帮助。除了创建大量表然后组合它们之外，我想不出任何方法来重新创建它。最后我会详细解释我的想法;现在，请参阅数据示例以及 SAS DO 循环正在执行的操作。

HIST_EMPLOYEE表：

+----------+----------+--------+
| EMPLOYEE | START_YR | END_YR |
+----------+----------+--------+
| JOHN     |     2013 |   2014 |
| WILL     |     2012 |   2016 |
| MARK     |     2012 |   2012 |
+----------+----------+--------+

SAS 中的 DO 循环：

DATA HIST_EMPLOYEE_NEW;
SET HIST_EMPLOYEE;
DO YR = START_YR TO END_YR;
OUTPUT;
END;
RUN;

输出：

+----------+----------+--------+------+
| EMPLOYEE | START_YR | END_YR |  YR  |
+----------+----------+--------+------+
| JOHN     |     2013 |   2014 | 2013 |
| JOHN     |     2013 |   2014 | 2014 |
| WILL     |     2012 |   2016 | 2012 |
| WILL     |     2012 |   2016 | 2013 |
| WILL     |     2012 |   2016 | 2014 |
| WILL     |     2012 |   2016 | 2015 |
| WILL     |     2012 |   2016 | 2016 |
| MARK     |     2012 |   2012 | 2012 |
+----------+----------+--------+------+

我解决这个问题的方法(这在任何方面都效率不高(是在i is from 0 to 10END_YR < START_YR + i上创建过滤的表，然后创建YR列，然后合并所有表。我可以进一步讨论这个问题，但我已经觉得这是不好的做事方式。

Answer 1

这是一种方法。 "with"子句称为公用表表达式 (CTE(，它只是为每个条目设置具有唯一 ID 的测试数据。

查询使用 CONNECT BY，可以将其视为返回的每一行的循环机制。 它带有一个名为"level"的变量，该变量每次迭代递增一次(从 1 开始(。 定义每行"循环"的次数是表达式 (end_yr-start_yr+1(。对于 JOHN，我们需要循环 2 次，因为我们需要 2 行，WILL 5 行，依此类推。 "PRIOR ID"子句有助于处理每个原始行的多行。

with hist_employee(id, employee, start_yr, end_yr) as (
select 1, 'JOHN', 2013, 2014 from dual union all
select 2, 'WILL', 2012, 2016 from dual union all
select 3, 'MARK', 2012, 2012 from dual
)
select employee, start_yr, end_yr, (start_yr + (level-1)) as YR
from hist_employee
connect by level <= end_yr-start_yr+1
and prior id = id
and prior sys_guid() is not null
order by id;

EMPLOYEE   START_YR     END_YR         YR
-------- ---------- ---------- ----------
JOHN           2013       2014       2013
JOHN           2013       2014       2014
WILL           2012       2016       2012
WILL           2012       2016       2013
WILL           2012       2016       2014
WILL           2012       2016       2015
WILL           2012       2016       2016
MARK           2012       2012       2012
8 rows selected.

Answer 2

只需创建一个具有 YR 值的表，范围从某个最小值到最大值并在此连接。

所以像这样：

with years as (
select 2012 + rownum - 1 as YR
from dual
connect by rownum < (2016 - 2012)
)
select a.*,b.YR
from HIST_EMPLOYEE a
inner join years b
on a.start_yr <= b.yr and b.yr <= a.end_yr
;

只需更改下限 (2012( 和上限 (2016( 即可更改要生成的年数。

请参阅此问题： 如何在 Oracle 中填充日历表？