如何让SQL仅读取IN子句中引用的CTE一次？查询优化求助

我用以下SQL做演示（最终目标是创建视图展示订单项及其父项信息）：

WITH RootItemIDs AS 
(
    SELECT DISTINCT ParentID 
    FROM C_Item 
    WHERE ParentID NOT IN (SELECT ItemID FROM C_Item)
)
SELECT COUNT(*)
FROM C_OrderHeader oh
JOIN C_OrderDetail od ON oh.OrderID = od.OrderID
JOIN C_Item i ON od.ItemID = i.ItemID
JOIN C_Item rootItem ON i.ParentID = rootItem.ItemID 
                     OR (i.ParentID IN (SELECT * FROM RootItemIDs) 
                         AND i.ItemID = rootItem.ItemID)

注：部分C_Item行的ParentID无有效指向，这类行视为“根项”，这也是CTE命名为RootItemIDs的原因。

当前问题：执行计划显示，仅83条记录的C_Item表被每个订单项重复读取，返回数百万行数据。尝试用临时表替代CTE，问题依旧。硬编码IN子句的值能解决，但不想手动维护新增值，需要改写查询避免重复扫描C_Item表，且最好不用临时表以便用于视图。

核心思路是提前一次性计算出每个Item对应的根项ID，将原来的OR关联转化为等值关联，让数据库能有效利用索引，避免重复扫描表。

方案一：用CASE判断根项ID #

WITH ItemRoots AS (
    SELECT 
        ItemID,
        -- ParentID无效时，根项为自身；否则取ParentID对应的项
        CASE 
            WHEN ParentID NOT IN (SELECT ItemID FROM C_Item) THEN ItemID
            ELSE ParentID
        END AS RootItemID
    FROM C_Item
)
SELECT 
    -- 替换为你实际需要的订单项、父项字段
    oh.OrderID,
    od.OrderDetailID,
    i.ItemID AS 子项ID,
    i.ItemName AS 子项名称,
    rootItem.ItemID AS 根项ID,
    rootItem.ItemName AS 根项名称
FROM C_OrderHeader oh
JOIN C_OrderDetail od ON oh.OrderID = od.OrderID
JOIN C_Item i ON od.ItemID = i.ItemID
JOIN ItemRoots ir ON i.ItemID = ir.ItemID
JOIN C_Item rootItem ON ir.RootItemID = rootItem.ItemID

方案二：用LEFT JOIN避免NOT IN的NULL问题 #

如果C_Item的ParentID存在NULL值，NOT IN会导致逻辑错误，推荐用LEFT JOIN判断ParentID是否有效：

WITH ItemRoots AS (
    SELECT 
        i.ItemID,
        -- ParentID有效则取对应ItemID，无效则取自身ID
        COALESCE(p.ItemID, i.ItemID) AS RootItemID
    FROM C_Item i
    LEFT JOIN C_Item p ON i.ParentID = p.ItemID
)
SELECT 
    -- 按需选择字段
    oh.OrderID,
    od.OrderDetailID,
    i.*,
    rootItem.*
FROM C_OrderHeader oh
JOIN C_OrderDetail od ON oh.OrderID = od.OrderID
JOIN C_Item i ON od.ItemID = i.ItemID
JOIN ItemRoots ir ON i.ItemID = ir.ItemID
JOIN C_Item rootItem ON ir.RootItemID = rootItem.ItemID

优化原理 #

原来的JOIN条件包含OR，会让数据库无法高效利用索引，被迫对C_Item表进行重复扫描。提前预计算每个Item的根项ID后，后续的关联都是等值连接，数据库可以通过索引快速定位数据，彻底解决重复扫描的问题，且该方案可直接用于视图。

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