根据条件替换null [英] Replacing null based on a condition

问题描述

我有一个包含很多列的表(但这里只发布了col1，col2，col3以简化发布):

I am having a table with many columns (but posting only col1, col2, col3 here for simplified post):

id    col1       col2            col3    source_id
a1    765.3      23-Apr-08       cat     a5
a2    3298.3     (null)          dog     a4
a3    8762.1     27-Nov-10       rat     a8
a4    (null)     (null)          (null) (null)      
a5    (null)     (null)          (null)  a6
a6    (null)     (null)          (null)  (null)

我想用id中的值填充源_id 的空值.例如， source_id a5行的null 必须替换为 id a1值，随后， source_id a6行的null 要替换为 a5行

I want to fill null values of source _id with values from id. For example, source_id a5 row has null which has to replaced with id a1 values, subsequently, source_id a6 row having null to be replaced with a5 row

输出:

id    col1       col2            col3   source_id
a1    765.3      23-Apr-08       cat    a5
a2    3298.3     (null)          dog    a4
a3    8762.1     27-Nov-10       rat    a8
a4    3298.3     (null)          dog   (null)       
a5    765.3      23-Apr-08       cat    a6
a6    765.3      23-Apr-08       cat  (null)

推荐答案

这看起来像左连接和条件逻辑:

This looks like a left join and conditional logic:

select 
    t.id,
    coalesce(t.col1, t1.col1) col1,
    coalesce(t.col2, t1.col2) col2,
    coalesce(t.col3, t1.col3) col3,
    t.source_id
from mytable t
left join mytable t1 on t1.id = t.source_id

这篇关于根据条件替换null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！