基于类Excel VLOOKUP规则的SQL两表关联生成新表问题

核心思路 #

• 采用LEFT JOIN保留Table1的全量数据，避免丢失未匹配到Table2的行
• 关联键仅为CHI字段，符合规则要求
• 新增字段HOSP_new可通过COALESCE函数简化实现，也可通过CASE WHEN写更易读的判断逻辑
• 提前处理Table2的重复值，保证关联后无重复行

前置优化（适配1200万行大数据量） #

• 给Table1和Table2的CHI字段分别建立索引，大幅提升关联查询效率
• 提前对Table2按CHI去重，确保每个CHI仅对应1行记录，避免关联后产生重复行：如果CHI存在多条记录，可根据业务需求选择取最新日期对应的CRN，示例去重逻辑如下：

-- 先对Table2去重，确保每个CHI唯一
SELECT CHI, MAX(CRN) AS CRN -- 业务如果有指定取数规则可替换此处聚合逻辑
FROM Table2
GROUP BY CHI

若已经确认Table2的CHI为唯一主键，可跳过聚合步骤，直接关联原表，性能更好。

最终实现SQL #

通用标准SQL写法 #

CREATE TABLE Table1_new AS
SELECT 
    t1.Date,
    t1.CHI,
    t1.HOS,
    COALESCE(t2.CRN, t1.HOS) AS HOSP_new
FROM Table1 t1
LEFT JOIN (
    -- 子查询确保Table2的CHI唯一
    SELECT CHI, MAX(CRN) AS CRN
    FROM Table2
    GROUP BY CHI
) t2 ON t1.CHI = t2.CHI;

如果需要更明确对应规则的写法，也可以用CASE WHEN实现，逻辑完全等价：

CREATE TABLE Table1_new AS
SELECT 
    t1.Date,
    t1.CHI,
    t1.HOS,
    CASE
        WHEN t2.CHI IS NULL THEN t1.HOS
        WHEN t1.HOS = t2.CRN THEN t1.HOS
        ELSE t2.CRN
    END AS HOSP_new
FROM Table1 t1
LEFT JOIN (
    SELECT CHI, MAX(CRN) AS CRN
    FROM Table2
    GROUP BY CHI
) t2 ON t1.CHI = t2.CHI;

无重复行保障说明 #

关联前已经对Table2按CHI做了聚合去重，每个CHI仅对应一行，和Table1的行关联时为1:1或1:0的关系，不会产生笛卡尔积重复行，最终输出行数和Table1原始行数完全一致。

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