mysql 查询 - 使用左连接和 where 子句进行多次计数

Question

我目前正在尝试获取以下数据:

用户名、用户图像URL、玩过的游戏总数、已完成的游戏、输掉的游戏、平均获胜(百分比)和用户积分

还有另一组数据:

用户统计数据，例如:

• 联盟中玩的最多游戏:23 - 怪物 killer
• 获胜最多的游戏:19/23 - Monster Killers
• 最失败的游戏:3/32 - Frog Racers
• 您的游戏获胜准确度(所有游戏的总和)- 68% 准确度

网站统计:

• 联赛中进行的比赛最多:650 - 直升机奔跑
• 玩过的热门游戏:1200 - Monster Killers
• 全站获胜准确率:82%

我有以下表格:

-用户表-

用户ID(int-pk)，用户名(varchar)，userImageUrl(文本)

-游戏 table -

gameId (int-pk)、gameName (varchar)、gameUserID (int)、gameLeagueId (int)、score1 (int)、score2 (int)、gameResultOut(0 或 1)、gameWon(0 或 1)

-用户余额表-

ubId(int-pk) userId (int) 余额 (int)

-联赛表-leagueId (int-pk) leagueName (varchar)

只是为了让您了解正在发生的情况，当用户玩游戏并选择一些结果时，会将一行插入到游戏表中。由于游戏是基于时间的，当结果出来时，会检查是否有任何具有该 id 的游戏，并根据用户选择的内容将 gameResultOut 更新为 1，将 gameWon 更新为 1 或 0得分。

我尝试了以下方法:

SELECT u.userID, u.userName, u.userImageUrl, l.leagueName , 
COUNT(g.gameId) AS predTotal, 
(SELECT COUNT(g.gameId) FROM games AS g WHERE g.gameResultOut = 1 AND g.gameWon = 1) AS gamesWon,
(SELECT COUNT(g.gameId) FROM games AS g WHERE g.gameResultOut = 1 AND g.gameWon = 0) AS gamesLost, 
ub.balance 
FROM games AS g
LEFT JOIN league AS l ON l.leagueId = g.gameLeagueId 
LEFT JOIN user AS u ON u.user_id = g.gameUserID 
LEFT JOIN user_balance AS ub ON ub.userId = u.userID 
WHERE l.leagueId = 4
GROUP BY u.userId 
ORDER BY ub.balance DESC

我可以在查询后轻松计算获胜百分比，所以这不是问题，但是胜利和失败的结果都是相同的，即使更改leageId，结果仍然相同，这不是我想要什么。

有人可以帮忙吗？

谢谢和问候，死灵

Answer 1

据我所知，游戏表存储用户玩过的游戏。因此，为了了解每个用户玩/赢/输了多少场比赛，您缺少游戏和用户.

您的子查询是:

(SELECT COUNT(g.gameId ) FROM games AS g WHERE g.gameResultOut = 1 AND g.gameWon = 1) AS gamesWon,
(SELECT COUNT(g.gameId) FROM games AS g WHERE g.gameResultOut = 1 AND g.gameWon = 0) AS gamesLost, 

它们应该是:

(SELECT COUNT(gw.gameId ) FROM games AS gw WHERE gw.gameResultOut = 1 AND gw.gameWon = 1 AND gw.gameUserID = u.user_id) AS gamesWon,
(SELECT COUNT(gl.gameId) FROM games AS gl WHERE gl.gameResultOut = 1 AND gl.gameWon = 0 AND gl.gameUserID = u.user_id) AS gamesLost, 

我想这就是你要找的:)

<小时/>

编辑基于评论，添加用户和网站统计提示:

对于这些信息，您需要执行多个不同的查询，因为大多数查询都会对某些值求和和/或按给定列进行分组，这不适合另一个查询。我会尽力为您提供一些想法，以便您可以实现。

用户统计

大多数比赛获胜或失败

您提供的查询的先前答案计算了用户输/赢任何游戏的次次数，但没有区分游戏之间的数据。

因此，如果您想知道在哪个游戏中用户获胜/失败最多，您应该得到如下内容:

SELECT
    g.gameName,
    -- How many times the user won per game
    (SELECT COUNT(gw.gameId) FROM games gw WHERE gw.gameResultOut = 1 AND gw.gameWon = 1 AND  gw.gameUserID = u.user_id) AS gamesWon,
    -- How many times the user payed each game
    COUNT(g.gameId) AS gamesPlayed,
    -- The Win Ratio. This may need a little work on, depending on what you want.
    -- Be aware that if a user played a game 1 time and won, it's ratio will be 1 (100%)
    -- So maybe you'll want to add your own rule to determine which game should show up here
    (gamesWon / gamesPlayed) AS winRatio
FROM
    games g
    INNER JOIN user u ON u.user_id = g.gameUserID 
-- Groups and counts data based on games + users
GROUP BY g.gameId, u.user_id
-- Now you order by the win ratio
ORDER BY winRatio DESC
-- And get only the first result, which means the game the player has most wins.
LIMIT 1

对于输掉的比赛，它几乎是相同的查询，只是更改了所需的字段和数学。

游戏获胜准确率

与之前的查询类似，只是您不再按游戏 ID 进行分组。只需按用户分组并进行数学计算即可。

网站统计

嗯，据我所知，我们仍在进行类似的查询。不同之处在于，对于整个网站统计数据，您永远不会按用户分组。您可以按比赛或联赛进行分组，具体取决于您想要实现的目标。

<小时/>

底线:看起来大多数查询都是相似的，您必须使用它们并适应您需要检索的每个信息。 请注意，它们可能无法正常工作，因为我无法在您的数据库上测试它们。您可能需要根据您的数据库/表架构纠正一些不一致的情况。

我希望这能给您一些工作上的见解。