mysql - 相关子查询 "unknown column"

Question

我在以下查询的“where 子句”中得到一个未知列“sites.id”:

SELECT id, COUNT( returning_visitors.per_ip ) as readers, AVG( returning_visitors.per_ip ) as avg_visits_pr
FROM sites
JOIN (
    SELECT COUNT( * ) AS per_ip
    FROM site_hits_unique
    WHERE site_id = sites.id
    AND date >= CURDATE( ) - INTERVAL 30 DAY
    GROUP BY site_id, ip
    HAVING per_ip > 1
) AS returning_visitors
WHERE id IN (162888, 42705, 11412)

我想为每个 sites.id 运行内部查询(该示例仅使用一些 ID 用于测试目的)。

相关子查询只有一层深，所以我不太清楚为什么它没有得到 sites.id。

有什么解决办法吗？

Answer 1

我从http://dev.mysql.com/doc/refman/5.6/en/subquery-restrictions.html找到了原因:

Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) during query execution, so they cannot be evaluated per row of the outer query. Before MySQL 5.6.3, materialization takes place before evaluation of the outer query. As of 5.6.3, the optimizer delays materialization until the result is needed, which may permit materialization to be avoided. See Section 8.2.1.18.3, “Optimizing Derived Tables (Subqueries) in the FROM Clause”.

虽然我仍然需要弄清楚如何重写我的查询以使其按我希望的方式工作。这里的功能是否必要/可行？