mysql - 带条件的 SQL 子查询计数

Question

这是一个非常具体的问题。但在过去的几个小时里我一直在用头撞墙，所以我会尽力解释它。

假设我有:

• 用户
• 玩过的游戏(与用户关联，有创建日期)
• 徽章已解锁(与用户和游戏关联，有创建日期)

我正在寻找一个查询，该查询将列出为特定用户解锁的所有徽章以及该用户在解锁该徽章之前玩过多少游戏。

SELECT users.id, 
       games.id, 
       badge.id, 
       games.created game_created,
       users_badges.created AS badge_created,
       subquery.nb_games 
  FROM badges 
       INNER JOIN users_badges 
                  ON badges.id = users_badges.badge_id 
       INNER JOIN users 
                  ON users_badges.user_id = users.id 
       INNER JOIN games 
                  ON users.id = games.user_id 
       INNER JOIN ( SELECT user_id, 
                           created, 
                           COUNT(*) AS nb_games 
                      FROM games 
                    GROUP BY 
                           user_id
                    ) subquery 
                  ON subquery.user_id = users.id 
                  AND subquery.created <= users_badges.created
 WHERE users.id = 1

我的GROUP BY语句显然使我的加入条件无效，并且我最终总是得到所玩游戏的总数:

+----------+----------+----------+----------+---------------------+---------------------+
| users.id | games.id | badge.id | nb_games | game_created        | badge_created       |
+----------+----------+----------+----------+---------------------+---------------------+
| 1        | 1        | 1        | 3        | 2014-03-12 00:00:00 | 2014-03-12 00:00:00 |
+----------+----------+----------+----------+---------------------+---------------------+
| 1        | 2        | 2        | 3        | 2014-03-13 00:00:00 | 2014-03-13 00:00:00 |
+----------+----------+----------+----------+---------------------+---------------------+
| 1        | 3        | 3        | 3        | 2014-03-14 00:00:00 | 2014-03-14 00:00:00 |
+----------+----------+----------+----------+---------------------+---------------------+

虽然我期待一些更渐进的事情:

+----------+----------+----------+----------+---------------------+---------------------+
| users.id | games.id | badge.id | nb_games | game_created        | badge_created       |
+----------+----------+----------+----------+---------------------+---------------------+
| 1        | 1        | 1        | 1        | 2014-03-12 00:00:00 | 2014-03-12 00:00:00 |
+----------+----------+----------+----------+---------------------+---------------------+
| 1        | 2        | 2        | 2        | 2014-03-13 00:00:00 | 2014-03-13 00:00:00 |
+----------+----------+----------+----------+---------------------+---------------------+
| 1        | 3        | 3        | 3        | 2014-03-14 00:00:00 | 2014-03-14 00:00:00 |
+----------+----------+----------+----------+---------------------+---------------------+

这可能吗？

Answer 1

这可以在没有子查询的情况下实现。正如您的情况一样，您首先在子查询中计算每个用户的游戏数量(因此计算每个用户的总数)，完成此操作后，您将尝试检查游戏日期。

我还发现一个问题，您已链接游戏两次，如果您之前需要用户 - 徽章 - 游戏，则不需要。您的要求中只有一次

解决方法如下

SELECT users.id, 
   game.id, 
   badge.id, 
   users_badges.created AS badge_created,
   COUNT(gamesBefore.id)_games 
FROM badges 
   INNER JOIN users_badges 
              ON badges.id = users_badges.badge_id 
   INNER JOIN users 
              ON users_badges.user_id = users.id 
   INNER JOIN games AS gamesBefore
              ON users.id = game.user_id 
              AND gamesBefore.created <= users_badges.created
WHERE users.id = 1
GROUP BY user.id, badge.id

稍微解释一下 - 您需要为用户 X 徽章选择所有可能的计数 - 因此我们按他们的 id 进行分组并计算之前的游戏数量。正如我之前提到的 - 根据您的要求，您不需要加入游戏两次。