SQL 交叉连接问题

发布于 2024-10-19 01:53:56 字数 1012 浏览 2 评论 0原文

我的 SQL 查询遇到了一些问题。

我有两个表：

表 1

id  guid  title     D0      D1     D2
-----------------------------------------
1   guid1 Title1    0.123  -0.235  0.789
2   guid2 Title2   -0.343   0.435  0.459
3   guid3 Title3    0.243  -0.267 -0.934
...
100 guid4 Title100 -0.423   0.955  0.029

和表 2（请注意，它具有相同的架构，只是数据不同）。

id  guid  title     D0      D1     D2
----------------------------------------
1   guid1 Title1    0.233  -0.436 -0.389
2   guid2 Title2   -0.343   0.235  0.789
3   guid3 Title3    0.573  -0.067 -0.124
...
100 guid4 Title100 -0.343   0.155  0.005

我试图弄清楚如何编写一个 SELECT 语句，该语句返回所有标题 WHERE ABS(Table1_D0*Table2_D0)+ABS(Table1_D1* Table2_D1)+ABS(Table1_D2*Table2_D2) 小于阈值（可能是硬编码）。

到目前为止，我正在尝试使用CROSS JOIN，但我不确定这是否是正确的方法。

这有道理吗？ Table1, row1 对应于 Table2 的所有行，然后 Table1, row2 对应于 Table2 的所有行。

如果重要的话，我正在使用 MS SQL。

非常感谢！布雷特

原文

I am having a bit of trouble with my SQL query.

I have two tables:

Table1

id  guid  title     D0      D1     D2
-----------------------------------------
1   guid1 Title1    0.123  -0.235  0.789
2   guid2 Title2   -0.343   0.435  0.459
3   guid3 Title3    0.243  -0.267 -0.934
...
100 guid4 Title100 -0.423   0.955  0.029

and Table 2 (note it has the same schema, just different data).

id  guid  title     D0      D1     D2
----------------------------------------
1   guid1 Title1    0.233  -0.436 -0.389
2   guid2 Title2   -0.343   0.235  0.789
3   guid3 Title3    0.573  -0.067 -0.124
...
100 guid4 Title100 -0.343   0.155  0.005

I am trying to figure out how to write a SELECT statement which returns all the titles WHERE all the combinations of ABS(Table1_D0*Table2_D0)+ABS(Table1_D1*Table2_D1)+ABS(Table1_D2*Table2_D2) are less than a thresholded value (hard coded probably).

So far I am trying to use a CROSS JOIN, but I am not sure if this is the correct approach.

Does this make sense? Table1, row1 against all the rows of Table2, then Table1, row2 against all the rows of Table2.

If it matters, I am using MS SQL.

Many thanks!
Brett

分享到QQ

分享到微博

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

发布评论

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

倾城泪 2024-10-26 01:53:56

SELECT t1.title
FROM Table1 t1
CROSS JOIN table2 t2
WHERE ABS(t1.D0*t2.D0)+ABS(t1.D1*t2.D1)+ABS(t1.D2*t2.D2)<10

SELECT t1.title
FROM Table1 t1
CROSS JOIN table2 t2
WHERE ABS(t1.D0*t2.D0)+ABS(t1.D1*t2.D1)+ABS(t1.D2*t2.D2)<10

回复收藏 0 原文

悲喜皆因你 2024-10-26 01:53:56

SELECT *
  FROM  Table1 a inner join
        Table2 b on a.Id=b.Id
where   ABS(a.D0*b.D0)+ABS(a.D1*b.D1)+ABS(a.D2*b.D2)<=@Value

SELECT *
  FROM  Table1 a inner join
        Table2 b on a.Id=b.Id
where   ABS(a.D0*b.D0)+ABS(a.D1*b.D1)+ABS(a.D2*b.D2)<=@Value

回复收藏 0 原文

弥枳 2024-10-26 01:53:56

CROSS JOIN 是正确的选择，CROSS JOIN 与根本没有连接相同（请参阅 Snowbear 答案）。

回复收藏 0 原文

百善笑为先 2024-10-26 01:53:56

select t1o.title
from Table1 t1o
where not exists
(
    -- none of the cross-joined rows for t1o must be above the threshold
    select t1.title
    from Table1 t1
    cross join Table2 t2
    where t1.id = t1o.id -- only the cross joined rows for the current t1o row
    -- inverted b/c not exists
    and abs(t1.D0*t2.D0)+abs(t1.D1*t2.D1)+abs(t1.D2*t2.D2) > 10 
)

select t1o.title
from Table1 t1o
where not exists
(
    -- none of the cross-joined rows for t1o must be above the threshold
    select t1.title
    from Table1 t1
    cross join Table2 t2
    where t1.id = t1o.id -- only the cross joined rows for the current t1o row
    -- inverted b/c not exists
    and abs(t1.D0*t2.D0)+abs(t1.D1*t2.D1)+abs(t1.D2*t2.D2) > 10 
)

回复收藏 0 原文

~没有更多了~