sql之在oracle中连接值

我必须像 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 

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

请您参考如下方法:

你不必把它弄得那么复杂。 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>