如何通过另一个表的计数来订购MySQL结果？

I have a database table containing game entries:

我有一个包含游戏条目的数据库表:

--------------------------------------------------------
| game_id | title  | description | entry_time          |
--------------------------------------------------------
| 1       | Game 1 | Descript... | yyyy-mm-dd hh:mm:ss |
--------------------------------------------------------
| 2       | Game 2 | Descript... | yyyy-mm-dd hh:mm:ss |
--------------------------------------------------------

And another containing game play history:

另一个包含游戏历史:

-----------------------------------------------
| game_id | user_id     | entry_time          |
-----------------------------------------------
| 1       | 0da89sadf89 | yyyy-mm-dd hh:mm:ss |
-----------------------------------------------
| 1       | f8jsf89vjs9 | yyyy-mm-dd hh:mm:ss |
-----------------------------------------------
| 2       | f8jsf89vjs9 | yyyy-mm-dd hh:mm:ss |
-----------------------------------------------

I am trying to select results from the first table, based on game popularity.

我试图根据游戏的受欢迎程度从第一张桌子中选择结果。

SELECT games.game_id, games.title, games.description
  FROM `games`
  JOIN foo_db.game_plays game_plays
    ON game_plays.game_id LIKE games.game_id
  WHERE games.title LIKE "%game%"
  ORDER BY COUNT(game_plays.game_id) DESC, games.entry_time DESC
  LIMIT 10

But for some reason, only one result is returned ("Game 1").

但由于某种原因,只返回一个结果(“游戏1”)。

When I remove JOIN, and just order the results by entry_time, both results are returned as expected.

当我删除JOIN,并按entry_time排序结果时,两个结果都按预期返回。

2 个解决方案

#1

I made this query. Could you please try this.

我做了这个查询。你能试试吗?

SELECT a.game_id, a.title, a.description, b.total
  from games a
  JOIN (SELECT game_id, count(user_id) as total from
           game_play group by game_id) b
  ON a.game_id = b.game_id
  AND a.title LIKE '%Game%'
  ORDER BY b.total DESC, a.entry time DESC

OUTPUT

Hope it will help.

希望它会有所帮助。

#2

Because MySQL is sometimes really slow when mixing JOIN and GROUP BY, a corelated subquery might be a good alternative:

因为在混合JOIN和GROUP BY时MySQL有时非常慢,所以核心子查询可能是一个很好的选择:

SELECT games.game_id, games.title, games.description
FROM `games`
WHERE games.title LIKE "%game%"
ORDER BY (
    SELECT COUNT(game_plays.game_id)
    FROM foo_db.game_plays
    WHERE game_plays.game_id = games.game_id
) DESC, games.entry_time DESC
LIMIT 10

#1