这是一个两步查找，因此
1.在cte_rnk_val中创建第一个查找（dense_rnk，val）
1.在cte_prd2_rank中创建第二个查找（id，prd2，rnk）
1.将所有三个连接在一起以获得输出

with cte_rnk_val as (
select dense_rnk as rnk, val from tab_secret group by 1,2),
cte_prd2_rank as (
select id, prd2, dense_rnk-1 as rnk from tab_secret group by 1,2,3)
select s.id,
       s.prd1,
       s.prd2,
       s.dense_rnk,
       s.val,
       r.val as output
  from tab_secret s
  join cte_prd2_rank p
    on s.id = p.id and s.prd1 = p.prd2
  join cte_rnk_val r
    on p.rnk = r.rnk;

输出量：

id  |prd1      |prd2      |dense_rnk|val|output|
----+----------+----------+---------+---+------+
7865|2015-04-10|2013-02-21|        1|S  |Z     |
7865|2013-07-09|2013-02-21|        1|S  |S     |
7865|2013-07-09|2013-02-21|        1|S  |S     |
7865|2015-04-10|2013-02-21|        1|S  |Z     |
7865|2015-04-10|2013-07-09|        2|Z  |Z     |
7865|2013-07-09|2013-07-09|        2|Z  |S     |
7865|2013-07-09|2014-10-10|        3|Z  |S     |
7865|2015-04-10|2014-10-10|        3|Z  |Z     |
7865|2015-04-10|2015-04-10|        4|Z  |Z     |
7865|2013-07-09|2015-04-10|        4|Z  |S     |
7865|2013-07-09|2016-10-15|        5|R  |S     |
7865|2015-04-10|2016-10-15|        5|R  |Z     |
7865|2015-04-10|2021-10-16|        6|R  |Z     |
7865|2013-07-09|2021-10-16|        6|R  |S     |

已在MySQL中测试查询。

试试这个。你可能需要翻译成Oracle，因为它是为SSMS格式化的。

SELECT e.Val
FROM (
    SELECT c.dense_rnk-1
    FROM (
        SELECT b.dense_rnk
        FROM data a INNER JOIN data b on a.prd1 = b.prd2 
    ) c
) d LEFT JOIN data e ON d.dense_rnk = e.dense_rnk

请注意，这个解决方案相当糟糕，使用原始表可能比使用提供的结果集做得更好。