mysql - 添加左连接后结果减少

Question

我正在使用 MYSQL，但在获得我想要的预期结果时遇到一些问题。

我现在正在处理 4 个表...

matchopponents
--------------
opp1id
opp2id 
matchid

matches
-------------
id 
title

players
------------
id
playeralias

ratings 
------------
ratingid
ratingvalue
matchid

我需要一个查询，从 matchopponents 表中选择每一行，根据 opp1id 和 opp2id 区分玩家别名，从 matches 表中获取比赛标题，然后对 matchopponent 中每场受尊敬的比赛的平均总评分进行四舍五入 table 。到目前为止，我的查询已接近预期结果，但它仅从 matchopponents 表返回一行。目前我在 matchopponents 表中有 2 行:

opp1id opp2id 匹配
2 3 114 5 12

以下查询返回

比赛标题 玩家 1 玩家 2 评级MarineKing vs Ryung MarineKing Ryung 3

这对于第一行来说非常有用...(opp1id 2，opp2id 3，matchid 11)

但是第二行没有返回任何内容...当我删除 LEFT JOIN 评级时，两者都正在显示。我已确保所有正确的身份证件均已就位且正确。:(

任何关于为什么会发生这种情况的帮助将不胜感激:)

SELECT 
m.title AS "Match Title",
p1.playeralias AS "Player 1",
p2.playeralias AS "Player 2",
ROUND(IFNULL(AVG(r.ratingvalue), 1)) AS "Rating"
FROM 
matchopponents AS MO
JOIN matches AS m ON mo.matchid = m.id
JOIN players AS p1 ON mo.opp1id = p1.id 
JOIN players AS p2 ON mo.opp2id = p2.id
LEFT JOIN ratings r
ON 
r.matchid = mo.matchid

Answer 1

您的原始查询缺少 GROUP BY 子句:

SELECT 
  m.title AS "Match Title",
  p1.playeralias AS "Player 1",
  p2.playeralias AS "Player 2",
  ROUND(IFNULL(AVG(r.ratingvalue), 1)) AS "Rating"
FROM  matchopponents AS MO
JOIN matches AS m ON mo.matchid = m.id
JOIN players AS p1 ON mo.opp1id = p1.id 
JOIN players AS p2 ON mo.opp2id = p2.id
LEFT JOIN ratings r
  ON r.matchid = mo.matchid
GROUP BY m.title, p1.playeraliasp2.playeralias;

由于您要聚合结果 (avg())，因此您需要聚合所有行。 MySQL 不同，因为它允许使用聚合函数而不包括要聚合或分组的选择列表中的其他列，但结果不一定是正确的。

将聚合函数与 GROUP BY 一起使用将确保您始终返回选择列表中其他列的预期值。当您不对 SELECT 列表中的项目进行 GROUP BY 或聚合时，可能会返回意外结果。 (参见MySQL Extensions to GROUP BY)

来自 MySQL 文档:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. ... You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.