mysql - 找出 SQL 中的 AVG 列

Question

我有这个 php/sql 查询:

$result = mysql_query("
            SELECT r.item_id, AVG(rating) AS avgrating, count(rating) AS count, i.item, c.category
            FROM ratings AS r
            LEFT JOIN items AS i
            ON r.item_id = i.items_id
            INNER JOIN master_cat c
            ON c.cat_id = i.cat_id
            GROUP BY item_id 
            ORDER BY avgrating DESC 
            LIMIT 25;");

当我输出这个时，计数是正确的，它显示了某些项目收到了多少票。

我只是想添加一个 WHERE count >= 10 子句，但一切都中断了。显然，当有几千个项目时，有些会得到一票并拥有100％。但这不是一个好的指标。我想打印出至少有 10 票(或 count >= 10)的项目

Answer 1

你应该使用 having 而不是 where

SELECT 
    r.item_id, AVG(rating) AS avgrating, 
    count(rating) AS count, i.item, c.category
FROM 
    ratings AS r
    LEFT JOIN items AS i
        ON r.item_id = i.items_id
    INNER JOIN master_cat c
        ON c.cat_id = i.cat_id
GROUP BY 
    item_id 
HAVING
    count >= 10
ORDER BY 
    avgrating DESC 
LIMIT 25;