sql - 你能在 SQL 中按多行分组吗？

Question

假设我有一个数据库来跟踪我的视频游戏收藏。我的目标是检索行并将它们转换为这种形式的 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”作为平台字段，我稍后可以将其拆分，作为一个起点。但我仍然得到两行。

我正在我的书籍和网上阅读有关此的内容，但我想我什至不太了解搜索的术语，因为我有点挣扎。我真的很感激任何人可以提供的任何提示，甚至是让我知道我可以搜索哪些术语来找到相关 Material 。

Answer 1

尝试使用下面的查询，如果你想根据标题、年份和完成分组平台和流派

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