plsql 如何动态生成case when，当临时表的数据变了， case when如何随着动态改变

Question

with temp as (
select * from t5_prod_cfm
)

date	rank
20110930	1
20211008	2
20211014	3
20211021	4

select case
when
t.workdate >= (select t1.date from temp t1 where rank='1') and <=(select t1.date from temp t1 where rank='2) then
'1'
when
t.workdate >= (select t1.date from temp t1 where rank='2') and <=(select t1.date from temp t1 where rank='3') then
'2'
when
t.workdate >= (select t1.date from temp t1 where rank='3') and <=(select t1.date from temp t1 where rank='4') then
'3'
when
t.workdate >= (select t1.date from temp t1 where rank='4') then
'4'
end cycle_num ,
prod_code
from t5_prod_in t
结果：

cycle	prod_code
1	s061101
2	s061101
3	s061101
4	s061101

---

问题 如果 temp变成了，sql的 case when如何动态的改变成下面的 示例

date	rank
20110930	1
20211008	2
20211014	3
20211021	4
20211028	5

sql示例：
select case
when
t.workdate >= (select t1.date from temp t1 where rank='1') and <=(select t1.date from temp t1 where rank='2) then
'1'
when
t.workdate >= (select t1.date from temp t1 where rank='2') and <=(select t1.date from temp t1 where rank='3') then
'2'
when
t.workdate >= (select t1.date from temp t1 where rank='3') and <=(select t1.date from temp t1 where rank='4') then
'3'
when
t.workdate >= (select t1.date from temp t1 where rank='4') and <=(select t1.date from temp t1 where rank='4') then
'4'
t.workdate >= (select t1.date from temp t1 where rank='5')
'5'
end cycle_num
from t5_prod_in t
结果：

cycle	prod_code
1	s061101
2	s061101
3	s061101
4	s061101
5	s061101

Answer 1

select 
rank as cycle_num ,
prod_code
from t5_prod_in t 
left join 
(
select date ,lead(date,1,'20991231') over() as date2,rank from temp order by rank asc
)t2
on t.workdate >= t2.date and t.workdate <= t2.date2;
-- 左右都是大于等于??? 会有重复数据的可能