在查询中连接两个SQL表

I have two tables (with the same schema)

我有两个表(具有相同的架构)

Table1
id   title   D0    D1    D2    D3
------------------------------------
1    Title1  0.12  3.23  4.90 -0.12
1    Title1  0.22  0.32 -4.90  0.12
1    Title1  0.13  1.24  3.50 -0.22
...
1    TitleN  1.22  2.33  3.90 -1.56

and

Table2
id   title   D0    D1    D2    D3
------------------------------------
1    Title1  1.42 -0.93 -2.99  3.22
1    Title1  0.52  3.32 -4.90  0.54
1    Title1  2.13  1.14  3.50 -0.22
...
1    TitleN  3.42  4.37  3.90 -1.26

I am trying to figure out how to do a query like can do this math:

我试图弄清楚如何做一个像这样的数学查询:

SELECT title FROM Table2 WHERE (Table1_Row1_D0*Table2_Row1_D0)+(Table1_Row1_D1*Table2_Row1_D1)+(Table1_Row1_D2*Table2_Row1_D2) < 0.5;

However, I would like the query to iterate through the rows of Table1 and perform the SELECT against the entire Table2. Basically, I want to select the titles from Table2 where the calculation inequality is met against ALL the row combination of Table1 and Table 2.

但是,我希望查询迭代Table1的行并对整个Table2执行SELECT。基本上,我想从表2中选择表2中的标题,其中计算不等式与表1和表2的所有行组合相对应。

Is this possible???

这可能吗???

Not sure it matters, but I am using Postgre.

不确定是否重要,但我正在使用Postgre。

3 个解决方案

#1

You'll want a CROSS JOIN

你需要一个CROSS JOIN

SELECT Table2.title 
FROM Table2 
CROSS JOIN Table1
WHERE (Table1.D0*Table2.D0)+(Table1.D1*Table2.D1)+(Table1.D2*Table2.D2) < 0.5;

#2

Basically, I want to select the titles from Table2 where the calculation inequality is met against ALL the row combination of Table1 and Table 2.

基本上,我想从表2中选择表2中的标题,其中计算不等式与表1和表2的所有行组合相对应。

For that you will want the reverse condition, where there does NOT exist an equality in Table1 for that Table2 row.

为此,您将需要反向条件,其中表1中的Table2行不存在相等的情况。

SELECT distinct title
FROM Table2
WHERE NOT EXISTS (
    SELECT *
    FROM Table1
    WHERE (Table1.D0*Table2.D0)+(Table1.D1*Table2.D1)
            +(Table1.D2*Table2.D2) >= 0.5
)

#3

You should be using a union. The only caveat is your returning fields from your selects must match

你应该使用工会。唯一需要注意的是,您选择的返回字段必须匹配

 (SELECT * FROM Table1 WHERE conditions) UNION (SELECT * FROM Table2 WHERE conditions)

Do your checks on the script side as long as your not pulling up too much data. You also have the option to add sub selects to the where condition to limit both sides of this union query.

只要您没有提取太多数据,就可以在脚本端进行检查。您还可以选择将子选择添加到where条件以限制此联合查询的两侧。

#1