将两个MySQL查询与计算相结合

Question

我有两个包含不同信息的表，但希望将它们合并到报告中作为一个表。

我可以设法将表与以下查询组合在一起：

SELECT `id`, `name`, `info`, `food`, `drinks`, `service`, `ambience`, `hygiene`, `recommend`, `best_menu`, `best_service`, `author`, `username` 
FROM `restaurants` RIGHT JOIN `rating` ON `rating`.`rid` IN (1,2,3);

但想要计算所有的评分，并在一个紧凑的视图中显示名称和信息，如下所示：

SELECT `rid` as `id`, COUNT(*) as `count`, AVG(`food`+`drinks`+`service`+`ambience`+`hygiene`) as `average`, SUM(`food`) as `food`, SUM(`drinks`) as `drinks`, SUM(`service`) as `service`, SUM(`ambience`) as `ambience`, SUM(`hygiene`) as `hygiene`, concat(round(( SUM(`recommend`)/COUNT(*) * 100 ),2),'%') as `recommended`, `best_menu` as `best`, `best_service` as `service`, `voted` as `last vote` 
FROM `rating` WHERE `rid` IN (3,1,2) GROUP BY `rid` ORDER BY `best_menu`, `best_service`;

所以我的目标是把第一个表中的"name“和"info”列放到第二个表中。

请看这里的示例：

http://sqlfiddle.com/#!9/d5589/35

非常感谢您的帮助！

Answer 1

在尝试之后，我终于可以构建正确的请求了。

SELECT `rid` as `id`, `name`, `info`, COUNT(*) as `count`, AVG(`food`+`drinks`+`service`+`ambience`+`hygiene`) as `average`, SUM(`food`) as `food`, SUM(`drinks`) as `drinks`, SUM(`service`) as `service`, SUM(`ambience`) as `ambience`, SUM(`hygiene`) as `hygiene`, concat(round(( SUM(`recommend`)/COUNT(*) * 100 ),2),'%') as `recommended`, `best_menu` as `best`, `best_service` as `service`, `voted` as `last vote` 
FROM `rating` JOIN `restaurants` ON `restaurants`.`id` = `rating`.`rid` WHERE `restaurants`.`id` IN (1,2,4) GROUP BY `rid` ORDER BY `best_menu`, `best_service`;

在这种情况下，我必须有所有餐厅的评级，否则他们将不会显示。我同意@Strawberry构建一个规范化的设计可以帮助避免这个问题。

Answer 2

虽然不是一个明确的解决方案，但此模式的标准化设计可能如下所示：

ratings_index
(rating_id*,rating_title,rating_type(integer,string));

users
(user_id*,username,email)

venues
(venue_id,name,address)

user_integer_ratings
(user_id*,venue_id*,rating_id*,rating)

user_string_ratings
(user_id*,venue_id*,rating_id*,rating)

* = (component of) PRIMARY KEY