MySQL子查询引用父查询中的字段

Question

我正在构建一个查询，对评级数据执行一些过滤。

假设我有一个简单的表称为ratings像下面，从网上的评价工具，存储数据：

 
+----------------+----------------+--------+ 
| page_title  | timestamp  | rating | 
+----------------+----------------+--------+ 
| Abc   | 20110417092134 | 1  | 
| Abc   | 20110418110831 | 2  | 
| Def   | 20110417092205 | 3  | 
+----------------+----------------+--------+ 

我需要提取与最新的10条点评低值高频的页面，请将此查询限制为在前一周产生至少20个评分的网页。这是 可笑长 查询我想出了：

SELECT a1.page_title, COUNT(*) AS rvol, AVG(a1.rating) AS theavg, 
(
    SELECT COUNT(*) FROM 
    (
     SELECT * FROM ratings a2 WHERE a2.page_title = a1.page_title 
     AND DATE(timestamp) <= '2011-04-24' ORDER BY timestamp DESC LIMIT 10 
    ) 
    AS latest WHERE rating >=1 AND rating <=2 ORDER BY timestamp DESC 
) 
AS lowest FROM ratings a1 
WHERE DATE(a1.timestamp) <= "2011-04-24" AND DATE(a1.timestamp) >= "2011-04-17" 
GROUP BY a1.page_title HAVING COUNT(*) > 20 

顶级查询查找在本周终止20余个评级2011-04-24网页，子查询应该检索来自顶级查询中每篇文章的最新10条评级的值为[1,2]之间的评级数。

MySQL抱怨子查询的WHERE子句中的a1.page_title是未知列，我怀疑这是因为a1没有被定义为第二级查询中的别名，而只是在顶级查询中，但我无能为力如何解决这个问题。

（编辑）

我加入我的犯罪嫌疑人上面关于跨级引用另一个查询其作品精绝的说明，注意这里A1没有在子查询定义，但它是在直接父：

SELECT a1.page_title, COUNT(*) AS rvol, AVG(a1.rating) AS theavg, 
(
    SELECT COUNT(*) FROM ratings a2 WHERE DATE(timestamp) <= '2011-04-24' 
    AND DATE(timestamp) >= '2011-04-17' AND rating >=1 
    AND rating <=2 AND a2.page_title = a1.page_title 
) AS lowest FROM ratings a1 
WHERE DATE(a1.timestamp) <= '2011-04-17' AND DATE(a1.aa_timestamp) >= '2011-04-11' 
GROUP BY a1.page_title HAVING COUNT(*) > 20 

Answer 1

我想你可能会考虑在连线两种观点可能使事情eaiser。

SELECT * 
FROM (SELECT COUNT(*), 
       a2.page_title 
     FROM ratings a2 
     WHERE DATE(timestamp) <= '2011-04-24' 
       AND DATE(timestamp) >= '2011-04-17' 
       AND rating >= 1 
       AND rating <= 2 

     GROUP BY a2.page_title) current 
     JOIN 
     (SELECT a1.page_title, 
        COUNT(*)  AS rvol, 
        AVG(a1.rating) AS theavg 
      FROM ratings a1 
      WHERE DATE(a1.timestamp) <= '2011-04-17' 
        AND DATE(a1.a_timestamp) >= '2011-04-11' 
      GROUP BY a1.page_title 
      HAVING COUNT(*) > 20) morethan20 
     ON current .page_title = morethan20.page_title 

Answer 2

如果你已经是这一个简单的表，我不知道你在哪里拉所有这些其他的表名而来，如：A1，A2，评级。我觉得你的SQL有点不合适，或者你的信息丢失了。

你做错误的原因是因为在你的子子查询中，你不在你的“FROM”语句中包含a1 ......因为这个表没有被包含，它不能被你的WHERE引用该子查询中的子句。

SELECT * 
FROM 
    (SELECT * 
     FROM a1 
     WHERE a1.timestamp <= (NOW()-604800) 
      AND a1.timestamp >= (NOW()-1209600) 
     GROUP BY a1.page_title 
     HAVING COUNT(a1.page_title)>20) 
    AS priorWeekCount 
WHERE 
    rating <= 2 
ORDER BY timestamp DESC 
LIMIT 10 

，因为我没有一个完整的表来测试这个......我觉得这是你找什么..但它是未经测试，并且知道我的编码习惯，很少是我输入100％的完美第一次;）

Answer 3

您对错误的分析是正确的：在子查询中已知lowest，a1不是。

我认为逻辑是从内到外的。以下可能不是最好的，但优化程序可能足够聪明，可以将最外面的SELECT中的两个子查询组合起来。 （如果不是，在可读性的风险，您可以引入子查询的另一个层次。）

SELECT r20plus.page_title, 
AVG((SELECT rating 
     FROM ratings r WHERE r.page_title=r20plus.page_title 
     ORDER BY timestamp DESC LIMIT 10)) as av, 
SUM((SELECT CASE WHEN rating BETWEEN 1 AND 2 THEN 1 ELSE 0 END 
     FROM ratings r WHERE r.page_title=r20plus.page_title 
     ORDER BY timestamp DESC LIMIT 10)) as n_low, 
FROM 
(SELECT page_title FROM ratings 
WHERE DATE(a1.timestamp) <= "2011-04-24" AND DATE(a1.timestamp) >= "2011-04-17" 
GROUP BY page_title 
HAVING COUNT(rating) >= 20) AS r20plus;