php - 计算 Average 和 LEFT JOIN 表

Question

我正在尝试将基于 postid 的 rating 列的平均值与另一个表连接起来。

我创建了这段代码来计算 rating 平均值并且它有效:

SELECT post_rating.postid, 
       AVG(post_rating.rating) 
FROM post_rating 
GROUP BY post_rating.postid"

但是当我尝试加入时它不起作用，如下所示:

$join = ' LEFT JOIN ('SELECT post_rating.postid, 
                      AVG(post_rating.rating) 
                      FROM post_rating 
                      GROUP BY post_rating.postid') AS post_rating 
            ON post_rating.postid=theList.id';

我如何加入此代码或有更好的方法吗？谢谢!

我使用的全部代码:

<?php

$pdo = new PDO('mysql:host=localhost;dbname=myDB', 'root', 'root');
$select = 'SELECT theList.id, theList.name, post_rating.rating, post_rating.postid';
$from = ' FROM theList';
$where = ' WHERE TRUE';
$join = ' LEFT JOIN ('SELECT post_rating.postid, AVG(post_rating.rating) FROM post_rating GROUP BY post_rating.postid') AS post_rating ON post_rating.postid=theList.id';
$opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
if (in_array("pub", $opts)){
$where .= " AND pub = 1";
}
if (in_array("bar", $opts)){
$where .= " AND bar = 1";
}
if (in_array("restaurant", $opts)){
$where .= " AND restaurant = 1";
}
if (in_array("club", $opts)){
$where .= " AND club = 1";
}

$sql = $select . $from . $join . $where;
$statement = $pdo->prepare($sql);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo($json);
?>

这是我的表格的简化版本:

postid 表

id | postid | rating
--------------------
1  |    3   |    5
2  |    3   |    4
3  |    1   |    3

list 表

id | name 
----------
1  | name1   
2  | name2   
3  | name3

我想输出的是:

id | name  | rating
------------------
1  | name1 |   3
2  | name2 |   0
3  | name3 |   4.5

Answer 1

我认为您正在以不必要的复杂方式处理此查询。只需在表之间执行 Left Join，将 theList 视为 Leftmost 表，并计算 Avg()在 postid 的分组中:

SELECT tl.id, 
       tl.name, 
       AVG(pr.rating) AS rating 
FROM post_rating AS pr 
LEFT JOIN theList AS tl ON tl.id = pr.postid 
GROUP BY tl.id, tl.name  

您的 PHP 代码将如下所示:

<?php 
$pdo = new PDO('mysql:host=localhost;dbname=myDB', 'root', 'root');
$select = 'SELECT tl.id, 
                  tl.name, 
                  AVG(pr.rating) AS rating ';

$from = ' FROM post_rating AS pr ';
$where = ' WHERE 1 = 1 ';
$join = ' LEFT JOIN theList AS tl ON tl.id = pr.postid ';
$opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
if (in_array("pub", $opts)){
    $where .= " AND pub = 1";
}
if (in_array("bar", $opts)){
    $where .= " AND bar = 1";
}
if (in_array("restaurant", $opts)){
    $where .= " AND restaurant = 1";
}
if (in_array("club", $opts)){
    $where .= " AND club = 1";
}

$group = ' GROUP BY tl.id, tl.name ';

$sql = $select . $from . $join . $where . $group;
$statement = $pdo->prepare($sql);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo($json);
?>