使用最小值总和从不同表中选择唯一记录

I have an SQLite database with 3 tables. Each of these tables have two columns: name and value such that all the tables have the same records (name) but different values. How do I select the records that are a part of least sum of values spanning across these tables such that the record names are unique?

我有一个包含3个表的SQLite数据库。这些表中的每一个都有两列:名称和值,以便所有表具有相同的记录(名称)但不同的值。如何选择跨越这些表的最小值总和的一部分的记录,以使记录名称是唯一的?

Names: Steven, Jamie, Michael, Jordan, Gary

姓名:史蒂文,杰米,迈克尔,乔丹,加里

Values

(in the order of names)

(按名称顺序)

Table 1: 1, 2, 3, 4, 5

表1:1,2,3,4,5

Table 2: 2, 3, 1, 5, 6

表2:2,3,1,5,6

Table 3: 9, 0, 2, 11, 3

表3:9,0,2,11,3

The output should return (Steven, Michael, Jamie) because the sum of the values in this case would equal to 2 which would be the least possible.

输出应该返回(Steven,Michael,Jamie),因为在这种情况下值的总和将等于2,这将是最不可能的。

2 个解决方案

#1

First, SQL tables represent unordered sets. So, there is no correspondence between two tables based on position. Instead, let me assume that each has a name column.

首先,SQL表表示无序集。因此,基于位置的两个表之间没有对应关系。相反,让我假设每个都有一个名称列。

Second, SQL doesn't really do optimization, so you need to do this in a brute force way. That is, generate all the combinations and then choose the minimum value.

其次,SQL并没有真正进行优化,因此您需要以强力方式执行此操作。也就是说,生成所有组合,然后选择最小值。

That uses essentially a cross join:

它主要使用交叉连接:

select min(t1.value + t2.value + t3.value)
from table1 t1 cross join table2 t2 cross join table3 t3
where t1.name <> t2.name and t3.name not in (t1.name, t2.name);

Or, if you want the names:

或者,如果你想要名字:

select (t1.value + t2.value + t3.value), t1.name, t2.name, t3.name
from table1 t1 cross join table2 t2 cross join table3 t3
where t1.name <> t2.name and t3.name not in (t1.name, t2.name)
order by (t1.value + t2.value + t3.value)
limit 1

#2

You can try this

你可以试试这个

    SELECT T1.val,T2.val,T3.val FROM T1 , T2 , T3 
    WHERE T1.val <> T2.val AND T2.val <> T3.val AND T1.val <> T3.val
    GROUP BY T1.val,T2.val,T3.val,(T1.no + T2.no + T3.no)
    HAVING (T1.no + T2.no + T3.no) = 
    (SELECT MIN(T1.no)+MIN(T2.no)+MIN(T3.no)     FROM T1 , T2 , T3 
    WHERE T1.val <> T2.val AND T2.val <> T3.val AND T1.val <> T3.val)

If there are more then 3 tables just add tables after FROM T1 , T2 , T3, T4,..TN
And Add Comparision for not equality by adding this in where clause
T1.val <> T4.val AND T2.val <> T4.val AND T3.val <>T4.val for every table // this is added for T4

如果有超过3个表,则只需在FROM T1,T2,T3,T4,...之后添加表。通过在where子句T1.val <> T4.val和T2.val <> T4中添加此表来添加比较不相等每个表的.val和T3.val <> T4.val //为T4添加

#1