主页 / dba / 问题 / 314250
Accepted
Pantea
Asked: 2022-07-11 22:35:54 +0800 CST

连接列值的更好方法

  • 772

我有一个具有以下结构和示例数据的表：

create table CUSTOMER_TEST
(
  customer_num Number,
  rel_one      Number,
  rel_two      Number,
  rel_three    Number,
  rel_four     Number
)

   Customer_num    Rel_one     Rel_two        Rel_three        Rel_four 
 --------------- ----------- -------------  --------------  --------------
    1               7           12             1000               5
    2               2            1               0                12
    3              12           99              13                0

期望的结果是这样的：

   Customer_num    Rel_one     Rel_two        Rel_three        Rel_four      Relation_Code
 --------------- ----------- -------------  --------------  --------------  ***************
    1               7           12             1000               5           L07R12C99S05
    2               2            1               0                12          L02R01C00S12
    3              12           99              13                0           L12R99C13S00

我写的是这样的：

select   customer_num,
         rel_one , 
         rel_two,
         rel_three,
         rel_four,
       'L' ||
       case 
         when   rel_one > 99 then '99'
           else lpad(  rel_one,2,0) end ||
       'R' ||
       case
         when   rel_two > 99 then '99'
           else lpad(  rel_two,2,0) end ||
       'C' ||
        case
         when   rel_three > 99 then '99'
           else lpad(  rel_three,2,0) end  || 
        'S' ||
                case
                 when   rel_four > 99 then '99'
                   else lpad(  rel_four,2,0) end as relation_code            
                  
from customer_test ;

有人有更好的主意吗？而不是使用这么多的案例陈述..

提前致谢

oracle-11g-r2 query-performance

1 个回答

  1. Best Answer
    select   customer_num,
         rel_one , 
         rel_two,
         rel_three,
         rel_four,
       'L' || lpad(LEAST(rel_one,   99), 2, 0) ||
       'R' || lpad(LEAST(rel_two,   99), 2, 0) ||
       'C' || lpad(LEAST(rel_three, 99), 2, 0) ||
       'S' || lpad(LEAST(rel_four,  99), 2, 0)  as relation_code                  
from customer_test ;

    我希望 none 值可以为 NULL 或负数。

    • 1

相关问题