MYSQL join - 从嵌套选择引用外部字段?

发布于 2024-09-05 06:10:06 字数 389 浏览 2 评论 0原文

是否允许从嵌套选择引用外部字段?

例如,

SELECT
FROM ext1
LEFT JOIN (SELECT * FROM int2 WHERE int2.id = ext1.some_id ) as x ON 1=1

在本例中,这是在嵌套选择中引用 ext1.some_id。 在这种情况下,我收到错误,字段 ext1.some_id 未知。 是否可以?还有其他办法吗?

更新:

不幸的是,我必须使用嵌套选择,因为我要向其添加更多条件,例如 LIMIT 0,1 然后我需要在同一个表上使用 LIMIT 1,1 进行第二次连接(以连接另一行) 最终目标是连接同一个表中的 2 行,就好像它们是两个表一样 所以我打算将一些相关的行“分散”成一长行。

Is it allowed to reference external field from nested select?

E.g.

SELECT
FROM ext1
LEFT JOIN (SELECT * FROM int2 WHERE int2.id = ext1.some_id ) as x ON 1=1

in this case, this is referencing ext1.some_id in nested select.
I am getting errors in this case that field ext1.some_id is unknow.
Is it possible? Is there some other way?

UPDATE:

Unfortunately, I have to use nested select, since I am going to add more conditions to it, such as LIMIT 0,1
and then I need to use a second join on the same table with LIMIT 1,1 (to join another row)
The ultimate goal is to join 2 rows from the same table as if these were two tables
So I am kind of going to "spread" a few related rows into one long row.

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

扫码二维码加入Web技术交流群

发布评论

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

评论(3

许久 2024-09-12 06:10:07

您最初问题的答案是:不,删除子查询并将条件放入 ON 子句中:

SELECT *
FROM ext1
LEFT JOIN int2 ON ( int2.id = ext1.some_id )

一种解决方案可能是使用变量来查找第一(或第二)行,但是该解决方案无法有效地处理索引,因此您最终可能会遇到性能问题。

SELECT ext1.some_id, int2x.order_col, int2x.something_else
FROM ext1
LEFT JOIN (SELECT `int2`.*, @i:=IF(@id=(@id:=id), @i+1, 0) As rank
           FROM `int2`,
           ( SELECT @i:=0, @id:=-1 ) v
              ORDER BY id, order_col ) AS int2x ON (     int2x.id = ext1.some_id
                                                             AND int2x.rank = 0 )
;

假设您有一列要按 (order_col) 排序,并且左连接每个 some_id 的第一行。

The answer to your initial question is: No, remove your sub-query and put the condition into the ON-clause:

SELECT *
FROM ext1
LEFT JOIN int2 ON ( int2.id = ext1.some_id )

One solution could be to use variables to find the first (or second) row, but this solution would not work efficiently with indexes, so you might end up with performance problems.

SELECT ext1.some_id, int2x.order_col, int2x.something_else
FROM ext1
LEFT JOIN (SELECT `int2`.*, @i:=IF(@id=(@id:=id), @i+1, 0) As rank
           FROM `int2`,
           ( SELECT @i:=0, @id:=-1 ) v
              ORDER BY id, order_col ) AS int2x ON (     int2x.id = ext1.some_id
                                                             AND int2x.rank = 0 )
;

This assumes that you have a column that you want to order by (order_col) and Left Joins the first row per some_id.

£烟消云散 2024-09-12 06:10:07

你是这个意思吗?

SELECT ...
FROM ext1
LEFT JOIN int2 ON int2.id=ext1.some_id

Do you mean this?

SELECT ...
FROM ext1
LEFT JOIN int2 ON int2.id=ext1.some_id
入怼 2024-09-12 06:10:07

这就是 ON 子句的用途:

SELECT
FROM ext1
LEFT JOIN int2 AS x ON x.id = ext1.some_id

That's what the ON clause is for:

SELECT
FROM ext1
LEFT JOIN int2 AS x ON x.id = ext1.some_id
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文