如何在SELECT语句中根据关联表数据重写Account表Type字段

完全可以通过SELECT语句结合子查询或CTE（公共表表达式）实现需求，不需要创建临时表。以下是两种可行的写法：

写法一：使用关联子查询 + CASE语句 #

这种写法直接在主查询中通过关联子查询判断每个账户的保单状态：

SELECT
    a.ID,
    CASE
        -- 无关联保单时设为Prospect
        WHEN NOT EXISTS (SELECT 1 FROM Policy p WHERE p.RelatedAccountID = a.ID) THEN 'Prospect'
        -- 存在过期保单且无有效(Active)保单时设为Previous Customer
        WHEN EXISTS (SELECT 1 FROM Policy p WHERE p.RelatedAccountID = a.ID AND p.Status = 'Expired')
             AND NOT EXISTS (SELECT 1 FROM Policy p WHERE p.RelatedAccountID = a.ID AND p.Status = 'Active') THEN 'Previous Customer'
        -- 其他情况保留原Type值
        ELSE a.Type
    END AS Type
FROM Account a
ORDER BY a.ID;

写法二：使用CTE聚合保单状态后关联 #

先通过CTE统计每个账户是否有有效保单、是否有过期保单，再关联Account表做判断：

WITH PolicySummary AS (
    SELECT
        RelatedAccountID,
        -- 标记是否有有效保单
        MAX(CASE WHEN Status = 'Active' THEN 1 ELSE 0 END) AS has_active,
        -- 标记是否有过期保单
        MAX(CASE WHEN Status = 'Expired' THEN 1 ELSE 0 END) AS has_expired
    FROM Policy
    GROUP BY RelatedAccountID
)
SELECT
    a.ID,
    CASE
        -- 无关联保单（CTE中无对应记录）
        WHEN ps.RelatedAccountID IS NULL THEN 'Prospect'
        -- 有过期保单且无有效保单
        WHEN ps.has_expired = 1 AND ps.has_active = 0 THEN 'Previous Customer'
        -- 其他情况保留原Type
        ELSE a.Type
    END AS Type
FROM Account a
LEFT JOIN PolicySummary ps ON a.ID = ps.RelatedAccountID
ORDER BY a.ID;

两种写法都能得到你需要的预期结果：

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