php - MySQL 查询错误 COUNT

Question

我正在开发一个应用程序。我必须查询我作为客人或管理员所在的房间。这很容易。问题是我想同时获得同一房间内的玩家数量。

SELECT rooms.id, rooms.name, rooms.buyin, rooms.administrator, rooms.creation_date,
       rooms.max_players, rooms.min_players, count(room_players.room)
FROM rooms, room_players
WHERE administrator = 1 and room_players.room = rooms.id 

我有一张包含房间的表格和另一张 (room_players)，它是用户和房间之间的匹配项。如果我删除 count(room_players.room) 和 and room_players.room = rooms.id 我有我管理的房间。但我也需要同一个房间内的玩家数量。

Answer 1

您的查询需要一个分组依据。通过 select 中的 COUNT()，它变成了一个返回一行的聚合查询——表中所有行的汇总。

SELECT r.id, r.name, r.buyin, r.administrator, r.creation_date,
       r.max_players, r.min_players, count(rp.room)
FROM rooms r join
     room_players rp
     ON rp.room = r.id 
WHERE administrator = 1 
GROUP BY r.id;

我还将连接语法修复为显式连接并添加了表别名以使查询更具可读性。

编辑:

我怀疑你想要的查询是这样的:

SELECT r.id, r.name, r.buyin, r.administrator, r.creation_date,
       r.max_players, r.min_players, count(rp.room)
FROM rooms r join
     room_players rp
     ON rp.room = r.id 
WHERE administrator <> 1 
GROUP BY r.id
HAVING sum(user = 1) > 0;