mysql - 连接时不同表的平均值

Question

CREATE TABLE `reviews` (
  `id` int(11) NOT NULL,
  `average` decimal(11,2) NOT NULL,
  `house_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `reviews` (`id`, `average`, `house_id`) VALUES
(1, '10.00', 1),
(2, '10.00', 1);
ALTER TABLE `reviews`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `reviews`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

CREATE TABLE `dummy_reviews` (
  `id` int(11) NOT NULL,
  `average` decimal(11,2) NOT NULL,
  `house_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `dummy_reviews` (`id`, `average`, `house_id`) VALUES
(0, '2.00', 1);
ALTER TABLE `dummy_reviews`
  ADD PRIMARY KEY (`id`);

和查询

SELECT
  AVG(r.average) AS avg1,
  AVG(dr.average) AS avg2
FROM
  reviews r
LEFT JOIN
  dummy_reviews dr ON r.house_id = dr.house_id

结果是

avg1        avg2    
10.000000   2.000000

现在一切都很好但是 (10 + 2)/2 = 6 ... 错误的结果

我需要 (10+10+2)/3 = 7,33 ... 我怎样才能得到这个结果？

SQLFiddle

Answer 1

您有连接的值，因此您不会有 3 行，您将有 2 行。您需要的是一个联合，这样您就可以拥有平均表中的所有行并从中进行计算。像这样:

select avg(average) from
  (select average from reviews
   union all
   select average from dummy_reviews
  ) queries

在这里查看:http://sqlfiddle.com/#!9/e0b75f/3