MySQL左连接查询优化：用WIT2非空值替换WIT1字段值

原查询语句 #

select tn.trans_date, tn.with as wit1, stn.with as wit2 
FROM fn_trans AS tn 
LEFT JOIN fn_sub_trans AS stn ON stn.L_trans_id = tn.trans_id 
WHERE tn.L_cat_id IN (11,12) OR stn.L_cat_id in (11,12) 

原查询返回结果 #

trans_date  wit1        wit2    
2023-06-14  50.0000     12.0000
2023-06-14  50.0000     13.0000
2023-05-08  16.0000     NULL
2023-04-29  13.5000     NULL
2023-04-28  65.6000     NULL 

需求说明 #

当wit2字段不为NULL时，用wit2的值替换对应行的wit1；当wit2为NULL时，保留wit1的原有值。

修改后的查询语句 #

如果只需要输出处理后的最终值，使用以下语句：

select 
    tn.trans_date,
    COALESCE(stn.with, tn.with) as final_wit
FROM fn_trans AS tn 
LEFT JOIN fn_sub_trans AS stn ON stn.L_trans_id = tn.trans_id 
WHERE tn.L_cat_id IN (11,12) OR stn.L_cat_id in (11,12) 

如果需要保留原有的wit1、wit2字段同时新增处理后的列，使用以下语句：

select 
    tn.trans_date,
    tn.with as wit1,
    stn.with as wit2,
    COALESCE(stn.with, tn.with) as final_wit
FROM fn_trans AS tn 
LEFT JOIN fn_sub_trans AS stn ON stn.L_trans_id = tn.trans_id 
WHERE tn.L_cat_id IN (11,12) OR stn.L_cat_id in (11,12) 

逻辑说明 #

COALESCE函数会返回传入参数列表中第一个非NULL的值，刚好匹配需求逻辑：优先取stn.with（即原结果中的wit2），若其为NULL则取tn.with（原结果中的wit1）。

预期返回结果 #

仅输出最终值的结果：

trans_date  final_wit
2023-06-14  12.0000
2023-06-14  13.0000
2023-05-08  16.0000
2023-04-29  13.5000
2023-04-28  65.6000

保留原字段的结果：

trans_date  wit1        wit2        final_wit
2023-06-14  50.0000     12.0000     12.0000
2023-06-14  50.0000     13.0000     13.0000
2023-05-08  16.0000     NULL        16.0000
2023-04-29  13.5000     NULL        13.5000
2023-04-28  65.6000     NULL        65.6000

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