在子查询中引用外部查询的表 [英] Referencing outer query's tables in a subquery

问题描述

是否可以使用MySQL在子查询中引用外部查询?我知道在某些情况下[em] 是可能的:

SELECT *
FROM table t1
WHERE t1.date = (
    SELECT MAX(date)
    FROM table t2
    WHERE t2.id = t1.id
);

但是我想知道这样的事情是否可以工作:

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';

我知道我可以使用GROUP BY或通过将外部WHERE子句拉入子查询来实现相同的目的，但是我需要这样做来自动生成SQL，并且由于各种其他原因，不能使用任何一种替代方法. >

更新:抱歉，该问题引起了一些混乱:第一个查询只是一个有效的示例，用以说明我不需要的需求.

更新2 :我需要同时进行u.id = p.user比较:第一个比较的是在'2009-10-10'之前加入的用户，而另一个是关联的加入条件表行正确.

解决方案

我认为这行不通，因为您将派生表'c'引用为联接的一部分.

但是，您可以只取出WHERE p.user = u.id并在派生表中替换为GROUP BY p.user，因为ON c.user = u.id将具有相同的效果.

Is it possible to reference an outer query in a subquery with MySQL? I know there are some cases where this is possible:

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 BY or by pulling the outer WHERE clause into the sub-query, but I need this for automatic SQL generation and cannot use either alternative for various other reasons.

UPDATE: Sorry, the question led to some confusion: The first query is just a working example, to demonstrate what I don't need.

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.

解决方案

i think that won't work, because you're referencing your derived table 'c' as part of a join.

however, you could just take out the WHERE p.user = u.id though and replace with a GROUP BY p.user in the derived table, because the ON c.user = u.id will have the same effect.

这篇关于在子查询中引用外部查询的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！