时间戳

首页 > 解决方案 > 计算平均和左连接表

php - 计算平均和左连接表

问题描述

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

我创建了这段代码来计算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

列表表

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

我试图基本上输出的是：

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

标签： phpmysqlpdo

解决方案

现在我看到您在之前的问题评论中试图告诉我的问题，您$join出于某种原因结束了字符串，因此大部分查询都不会包含在内

所以这条线

$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';

（看代码颜色显示错误）

所以修改为

$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';

我可以建议您向 PDO 添加异常处理，以便在出现错误时在浏览器上得到通知。在您的连接代码中执行此操作

$pdo = new PDO(.......);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

推荐阅读