在查询中连接两个 SQL 表

发布于 2024-10-12 15:03:53 字数 892 浏览 0 评论 0原文

我有两个表（具有相同的架构）

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

，

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

我试图弄清楚如何执行一个查询，就像可以做这个数学运算：

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;

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

这可能吗？？？

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

原文

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.

Is this possible???

Not sure it matters, but I am using Postgre.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

°如果伤别离去 2024-10-19 15:03:53

基本上，我想选择标题
由表2计算得出
所有行都满足不等式
表1和表2的组合。

为此，您需要相反的条件，即表 1 中不存在与表 2 行相等的情况。

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
)

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.

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

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
)

回复收藏 0 原文

明媚殇 2024-10-19 15:03:53

你需要一个交叉连接

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

You'll want a 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;

回复收藏 0 原文

々眼睛长脚气 2024-10-19 15:03:53

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

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

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

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.

回复收藏 0 原文

~没有更多了~