检查一行中多列值之间的等于？

quick question here

这里有个问题

I have a table like this

我有一张这样的桌子

COLUMN_1 COLUMN_2 COLUMN_3 COLUMN_4 COLUMN_5  SAME
X1       X1       X1       X1       X1        X1
X1       X2       X1       X1       X3        DIFFERENT
X1       NULL     X1       X1       NULL      X1
X1       NULL     X2       NULL     X3        DIFFERENT

So basically what i want to know if all the colums between column_1 and column 5 are the same , then get a column with the value (row 1 ).If not all are the columns are different then get a column with different (row 2) . These rows can contain null values (row 3, row 4) but they do not affect the flag.

基本上我想知道如果column_1和第5列之间的所有列都相同，那么得到一个带有值（第1行）的列。如果不是所有列都不同，那么得到一个不同的列（第2行）。这些行可以包含空值（第3行，第4行），但它们不会影响标志。

BTW, The columns type is STRING.

顺便说一句，列类型是STRING。

Im looking to do this in teradata or oracle.

我希望在teradata或oracle中这样做。

Thanks a lot

非常感谢

PS: Column_1 is not null it can contain values from x1 to x5. Column_2 to Column_5 can be null.

PS：Column_1不为null，它可以包含从x1到x5的值。 Column_2到Column_5可以为null。

4 个解决方案

#1

Depending on the actual data you are working with, you could temporarily unpivot the data as rows, and from there your problem is easy to solve. Here I'm assuming there is a primary key in your table called row_id.

根据您使用的实际数据，您可以暂时将数据作为行进行取消，从那里您的问题很容易解决。这里我假设你的表中有一个名为row_id的主键。

Using your example data...

使用您的示例数据......

with your_table as(
   select 1 as row_id, 'X1' as c1, 'X1' as c2, 'X1' as c3, 'X1' as c4, 'X1' as c5 from dual union all
   select 2 as row_id, 'X1' as c1, 'X2' as c2, 'X1' as c3, 'X1' as c4, 'X3' as c5 from dual union all
   select 3 as row_id, 'X1' as c1, null as c2, 'X1' as c3, 'X1' as c4, null as c5 from dual union all
   select 4 as row_id, 'X1' as c1, null as c2, 'X2' as c3, null as c4, 'X3' as c5 from dual
)
select * 
  from your_table; 

ROW_ID   C1  C2  C3  C4  C5
------   --  --  --  --  --
1        X1  X1  X1  X1  X1
2        X1  X2  X1  X1  X3
3        X1      X1  X1   
4        X1      X2      X3

Using the unpivot operator like below, would turn all non-null column values into each own record. Notice how the null values are missing from the output.

使用如下所示的unpivot运算符，会将所有非空列值转换为每个自己的记录。请注意输出中缺少空值的方式。

select * 
  from your_table unpivot(colval for colname in(c1, c2, c3, c4, c5));

row_id   colname  colval
------   -------  ------
1            C1       X1
1            C2       X1
1            C3       X1
1            C4       X1
1            C5       X1
2            C1       X1
2            C2       X2
2            C3       X1
2            C4       X1
2            C5       X3
3            C1       X1
3            C3       X1
3            C4       X1
4            C1       X1
4            C3       X2
4            C5       X3

...And from here it is easy to solve your problem.

......从这里可以轻松解决您的问题。

select row_id
      ,case when count(distinct colval) = 1 -- There is only one value across columns    
            then min(colval)                -- All are same, just pick one
            else 'DIFFERENT'
        end one_value
  from your_table unpivot (colval for colname in(c1, c2, c3, c4, c5))
 group -- Group by to reassemble the record 
    by row_id

Edit: After clarifications about C1 and nullability, the problem is much simpler:

编辑：在澄清C1和可空性后，问题更加简单：

select case when not c1 = all(c2,c3,c4,c5) then 'DIFFERENT' else c1 end
  from your_table;

But I'm anyway leaving the old answer, because it solves the problem when all columns are nullable.

但我无论如何都要留下旧答案，因为它解决了所有列都可以为空的问题。

#2

Using a trigger you can something like this :

使用触发器你可以这样：

CREATE OR REPLACE TRIGGER TRIGGER1 
BEFORE INSERT OR UPDATE ON TEST 
FOR EACH ROW
BEGIN
  IF :new.COLUMN1= :new.COLUMN2
     AND :new.COLUMN3=:new.COLUMN1
     AND  :new.COLUMN4=:new.COLUMN1
     AND :new.COLUMN5=:new.COLUMN1     

   THEN
    :new.same:='SAME';
    ELSE
    :new.same:='DIFFERENT';

    END IF;

END;

You can consider adding NULL condition check as well to the above trigger.

您可以考虑在上面的触发器中添加NULL条件检查。

#3

When column 1 is defined as NOT NULL you can apply following logic: concatenate columns 2 to 5 and remove any occurance of the string in column 1. When the result is an empty string all columns were equal (or NULL).

当第1列定义为NOT NULL时，您可以应用以下逻辑：连接第2列到第5列并删除第1列中字符串的任何出现。当结果为空字符串时，所有列都相等（或NULL）。

CASE 
   WHEN OReplace(  Coalesce(COLUMN_2,'')
                 ||Coalesce(COLUMN_3,'')
                 ||Coalesce(COLUMN_4,'')
                 ||Coalesce(COLUMN_5,'')
                 ,COLUMN_1
                 ,'') = ''
   THEN COLUMN_1
   ELSE 'different'
end

Edit:

编辑：

Ups, this was way too complicated. Much simpler:

起来，这太复杂了。更简单：

CASE
   WHEN column_1 <> column_2
     OR column_1 <> column_3
     OR column_1 <> column_4
     OR column_1 <> column_5
   THEN 'different'
   ELSE column_1   
end

#4

You can consider adding a virtual column to your table:

您可以考虑向表中添加虚拟列：

alter table your_table add same as(
   case when not column_1 = all(column_2, column_3, column_4, column_5) 
        then 'DIFFERENT' 
        else column_1 
    end
);

#1