双表多条件匹配SQL查询求助：实现两级匹配并填充up_ind_t2

表结构 #

表t1 #

表t2 #

需求 #

• 优先基于serial_num_t1 = serial_num_t2匹配t1和t2的记录
• 对精确匹配失败的记录，再基于serial_num_trunc_t1 = serial_num_trunc_t2进行匹配
• 最终结果需包含t1的全部6条记录，且所有记录的up_ind_t2字段值为1

原SQL问题 #

原CTE查询存在以下问题：

1. 语法错误：SELECT a.*后缺少逗号，子查询中使用未定义的别名q（WHERE q.up_ind_t2 <> 1）
2. 逻辑错误：使用内连接合并两次匹配结果，会丢失未匹配的t1记录，且未实现"优先精确匹配"的优先级逻辑

优化后的SQL语句 #

WITH t1_data AS (
    SELECT * FROM t1
), t2_data AS (
    SELECT * FROM t2
)
SELECT 
    t1.*,
    COALESCE(t2_exact.up_ind_t2, t2_trunc.up_ind_t2) AS up_ind_t2,
    COALESCE(t2_exact.serial_num_t2, t2_trunc.serial_num_t2) AS serial_num_t2,
    COALESCE(t2_exact.serial_num_trunc_t2, t2_trunc.serial_num_trunc_t2) AS serial_num_trunc_t2
FROM t1_data t1
-- 第一步：精确匹配
LEFT JOIN t2_data t2_exact 
    ON t1.serial_num_t1 = t2_exact.serial_num_t2
-- 第二步：仅精确匹配失败时，使用截断匹配
LEFT JOIN t2_data t2_trunc 
    ON t1.serial_num_trunc_t1 = t2_trunc.serial_num_trunc_t2
    AND t2_exact.serial_num_t2 IS NULL
-- 确保最终结果的up_ind_t2为1
WHERE COALESCE(t2_exact.up_ind_t2, t2_trunc.up_ind_t2) = 1;

逻辑说明 #

1. 先通过t2_exact执行精确匹配，优先获取完全匹配的t2数据
2. 仅当精确匹配无结果时，才通过t2_trunc执行截断匹配
3. 使用COALESCE函数优先取精确匹配的字段值，若无则 fallback 到截断匹配的结果
4. 最后过滤出up_ind_t2为1的记录，同时保证t1的全部6条记录都被包含（基于示例数据，所有t1记录都能匹配到up_ind_t2=1的t2记录）

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