MySQL 在子查询中引用外部查询的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2645485/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Referencing outer query's tables in a subquery
提问by soulmerge
Is it possible to reference an outer query in a subquery with MySQL? I know there are somecases where this is possible:
是否可以在 MySQL 的子查询中引用外部查询？我知道在某些情况下这是可能的：
SELECT *
FROM table t1
WHERE t1.date = (
SELECT MAX(date)
FROM table t2
WHERE t2.id = t1.id
);
But I'm wondering if something like this could work:
但我想知道这样的事情是否可行：
SELECT u.username, c._postCount
FROM User u
INNER JOIN (
SELECT p.user, COUNT(*) AS _postCount
FROM Posting p
--# This is the reference I would need:
WHERE p.user = u.id
GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';
I know I could achieve the same using a GROUP BYor by pulling the outer WHEREclause into the sub-query, but I need this for automatic SQL generation and cannot use either alternative for various other reasons.
我知道我可以使用 aGROUP BY或通过将外部WHERE子句拉入子查询来实现相同的效果，但是我需要它来自动生成 SQL，并且由于其他各种原因不能使用任何一种替代方法。
UPDATE: Sorry, the question led to some confusion: The first query is just a working example, to demonstrate what I don'tneed.
UPDATE：对不起，导致了一些混乱的问题：第一个查询仅仅是一个工作例子，来证明什么，我也不需要。
UPDATE 2: I need both u.id = p.user comparisons: The first counts users that joined before '2009-10-10', while the other one is a join condition that associates table rows correctly.
更新 2：我需要两个 u.id = p.user 比较：第一个计数在 '2009-10-10' 之前加入的用户，而另一个是正确关联表行的连接条件。
采纳答案by chris
i think that won't work, because you're referencing your derived table 'c' as part of a join.
我认为这行不通，因为您将派生表“c”作为连接的一部分进行引用。
however, you could just take out the WHERE p.user = u.idthough and replace with a GROUP BY p.userin the derived table, because the ON c.user = u.idwill have the same effect.
但是，您可以取出WHERE p.user = u.id虽然并替换GROUP BY p.user为派生表中的 a，因为这ON c.user = u.id将具有相同的效果。
回答by Jeremy
Isn't this what you're after?
这不是你追求的吗？
SELECT u.username, c._postCount
FROM User u
INNER JOIN (
SELECT p.user, COUNT(*) AS _postCount
FROM Posting p
GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';
The reason this will work is that the nature of the join itself will filter on user. You don't need to have a WHERE clause explictly filtering on user.
这将起作用的原因是连接本身的性质将过滤用户。您不需要对用户进行显式过滤的 WHERE 子句。
回答by Shawn Kelly
This is probably better:
这可能更好：
SELECT u.username,
(SELECT COUNT(*) FROM Posting WHERE user = u.id) as _postCount
FROM User u WHERE u.joinDate < '2009-10-10';
回答by Rajat Bhatnagar
This solution is for postgresql. You could use LATERAL JOINwhich is available in postgresql. Here is how you could use it in your query.
此解决方案适用于 postgresql。您可以使用postgresql 中提供的LATERAL JOIN。这是您在查询中使用它的方法。
SELECT u.username, c._postCount
FROM User u
INNER JOIN LATERAL (
SELECT p.user, COUNT(*) AS _postCount
FROM Posting p
WHERE p.user = u.id
GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';
Here is a reference you could use. https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd0199df
这是您可以使用的参考。https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd0199df
