简洁的方式选择COST1与COST2不同的位置（请null = null）

Question

我有两个列：COST1和COST2。

值可以：

• 负数为零
• 正
• 数
• null

with workorder
     (cost1,cost2) as (
select    1,    1 from dual union all
select -100, null from dual union all
select null,    0 from dual union all
select    0,  100 from dual union all
select null, null from dual
)

select
  *
from
  workorder

COST1	COST2
1	1
-100	NULL
NULL	0 0
0	100
NULL	NULL

db＆lt;＆gt; fiddle

---

我想选择与cost1不同于cost2的行。

• 我想对待无效，就像它们是平等的一样。
• 但是我不想将无效视为零。

结果：

COST1	COSS2
-100	NULL
NULL	0
0	100

---

它等于不是 sqlite中的语法：

with workorder 
(cost1,cost2) as (
values
(   1,    1),
(-100, null),
(null,    0),
(   0,  100),
(null, null)
)

select
  *
from
  workorder
where
  cost1 is not cost2

COSS1 COSS1	COSS2
-100	NULL
NULL	0
0	100 100

---

norefloll （简洁的表现）

Answer 1

您可以使用 nvl 函数。因此检查不平等。

where NVL(CAST(cost1 AS VARCHAR2(10)), 'null') <> NVL(CAST(cost2 AS VARCHAR2(10)), 'null')

---

另一个选项是将检查零值的两个条件与单个检查结合在一起：两个值的串联应与一个或另一个成本值相对应。

where cost1 <> cost2
   OR CONCAT(cost1, cost2) IN (cost1, cost2)

检查demos

Answer 2

如何使用binary_float_nan作为nvl的默认值？

with workorder (cost1,cost2) as (
    select    1,    1 from dual union all
    select -100, null from dual union all
    select null,    0 from dual union all
    select    0,  100 from dual union all
    select null, null from dual
)
select o.*
from   workorder o
where  nvl(cost1,binary_double_nan) <> nvl(cost2,binary_double_nan)

另外，如果为COPT定义了精度和比例，例如编号（10,2），则相当安全的默认值可能是0.0001 ，1/3或22/7。

Answer 3

这是一种方式。但这并不像我想要的那样简洁：

with workorder
     (cost1,cost2) as (
select    1,    1 from dual union all
select -100, null from dual union all
select null,    0 from dual union all
select    0,  100 from dual union all
select null, null from dual
)

select
  *
from
  workorder
where
  cost1 <> cost2
  or (cost1 is null and cost2 is not null)
  or (cost1 is not null and cost2 is null)

COST1  COST2
-----  -----
 -100   null
 null      0
    0    100