能否将指定JOIN转为带参数的通用CTE？（Oracle/SQL Server/DB2）

完全没问题，咱们可以把原来的嵌套子查询重构为带参数的CTE，既保留你要的首行过滤逻辑（避免全表扫描），还能在主查询里灵活传入不同的HOBBIT_CODE和ASSOCIATION_CODE参数，同时兼容Oracle、SQL Server、DB2这些支持CTE的数据库。

核心思路 #

CTE本身支持引用外部定义的参数（不同数据库的参数声明方式略有差异），我们只需要把原查询里的硬编码条件替换成参数，然后在CTE里保留ROW_NUMBER()的分区排序逻辑——这正是你原来嵌套子查询保证性能的关键，CTE版本会完全继承这个性能优势。

具体实现示例 #

下面分数据库类型给出参数化CTE的写法，你可以根据实际环境调整：

1. Oracle 版本（使用绑定变量）

-- 声明绑定变量（PL/SQL中可直接用，SQL*Plus需先定义）
VAR p_hobbit_code VARCHAR2(10);
VAR p_assoc_code VARCHAR2(10);
EXEC :p_hobbit_code := '99';
EXEC :p_assoc_code := 'GNDLF';

WITH MITHRIL_HOBT_CTE AS (
    SELECT 
        HH.*,
        ROW_NUMBER() OVER (PARTITION BY HH.WORKSHEET_HISTORY_ID ORDER BY QQ.GOBLIN_HOBBIT_ID DESC) AS INDICATOR
    FROM ILU.WORKSHEET_HOBBIT_RECOMMENDATIONS HH
    INNER JOIN ILU.GOBLIN_HOBBIT QQ 
        ON QQ.GOBLIN_HOBBIT_id = HH.GOBLIN_HOBBIT_id
    INNER JOIN ILU.HOBBIT_master ZZ 
        ON ZZ.HOBBIT_master_id = QQ.HOBBIT_master_id
    WHERE ZZ.HOBBIT_CODE = :p_hobbit_code 
      AND ZZ.ASSOCIATION_CODE <> :p_assoc_code
)
-- 主查询里直接LEFT JOIN这个CTE，过滤出首行数据
SELECT 
    hst.*,
    MITHRIL_HOBT_CTE.* -- 按需选择字段
FROM YOUR_MAIN_TABLE hst
LEFT JOIN MITHRIL_HOBT_CTE 
    ON MITHRIL_HOBT_CTE.WORKSHEET_HISTORY_ID = hst.WORKSHEET_HISTORY_ID
    AND MITHRIL_HOBT_CTE.INDICATOR = 1;

2. SQL Server / DB2 版本（使用局部变量）

-- 声明局部变量
DECLARE @p_hobbit_code VARCHAR(10) = '99';
DECLARE @p_assoc_code VARCHAR(10) = 'GNDLF';

WITH MITHRIL_HOBT_CTE AS (
    SELECT 
        HH.*,
        ROW_NUMBER() OVER (PARTITION BY HH.WORKSHEET_HISTORY_ID ORDER BY QQ.GOBLIN_HOBBIT_ID DESC) AS INDICATOR
    FROM ILU.WORKSHEET_HOBBIT_RECOMMENDATIONS HH
    INNER JOIN ILU.GOBLIN_HOBBIT QQ 
        ON QQ.GOBLIN_HOBBIT_id = HH.GOBLIN_HOBBIT_id
    INNER JOIN ILU.HOBBIT_master ZZ 
        ON ZZ.HOBBIT_master_id = QQ.HOBBIT_master_id
    WHERE ZZ.HOBBIT_CODE = @p_hobbit_code 
      AND ZZ.ASSOCIATION_CODE <> @p_assoc_code
)
-- 主查询里LEFT JOIN并过滤首行
SELECT 
    hst.*,
    MITHRIL_HOBT_CTE.*
FROM YOUR_MAIN_TABLE hst
LEFT JOIN MITHRIL_HOBT_CTE 
    ON MITHRIL_HOBT_CTE.WORKSHEET_HISTORY_ID = hst.WORKSHEET_HISTORY_ID
    AND MITHRIL_HOBT_CTE.INDICATOR = 1;

为什么这个方案能保证性能？ #

和你原来的嵌套子查询逻辑完全一致：

• 先通过ROW_NUMBER()按WORKSHEET_HISTORY_ID分区，按GOBLIN_HOBBIT_ID倒序排序，只保留每个分区的第一行（INDICATOR=1）
• 避免了你之前用rownum=1的子查询带来的全表扫描问题——因为CTE里的关联和过滤逻辑会先执行，再做分区排序，数据库可以利用索引优化这个过程

额外提示 #

如果需要在同一个会话里多次调用不同参数，只需要修改变量的值重新执行主查询即可，比写多次嵌套子查询或者函数要灵活得多，也更容易调试和优化。

内容的提问来源于stack exchange，提问作者mgrant24601