请教一个sql的写法

显示全部楼层 · 2021-10-12 20:11:36

select q.seq_id,
case

when q.zh_ba_fwzbm is not null then
q.zh_ba_fwzbm

when q.zh_wxzj is not null then
q.zh_wxzj

when q.zh_ps_ybd is not null then
q.zh_ps_ybd

else
null
end zh_dongh
from tmp_fk_qsdj_qlfw q
意思是：zh_dongh的取值按照case when来取，即如果zh_ba_fwzbm 不为空就取zh_ba_fwzbm，否则，如果zh_wxzj 不为空就取zh_wxzj .....这样一个逻辑，现在在系统中我们要加一个字段zh_source来标记zh_dongh的数据来源，比如取zh_ba_fwzbm 时zh_source为1，取zh_wxzj 时zh_source为2，这条sql语句该如何写？

千问 · 2021-10-12 20:11:36

百度一下
oracle COALESCE

千问 · 2021-10-12 20:11:36

select q.seq_id,
case
when q.zh_ba_fwzbm is not null then

q.zh_ba_fwzbm
when q.zh_wxzj is not null then

q.zh_wxzj
when q.zh_ps_ybd is not null then

q.zh_ps_ybd
else

null
end zh_dongh,
case
when q.zh_ba_fwzbm is not null then

1
when q.zh_wxzj is not null then

2
when q.zh_ps_ybd is not null then

3
else

null
end zh_dongh
from tmp_fk_qsdj_qlfw q

千问 · 2021-10-12 20:11:36

同意楼上

千问 · 2021-10-12 20:11:36

SELECT Q.SEQ_ID,

DECODE(COALESCE(Q.ZH_BA_FWZBM, Q.ZH_WXZJ, Q.ZH_PS_YBD),

Q.ZH_BA_FWZBM,

Q.ZH_BA_FWZBM,

Q.ZH_WXZJ,

Q.ZH_WXZJ,

Q.ZH_PS_YBD,

Q.ZH_PS_YBD) ZH_DONGH,
DECODE(COALESCE(Q.ZH_BA_FWZBM, Q.ZH_WXZJ, Q.ZH_PS_YBD),

Q.ZH_BA_FWZBM,

1,

Q.ZH_WXZJ,

2,

Q.ZH_PS_YBD,

3,

4) ZH_SOURCE
FROM TMP_FK_QSDJ_QLFW Q