sql - 在oracle中连接值

Question

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

例子:

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

请建议我们能否以其他方式实现此逻辑

Answer 1

你不必把它弄得那么复杂。 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 且其他列不为空时的大小写。

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>