您可以在SQL中按多行分组吗？

绿三角

假设我有一个跟踪我的视频游戏收藏的数据库。我的目标是检索行并将其转换为这种形式的JavaScript对象：

{
    title: "Dark Souls",
    releaseYear: 2011,
    genres: ["RPG", "Action"],
    platforms: ["Windows", "Xbox 360", "PlayStation 3"],
    completed: false
}

我想将此数据存储在SQLite数据库中。我的直觉是这样组织它。

表：游戏

title : String
releaseYear : Int
completed : Int (0/1 in lieu of boolean type)

表：平台

name : String

表：流派

name : String

表格：games_genres_link

genres_id : Int
games_id: Int

表格：games_platforms_link

games_id : Int
platforms_id : Int

我发出类似的查询

SELECT games.title,
       games.releaseYear,
       games.completed,
       platforms.name as platforms,
       genres.name as genres
FROM games
JOIN games_platforms_link on games_platforms_link.games_id = games.rowid
JOIN platforms on games_platforms_link.platforms_id = platforms.rowid
JOIN games_genres_link on games_genres_link.games_id = games.rowid
JOIN genres on games_genres_link.genres_id = genres.rowid
GROUP BY platforms

希望至少获得“ Windows，Xbox 360，PlayStation”作为平台字段，稍后我可以将其作为起点进行拆分。但是我仍然得到两行。

我正在书本和在线上阅读有关此内容的信息，但我想我甚至不甚了解搜索的用语，因为我有点挣扎。我真的很感激任何人都可以提供的任何提示，甚至包括让我知道可以搜索哪些术语以找到相关材料的东西。

Unnikrishnan R

如果要根据标题，年份和完成的类别对平台和类型进行分组，请尝试以下查询

SELECT games.title,
       games.releaseYear,
       games.completed,
       GROUP_CONCAT(platforms.name) as platforms,
       GROUP_CONCAT(genres.name) as genres
FROM games
JOIN games_platforms_link on games_platforms_link.games_id = games.rowid
JOIN platforms on games_platforms_link.platforms_id = platforms.rowid
JOIN games_genres_link on games_genres_link.games_id = games.rowid
JOIN genres on games_genres_link.genres_id = genres.rowid
GROUP BY games.title,games.releaseYear,games.completed

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。