计算平均连接表和左连接表

jmp7cifd 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(288)

我想加入 rating 基于 postid 另一张table。
我已经创建了这个代码来计算 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);
?>

以下是我的表格的简化版本：
过帐表

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

mysql php pdo

来源：https://stackoverflow.com/questions/52632736/working-out-the-average-and-left-join-tables

2条答案

按热度按时间

kqlmhetl1#

我认为你正在以一种不必要的复杂方式处理这个问题。简单地做 Left Join 在table之间，考虑一下 theList 作为最左边的表，并计算 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);
?>

赞(0）回复(0）举报 2021-06-21

qvtsj1bj2#

现在我看到你在之前的问题评论中试图告诉我的问题，你已经结束了 $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);

赞(0）回复(0）举报 2021-06-20

我来回答

计算平均连接表和左连接表

2条答案

相关问题

热门标签

最新问答