根据条件替换null [英] Replacing null based on a condition
本文介绍了根据条件替换null的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含很多列的表(但这里只发布了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屋!
查看全文