mysql - 平均评分，评分存储在 2 列中

Question

我搜索了该站点，所有类似的答案都指的是单独的评级表，而不是这样的实现。

我已经将一个 wordpress 网站转换为一个用 PHP/MYSQL 自行构建的网站，想象一下实际的帖子是评论，而评论是与原始帖子相关的实际评论。

我遇到的问题是我有一个帖子表的评级字段和每个输入的评论的评级字段。

我正在尝试使用别名 AvgRating 作为一个别名，但是它只返回评论评级(c.rating)，如果删除该行它会从帖子表中带回评论评级，有没有办法加入别名作为平均评级？显然只有这部分是错误的，但不会抛出错误。

SELECT p.post_id
     , p.title
     , AVG(p.rating) AvgRating
     , AVG(c.rating) AvgRating -- returns average from comments field, if line is removed will return posts rating value instead..
  FROM posts p
  LEFT 
  JOIN comments c 
    ON p.post_id = c.post_id 
 WHERE p.post_id = $post_id
   AND c.post_id = $post_id
   AND active = 1  
 GROUP 
    BY p.post_id
     , p.title;

我正在尝试获取架构聚合计数标记的平均值。有没有一种方法可以在一个查询中实现这一点？或者我唯一的选择是使用 2 个查询返回 2 个平均值并使用 PHP 函数返回两者之间的平均值？

这是一个测试数据库:

CREATE TABLE `posts` (
`post_id` int(11) NOT NULL,
`cat_id` int(11) NOT NULL,
`rating` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`content` varchar(5000) NOT NULL,
`date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`author` varchar(255) NOT NULL,
`active` int(11) DEFAULT '0',
`slug` varchar(255) NOT NULL,
`user_submitted` int(11) NOT NULL DEFAULT '1'
) ENGINE=InnoDB AUTO_INCREMENT=2157 DEFAULT CHARSET=latin1;

CREATE TABLE `comments` (
`cmt_id` int(11) NOT NULL,
`post_id` int(11) NOT NULL,
`rating` int(11) NOT NULL,
`comment` varchar(5000) NOT NULL,
`cmt_author` varchar(255) NOT NULL,
`cmt_email` varchar(255) NOT NULL,
`ip_address` varchar(100) NOT NULL,
`cmt_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`cmt_status` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=397 DEFAULT CHARSET=latin1;

INSERT INTO `posts` (`post_id`, `cat_id`, `rating`, `title`, `content`, `date_added`, `author`, `active`, `slug`, `user_submitted`) VALUES
(2, 9, 5, 'site1.com', 'Original post review body', '2010-05-07 05:00:00', '', 1, 'site1-com', 1);

INSERT INTO `comments` (`cmt_id`, `post_id`, `rating`, `comment`, `cmt_author`, `cmt_email`, `ip_address`, `cmt_date`, `cmt_status`) VALUES
(1, 2, 5, 'it is a good site test review', 'Anonymous', '', '', '2010-01-06 21:51:00', 1),
(2, 2, 3, 'it is an average site test review', 'Anonymous', '', '', '2010-01-06 20:51:00', 1),
(3, 2, 1, 'it is an bad site test review', 'Anonymous', '', '', '2010-01-06 23:51:00', 1),
(4, 2, 0, 'neutral test review', 'Anonymous', '', '', '2010-01-06 22:51:00', 1),
(5, 2, 2, 'below average test review', 'Anonymous', '', '', '2010-01-06 22:51:00', 1);

要运行的查询:

SELECT p.post_id
 , p.title
 , AVG(p.rating) AvgRating
 , AVG(c.rating) AvgRating -- returns average from comments field, if line is removed will return posts rating value instead..
FROM posts p
LEFT 
JOIN comments c 
ON p.post_id = c.post_id 
WHERE p.post_id = 2
AND active = 1  
GROUP 
BY p.post_id
 , p.title;

在测试数据库上的 PHPmyadmin 中运行此命令返回:AvgRating:当前选择不包含唯一列。网格编辑、复选框、编辑、复制和删除功能不可用

(这是意料之中的，因为我错误地认为它会将两者的平均值放入别名列 avgRating 的一个实例中)

结果:

post_id  title      AvgRating  AvgRating
   2     site1.com  5.0000     2.2000 

所以这在某种意义上是正确的，但是如何将原始帖子评分 5.000 计入评论评分 2.2000？

所需的结果应该是 2.6667 而不是 2.2000，因为 (5+5+3+1+0+2)/6 = 2.6667。 2.2000 只是评论评分的平均值，并没有考虑原始帖子评分 5.000

Answer 1

一种方法是使用 UNION...

SELECT post_id
     , AVG(rating) avg_rating
  FROM 
     ( SELECT post_id
            , rating FROM posts
        UNION 
          ALL
       SELECT post_id
            , rating 
         FROM comments
     ) x 
 GROUP 
    BY post_id;

http://sqlfiddle.com/#!9/ad65d/14