mysql连接和连接不同的表

Question

我试着写一个小投票工具。我有 3 个表:用户、位置和投票。 votes 有 2 个外键(user_id 和 location_id)。

用户(示例数据):

+----+----------+
| id | username |
+----+----------+
|  5 | user1    |
|  7 | user2    |
| 11 | user3    |
|  4 | user4    |
| 12 | user5    |
+----+----------+

地点:

+----+----------------+
| id | locationname   |
+----+----------------+
|  1 | Pasta          |
|  2 | Burger         |
|  3 | Pizza          |
|  4 | Chinese        |
|  5 | Thai           |
+----+----------------+

票数:

+----+---------+-------------+------------+
| id | user_id | location_id | date       |
+----+---------+-------------+------------+
| 30 |       5 |           1 | 2016-06-30 |
| 31 |       5 |           1 | 2016-07-01 |
| 32 |       7 |           1 | 2016-07-01 |
| 38 |      11 |           2 | 2016-07-01 |
| 39 |       4 |           1 | 2016-07-04 |
| 41 |      12 |           3 | 2016-07-04 |
| 44 |       5 |           4 | 2016-07-04 |
| 46 |       7 |           5 | 2016-07-04 |
+----+---------+-------------+------------+

key 对日期和用户是唯一的，因此用户不能投票两次。

我现在想要一个像这样的 CURDATE() 列表:

+----------------+----------------+----------------------+
| locationname   | Votes          | Voters               |
+----------------+----------------+----------------------+
| Pasta          | 3              | user1, user2, user x |
| Burger         | 2              | user3, user4         |
| Pizza          | 1              | user5                |
| Chinese        | 1              | user6                |
| Thai           | 0              |                      |
+----------------+----------------+----------------------+

我该如何解决这个问题？尝试过类似的东西:

SELECT locations.locationname AS location, count(*) AS count, GROUP_CONCAT(users.username SEPARATOR ', ') AS Voters
FROM votes
INNER JOIN locations ON votes.location_id=locations.id
    WHERE date = CURDATE()
INNER JOIN users ON users.id=votes.user_id
    WHERE location_id = "1" AND date = CURDATE()
GROUP BY location_id
ORDER BY count DESC;

谢谢

Answer 1

我的一个 friend 向我展示了如何解决这个问题:

SELECT l.id AS locationid, l.locationname, count(username) AS count, GROUP_CONCAT(username SEPARATOR ", ") AS users
FROM locations l
LEFT JOIN votes v
  ON v.location_id = l.id AND v.date = CURDATE()
LEFT JOIN users u
  ON v.user_id = u.id
GROUP BY locationname
ORDER BY count DESC;