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

with temp as (
select * from t5_prod_cfm
)

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
结果：

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

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
结果：

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;
-- 左右都是大于等于??? 会有重复数据的可能