php - 特定用户今天玩过的没有数据的游戏数量

Question

我正在尝试获取队列中的召唤师今天玩过的游戏数量。我尝试了几个查询。我得到的最接近的是:

   SELECT 
        s.summoner,
        s.wins,
        COUNT(*) as playedToday
   FROM summoners s
   LEFT JOIN teams_players tp ON tp.summoner_id = s.summoner
   LEFT JOIN teams t ON t.id = tp.team_id
   LEFT JOIN matches b ON t.id = b.blue_id
   WHERE
        b.played_on = DATE(NOW())
        s.in_queue;

但这并没有给出每个用户的数量。它只是获取总体计数。

所有表格都没有关于任何召唤师的任何数据。我想要的是这样的:

    +-----------------+------+-------------+
    | summoner        | wins | gamesPlayed |
    +-----------------+------+-------------+
    | DotAliscious    |  353 |           0 |
    | Kraator         |  440 |           0 |
    | Nammon          |  667 |           0 |
    | VictorousSecret |  843 |           0 |
    | Canas           |  544 |           0 |
    | Sprognak        |  502 |           0 |
    | Ghostilocks     |  808 |           0 |
    | b0b d0e         |  224 |           0 |
    | Metroshica      |  339 |           0 |
    | RubenatorX      |  478 |           0 |
    +-----------------+------+-------------+

我想不出如何笼统地表达这个问题，这就是我没有找到解决方案的原因。

---

我的数据集。请注意，teams.id 与 matches.blue_id 和 matches.purple_id 相匹配。

mysql> DESCRIBE summoners;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| icon         | int(11)     | NO   |     | 0       |                |
| summoner     | varchar(32) | NO   | UNI | NULL    |                |
| skype        | varchar(32) | NO   |     | NULL    |                |
| email        | varchar(32) | NO   |     | NULL    |                |
| in_queue     | tinyint(1)  | NO   |     | 0       |                |
| wins         | int(11)     | NO   |     | 0       |                |
| level        | int(11)     | NO   |     | 1       |                |
| lan_wins     | int(11)     | NO   |     | 0       |                |
| played_today | int(11)     | NO   |     | 0       |                |
+--------------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql> DESCRIBE matches;
+-----------+-------------+------+-----+---------------------+----------------+
| Field     | Type        | Null | Key | Default             | Extra          |
+-----------+-------------+------+-----+---------------------+----------------+
| id        | int(11)     | NO   | PRI | NULL                | auto_increment |
| blue_id   | int(11)     | NO   | MUL | NULL                |                |
| purple_id | int(11)     | NO   | MUL | NULL                |                |
| status    | varchar(32) | NO   |     | display             |                |
| played_on | datetime    | NO   |     | 2012-04-06 13:53:55 |                |
+-----------+-------------+------+-----+---------------------+----------------+
5 rows in set (0.00 sec)

mysql> DESCRIBE teams;
+---------+-------------+------+-----+------------+----------------+
| Field   | Type        | Null | Key | Default    | Extra          |
+---------+-------------+------+-----+------------+----------------+
| id      | int(11)     | NO   | PRI | NULL       | auto_increment |
| outcome | varchar(32) | NO   |     | incomplete |                |
+---------+-------------+------+-----+------------+----------------+
2 rows in set (0.00 sec)

mysql> DESCRIBE teams_players;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| team_id     | int(11) | NO   | MUL | NULL    |                |
| summoner_id | int(11) | NO   | MUL | NULL    |                |
+-------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Answer 1

您将要使用子选择来获取满足条件的记录数，如果条件为假，它将返回 0。类似的东西

(SELECT COUNT(*) FROM matches b WHERE b.blue_id = t.id AND b.played_on = DATE(NOW())) AS playedToday

并且您需要删除 LEFT JOIN 到 matches 表和检查日期的 WHERE 子句。