如何在oraclesql中实现for循环

Question

我是oracle-sql的新手，并使用此子查询来查找医院中的患者数量。

Select wtt.wrt_ip_num, max(wtt.wrt_sl_num) wrt_sl_num 
FROM W_TRANSFER_TXN wtt
where /*wtt.wrt_ip_num='IP/20/034619' and*/ trunc(wtt.wrt_in_dt)<=TO_DATE('29-Sep-2020','DD-MON-YYYY')
and (wtt.wrt_out_dt is null or trunc(wtt.wrt_out_dt)>=TO_DATE('29-Sep-2020','DD-MON-YYYY'))
group by wtt.wrt_ip_num);

这给出了1天的值。我想在where子句所在的地方运行一个for循环，并想循环该值10天，这样它就可以给出10天的输出。这可能吗。如果是，怎么办？

Answer 1

当您认为在数据库中需要FOR循环时，许多时候您需要生成要循环的值，然后将这些值连接到表中。Oracle将为您处理循环。

生成数据有很多技巧，最简单的是从DUAL中选择，并使用UNION ALL构建一小组数据：

select wtt.wrt_ip_num, max(wtt.wrt_sl_num) wrt_sl_num 
from w_transfer_txn wtt
join
(
select date '2020-09-20' the_date from dual union all
select date '2020-09-21' the_date from dual union all
select date '2020-09-22' the_date from dual union all
select date '2020-09-23' the_date from dual union all
select date '2020-09-24' the_date from dual union all
select date '2020-09-25' the_date from dual union all
select date '2020-09-26' the_date from dual union all
select date '2020-09-27' the_date from dual union all
select date '2020-09-28' the_date from dual union all
select date '2020-09-29' the_date from dual
) dates
on trunc(wtt.wrt_in_dt) = dates.the_date
group by wtt.wrt_ip_num;

生成10个日期的一种更短但更神秘的方法是使用这样的分层查询：

select wtt.wrt_ip_num, max(wtt.wrt_sl_num) wrt_sl_num 
from w_transfer_txn wtt
join
(
select date '2020-09-20' + level - 1 the_date
from dual
connect by level <= 10
) dates
on trunc(wtt.wrt_in_dt) = dates.the_date
group by wtt.wrt_ip_num;