Databricks SQL关联列非等值谓词报错，求重构无关联子查询的T-SQL

报错原因是Databricks SQL不允许在关联子查询的非等值谓词中引用外部列（对应报错信息里的outer(StatusID#20829) IN (1,2)）。原查询的关联子查询同时引用了外部表的SplitID（等值关联）和StatusID（非等值筛选），这在Databricks的SQL解析规则中不被支持。

将关联子查询替换为提前聚合+LEFT JOIN的方式，规避关联子查询的限制：

SELECT DISTINCT
    s.AccountID,
    s.CreatedDate,
    s.StatusID,
    -- 仅当状态符合要求时显示取消日期，否则返回NULL，与原逻辑一致
    CASE WHEN s.StatusID IN (1, 2) THEN sa.CancelledDate ELSE NULL END AS CancelledDate
FROM CRM.InvestmentInstruction.Split s
INNER JOIN CRM.InvestmentInstruction.SplitPortfolio sp
    ON sp.SplitID = s.SplitID
INNER JOIN CRM.InvestmentInstruction.InvestmentRequest ir
    ON ir.InvestmentRequestID = sp.InvestmentRequestID
INNER JOIN CRM.dbo.ModelPortfolio mp
    ON mp.ModelPortfolioID = ir.ModelID
INNER JOIN (
    SELECT DISTINCT
        mh.ModelPortfolioID
    FROM CRM.dbo.modelHolding mh
    INNER JOIN Securities.dbo.Security sec
        ON sec.SecurityID = mh.LinkSecurityId
    WHERE sec.IsCashSecurity = 0
) mh
    ON mh.ModelPortfolioID = mp.ModelPortfolioID
-- 左连接提前聚合好的取消记录数据
LEFT JOIN (
    SELECT
        sia.SplitID,
        MAX(sia.DateOfChange) AS CancelledDate
    FROM CRM.InvestmentInstruction.SiiAudit sia
    WHERE sia.SiiAuditTypeID = 3 -- 筛选取消类型的审计记录
    GROUP BY sia.SplitID
) sa
    ON sa.SplitID = s.SplitID
WHERE s.TypeID = 0

1. 提前聚合审计数据：单独对SiiAudit表按SplitID分组，计算每个拆分记录对应的最新取消日期，生成仅包含关联键和目标字段的轻量数据集。
2. 替换关联子查询为LEFT JOIN：用LEFT JOIN关联主查询和聚合后的数据集，确保主表所有符合条件的记录都能保留，无对应取消记录时返回NULL。
3. 状态条件迁移：原关联子查询中的s.StatusID IN (1,2)条件，移到SELECT语句的CASE表达式中，保证仅符合状态要求的记录才显示取消日期，与原查询逻辑完全对齐。

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