MySQL 通过查询获取组上最后添加值的名称

Question

我试图在视频游戏作弊和提示页面的索引中显示，其中列出了所有可用的作弊，但我按视频游戏对结果进行分组并计算视频游戏有多少作弊，这是我可以完成的，但是我正在尝试展示最后添加到游戏中的作弊。

我的查询是:

SELECT a_games.game_id, COUNT(*) AS cheat_count, a_games.game_fname, a_games.game_logo, a_cheats.cheat_title FROM a_cheats
                LEFT JOIN a_games ON a_games.game_id=a_cheats.game_id
                GROUP BY a_cheats.game_id

这仅显示第一个添加的作弊。

我尝试在 cheat_id 上使用 max，但值 cheat_title 始终显示第一个添加的作弊。

表a_cheats

cheat_id    type_id    member_id    game_id    cheat_title    cheat_body    cheat_date    
1        |  1       |  1         |  22      |  Truques V...|  Introduz...|  2014-10-...| 
2        |  1       |  1         |  25      |  Invulnera...|  Durante ...|  2014-10-...| 
3        |  1       |  1         |  25      |  Modo Debu...|  Durante ...|  2014-10-...|      
4        |  1       |  1         |  25      |  Charme In...|  Durante ...|  2014-10-...| 
5        |  1       |  1         |  36      |  Cabeças e...|  Começa o...|  2014-10-...| 

表a_games

game_id    genre_id    member_id    game_fname    game_sname   game_logo 
22      |  15       |  1         |  4x4 Worl...|             | 5259da0...
25      |  3        |  1         |  Akuji th...|             | 5287ae0...
36      |  25       |  1         |  All Star...|             | 5287daa...

因此，在结果中，Akuji the Heartless 应该显示 cheat_title“Charme Infinito”，这是该游戏最后添加的作弊

查询结果

game_id   game_count   game_fname            game_logo                              cheat_title
22      | 1          | 4x4 World Trophy    | 5259da0527128_ava_4x4worldtrophy.jpg | Truques (Vários)
25      | 3          | Akuji The Heartless | 5287ae093e115_ava_akujiheartless.jpg | Invulnerabilidade
36      | 1          | All Star Tennis'99  | 5287daa2695ef_ava_allstartennis99.jpg| Cabeças e pés grandes

Answer 1

似乎一旦你调用了count()，你就无法控制稍后选择哪个title(它直接获取最早条目的标题)。因此，我们可以通过稍后调用计数来完成您想要的操作。

注意:如果您希望更可靠，可以将 ORDER BY a_cheats.cheat_id DESC 更改为 ORDER BY a_cheats.cheat_date DESC。

SELECT gameID,gameName,cheatTitle, COUNT(gameID) AS cheat_count
FROM (
    SELECT a_games.game_id as gameID, a_games.game_fname as gameName, a_cheats.cheat_title as cheatTitle
    FROM a_cheats
    JOIN a_games ON a_games.game_id=a_cheats.game_id
    ORDER BY a_cheats.cheat_id DESC
) ungrouped
GROUP BY gameID

SQLFiddle:http://sqlfiddle.com/#!2/63f888/18

或者

SELECT gameID,gameName,cheatTitle, COUNT(cheatID) AS cheat_count
FROM (
    SELECT a_games.game_id as gameID, a_games.game_fname as gameName, a_cheats.cheat_title as cheatTitle, a_cheats.cheat_id as cheatID
    FROM a_cheats
    RIGHT JOIN a_games ON a_games.game_id=a_cheats.game_id
    ORDER BY a_cheats.cheat_id DESC
) ungrouped
GROUP BY gameID

SQLFiddle:http://sqlfiddle.com/#!2/63f888/19