来自两个表的 MySQL 评级

Question

我正在使用 MySQL 和 PHP 为 Flash 游戏构建数据层。检索关卡列表非常简单，但我在尝试获取关卡的平均评分及其指针信息时遇到了障碍。这是一个示例数据集:

级别表:

level_id | level_name
       1 | Some Level
       2 | Second Level
       3 | Third Level

评分表:

rating_id | level_id | rating_value
        1 |    1     |      3
        2 |    1     |      4
        3 |    1     |      1
        4 |    2     |      3
        5 |    2     |      4
        6 |    2     |      1
        7 |    3     |      3
        8 |    3     |      4
        9 |    3     |      1

我知道这需要一个连接，但是当我请求级别列表时，我无法弄清楚如何根据 level_id 获取平均评级值。这就是我想要做的:

SELECT levels.level_id, AVG(ratings.level_rating WHERE levels.level_id = ratings.level_id) FROM levels

我知道我的 SQL 存在缺陷，但我不知道如何理解这个概念。我唯一可以开始工作的是从整个评级表中返回一个平均值，这不是很有用。

上述概念上有效但语法错误的查询的理想输出是:

level_id | level_rating
        1| 3.34
        2| 1.00
        3| 4.54 

我的主要问题是在返回查询之前，我不知道如何使用每个响应行的 level_id。就像我想使用占位符……或别名……我真的不知道，这很令人沮丧。我现在采用的解决方案是 EPIC 创可贴，从长远来看只会给我带来问题……请帮忙!

Answer 1

如果您的 levels 表是 InnoDB:

SELECT  l.level_id, COALESCE(AVG(level_rating), 0)
FROM    levels l
LEFT JOIN
        ratings r
GROUP BY
        l.id

如果您的levels 表是MyISAM:

SELECT  l.level_id,
        (
        SELECT  COALESCE(AVG(level_rating), 0)
        FROM    rating r
        WHERE   r.level_id = l.level_id
        )
FROM    levels l

子查询对于 MyISAM 更有效，因为它是基于堆的，并且 GROUP BY 需要排序和/或物化。