SQL:如何检查列中的值是否等于行中其他列的任意组合的总和

Question

我遇到了一个场景，我有一个这样的表结构：

Column0 Column1 Column2 Column3 Column4 Column5 
100     U       V       X       Y       Z

我需要了解是否：

• 100=U、V、X、Y或Z。
• 100=X+Y+Z+V
• 100=X+Y
• 100=V+Z
• 等。

关于如何实现这一目标的任何建议？

案例陈述将永远需要写出来。我不写PL/SQL代码，但我对它有些熟悉。

Answer 1

编写Case语句并不像你暗示的那么难。 我在大约 20 秒内使用文本编辑器生成了以下Case语句，该语句应处理示例中的所有情况：

Select  Case
When Column0 = Column1  Then 1
When Column0 = Column2  Then 1
When Column0 = Column3  Then 1
When Column0 = Column4  Then 1
When Column0 = Column5  Then 1
When Column0 = Column1 + Column2    Then 1
When Column0 = Column1 + Column3    Then 1
When Column0 = Column1 + Column4    Then 1
When Column0 = Column1 + Column5    Then 1
When Column0 = Column2 + Column3    Then 1
When Column0 = Column2 + Column4    Then 1
When Column0 = Column2 + Column5    Then 1
When Column0 = Column3 + Column4    Then 1
When Column0 = Column3 + Column5    Then 1
When Column0 = Column4 + Column5    Then 1
When Column0 = Column1 + Column2 + Column3  Then 1
When Column0 = Column1 + Column2 + Column4  Then 1
When Column0 = Column1 + Column2 + Column5  Then 1
When Column0 = Column1 + Column3 + Column4  Then 1
When Column0 = Column1 + Column3 + Column5  Then 1
When Column0 = Column1 + Column4 + Column5  Then 1
When Column0 = Column2 + Column3 + Column4  Then 1
When Column0 = Column2 + Column3 + Column5  Then 1
When Column0 = Column2 + Column4 + Column5  Then 1
When Column0 = Column3 + Column4 + Column5  Then 1
When Column0 = Column1 + Column2 + Column3 + Column4    Then 1
When Column0 = Column1 + Column2 + Column3 + Column5    Then 1
When Column0 = Column1 + Column3 + Column4 + Column5    Then 1
When Column0 = Column2 + Column3 + Column4 + Column5    Then 1
When Column0 = Column1 + Column2 + Column3 + Column4 + Column5  Then 1
Else 0
End As SumOfOtherColumns
From    YourTable

Answer 2

即使答案已经被接受，这是我的替代解决方案。在性能方面，它很可能更糟(待测试(，但我认为值得展示，原因如下：

如您所见，有 5 列已经很容易错过一些案例，只有 31 个案例需要检查。 如果您添加一列，这将转到 63 检查，下一列为 127 ...您不必担心这一点，因为它会动态生成所有案例

另一个有趣的一点是，如果您有兴趣查看每行的详细信息以及它匹配的原因，它是免费的查询。 你只需要选择子视图

最后一点是，我认为这在学术上很有趣。该解决方案包含数据反透视、递归自联接、动态动态表达式计算。当然，我不客观，但地狱很有趣:)

--Table and data to test the query
create table my_table ( column0 number, column1 number, column2 number, 
column3 number, column4 number, column5 number );
INSERT INTO my_table values (100,20,20,10,40,10); -- must match on the sum of 5 columns
INSERT INTO my_table values (100,50,200,300,150,250); -- must not match
INSERT INTO my_table values (100,50,50,100,150,250); -- must match twice ( on col1+col2 and col3 )
-- If your table has a unique key, you can remove the datas_with_id and put
-- your table directly in the unpivoted_data subquery
with datas_with_id  as ( select rowid as row_id, t.* from my_table t),
unpivoted_data as ( select row_id, column0 as sum_to_check, column_name, column_value 
from datas_with_id 
unpivot ( column_value for column_name in (column1,column2,column3,column4,column5))),
calculated_sum as ( select row_id, xmlquery(sys_connect_by_path(u.column_value,'+')||' = '|| sum_to_check
returning content).getStringVal() result
from unpivoted_data u connect by nocycle prior column_name>column_name 
and prior row_id=row_id and level < 6)
select * from my_table where rowid in ( select row_id from calculated_sum where result = 'true' )

如果要添加另一列，请将其添加到 unpivot 子句中，将 1 添加到级别中，您就很好

了如果在calculated_sum中添加sys_connect_by_path(u.column_name，'+'(||' = '|| sum_to_check，您可以看到匹配的每个公式

Answer 3

在这里，我编写了一些可以动态使用它的代码

1-首先我创建表并为其生成一行

create table my_table ( column0 number, column1 number, column2 number, 
column3 number, column4 number, column5 number );
INSERT INTO my_table values (100,20,20,10,40,10);

2 创建类型如下行

create or replace type Key_Value_Typ as object
(
column_name   varchar2(100),
column_val    varchar2(100)  
)

3-你可以在这里看到一个例子

declare
lv_count number;
lv_row   my_table%rowtype;
type my_list is table of Key_Value_Typ;
list1 my_list;
------------------------------------------------------------
-- function for control column condition
function check_fun(
lv_list my_list, 
-- the function input is array of key value include column name and column value
-- you can implement your code here, for example as input parameter declare your own parameters 
-- for example
--   lv_column0 my_table.column0%type;
lv_where varchar2
-- the condition that you want check it
) return number is
lv_str   varchar2(200);
lv_count number;
begin
lv_str := lv_where;
for i in 1 .. lv_list.count Loop
lv_str := replace(lv_str,
lv_list(i).column_name,
lv_list(i).column_val);
ENd Loop;
execute immediate 'select count(*) from dual where ' || lv_str
into lv_count;
-- if the function return 1 result is true and o means result is false
return lv_count;
end check_fun;
------------------------------------------------------------
begin
-- fetch data from my_table and get one of them 
select * into lv_row from my_table where column0 = 100;
-- create list of data include columns and values
list1 := my_list(Key_Value_Typ('column0', lv_row.column0),
Key_Value_Typ('column1', lv_row.column1),
Key_Value_Typ('column2', lv_row.column2),
Key_Value_Typ('column3', lv_row.column3),
Key_Value_Typ('column4', lv_row.column4),
Key_Value_Typ('column5', lv_row.column5));

lv_count := check_fun(list1, 'column0=column1+column2+column3+column4+column5');
dbms_output.put_line('result ()' || lv_count);
end;