选择返回 1 个结果而不是 3 个结果的查询，因为 AVG - Select query returning 1 result instead of 3 because of AVG

Select query returning 1 result instead of 3 because of AVG

下面的选择查询返回 1 行，而它应该是 3。我很确定这是因为AVG(k.sumtotal(字段。

如果我重写查询并取出 AVG(k.sumtotal( 列并取出 FROM inv_ratings AS k，我会得到我的 3 行。

我不知道为什么这个表(inv_ratings(或这个AVG(k.sumtotal(列将行数限制为1。我在网上看了几个小时，试图找到有关使用 AVG 子句返回结果的信息，但没有找到太多。我是否必须使用分组依据子句，我尝试过，只得到错误。

$p = "SELECT i.invention_id, i.inv_title, i.date_submitted, i.category_id,"
    . " i.approved, c.category_id, c.category, u.image_name, AVG(k.sumtotal)"
    . " FROM inv_ratings AS k INNER JOIN inventions AS i USING (invention_id)"
    . " INNER JOIN categories AS c USING (category_id)"
    . " INNER JOIN images AS u USING (invention_id)"
    . " WHERE c.category_id = $cat AND i.approved = 'approved'"
    . " HAVING u.image_name < 2"
    . " ORDER BY date_submitted"
    . " DESC LIMIT $start, $display";
$q = mysqli_query($dbc, $p) or trigger_error("Query: $p'n<br />mysqli Error: " . mysqli_error($dbc));

你遇到了MySQL的一个陷阱：

http://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html

我讨厌MySQL允许这种语法，因为它只会引起混乱。但是您可能想要的是(要使用MySQL的黑客行为，请注意，如果除了invention_id或总和之外的任何字段有多个值，则从该列中获得一个随机值(：

$p = "SELECT i.invention_id, i.inv_title, i.date_submitted, i.category_id,"
. " i.approved, c.category_id, c.category, u.image_name, AVG(k.sumtotal)"
. " FROM inv_ratings AS k INNER JOIN inventions AS i USING (invention_id)"
. " INNER JOIN categories AS c USING (category_id)"
. " INNER JOIN images AS u USING (invention_id)"
. " WHERE c.category_id = $cat AND i.approved = 'approved'"
. " GROUP BY i.invention_id "
. " HAVING u.image_name < 2"
. " ORDER BY date_submitted"
. " DESC LIMIT $start, $display";

或者，不使用MySQL的黑客行为：

$p = "SELECT i.invention_id, i.inv_title, i.date_submitted, i.category_id,"
. " i.approved, c.category_id, c.category, u.image_name, AVG(k.sumtotal)"
. " FROM inv_ratings AS k INNER JOIN inventions AS i USING (invention_id)"
. " INNER JOIN categories AS c USING (category_id)"
. " INNER JOIN images AS u USING (invention_id)"
. " WHERE c.category_id = $cat AND i.approved = 'approved'"
. " GROUP BY i.invention_id, i.inv_title, i.date_submitted, i.category_id,"
. " i.approved, c.category_id, c.category, u.image_name "
. " HAVING u.image_name < 2"
. " ORDER BY date_submitted"
. " DESC LIMIT $start, $display";