sql - 在Oracle中连接值

我必须像a-b-c一样使用'-'连接列a，b，c。如果a或b或c为null，则不应考虑此列值。

例:

select A,B,C,
case
  when A is not null and B is not null and C is not null then
  A ||'-'||B ||'-'|| C
  when A is not null and B is not null then
A||'-'||B
when A is not null and C is not null then
A||'-'||C
when B is not null and C is not null then
B||'-'||C
when A is null and B is null and C is not null then
C
when A is null and C is null and B is not null  then
B
when B is null and C is null and A is not null then
A
end
  TEXT from
table1

请提出我们可以通过其他方式实现此逻辑吗

最佳答案

您不必使其变得如此复杂。 NULL无论如何都会被隐式处理，不会被考虑，您只需要注意用来连接字符串的定界符即可。 TRIM 应该可以完成这项工作。

SQL> WITH DATA AS(
  2  SELECT 1 A, 2 b, 3 c FROM dual UNION ALL
  3  SELECT NULL A, NULL b, NULL c FROM dual UNION ALL
  4  SELECT 1 A, NULL b, NULL c FROM dual UNION ALL
  5  SELECT 1 A, 2 b, NULL c FROM dual
  6  )
  7  SELECT TRIM(both '-' FROM A||'-'||b||'-'||c) str FROM DATA;

STR
----------------------------------------------------------------
1-2-3

1
1-2

SQL>

如果要排除NULL值，请向谓词添加 NOT NULL 过滤器。

SQL> WITH DATA AS(
  2  SELECT 1 A, 2 b, 3 c FROM dual UNION ALL
  3  SELECT NULL A, NULL b, NULL c FROM dual UNION ALL
  4  SELECT 1 A, NULL b, NULL c FROM dual UNION ALL
  5  SELECT 1 A, 2 b, NULL c FROM dual
  6  )
  7  SELECT str
  8  FROM
  9    ( SELECT TRIM(BOTH '-' FROM A||'-'||b||'-'||c) str FROM DATA
 10    )
 11  WHERE str IS NOT NULL;

STR
-------------------------------------------------------------------
1-2-3
1
1-2

SQL>

当中间列为NULL而其他列不为null时，更新情况。

SQL> WITH DATA AS(
  2  SELECT 1 A, 2 b, 3 c FROM dual UNION ALL
  3  SELECT NULL A, NULL b, NULL c FROM dual UNION ALL
  4  SELECT 1 A, NULL b, NULL c FROM dual UNION ALL
  5  SELECT 1 A, 2 b, NULL c FROM dual UNION ALL
  6  SELECT 1 A, NULL b, 3 c FROM dual
  7  )
  8  SELECT REPLACE(str, '--', '-') str
  9  FROM
 10    ( SELECT TRIM(BOTH '-' FROM A||'-'||b||'-'||c) str FROM DATA
 11    )
 12  WHERE str IS NOT NULL;

STR
-------------------------------------------------------------------
1-2-3
1
1-2
1-3

SQL>