所以基本上我想要實作的是，如果四列是空的，一列包含 1 作為值，一列包含 0，那么我想在一列中輸入滿足默認值（0.06077）條件的條目。
含義： IF COL_A, COL_B, COL_C, COL_D IS NULL and COL_E = 0 AND COL_F = 0 然后填充 COL_A 中滿足這些條件的行，默認值為 0.06077。
我嘗試了以下方法：
, CASE WHEN COL_A IS NULL AND COL_B IS NULL AND COL_C IS NULL AND COL_D IS NULL
AND COL_E = 0 AND COL_F = 1 THEN NVL(COL_A, 0.06077) END
以下確實填寫了符合我設定條件的條目。有人知道潛在的解決方案嗎？
uj5u.com熱心網友回復：
如果 COL_A、COL_B、COL_C、COL_D 為 NULL 并且 COL_E = 0 AND COL_F = 0，則使用默認值 0.06077 填充 COL_A 中滿足這些條件的行。
你可能需要這個case運算式：
case when
coalesce(COL_A, COL_B, COL_C, COL_D) is null and COL_E = 0 AND COL_F = 1
then 0.06077
else COL_A end as COL_A
例子
with dt as (
select null COL_A, null COL_B, null COL_C, null COL_D, 0 COL_E, 1 COL_F from dual union all
select 1 COL_A, null COL_B, null COL_C, null COL_D, 0 COL_E, 1 COL_F from dual union all
select null COL_A, null COL_B, null COL_C, null COL_D, 0 COL_E, 0 COL_F from dual
)
select
case when
coalesce(COL_A, COL_B, COL_C, COL_D) is null and COL_E = 0 AND COL_F = 1
then 0.06077
else COL_A end as COL_A,
COL_B, COL_C, COL_D, COL_E, COL_F
from dt;
COL_A COL_B COL_C COL_D COL_E COL_F
---------- ----- ----- ----- ---------- ----------
,06077 0 1
1 0 1
0 0
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/407687.html
標籤：
下一篇：使用多個樞軸將行轉置為列