SQLite объединяет значения похожих записей в одну

Question

В моей базе данных SQLite есть таблица Tracks, которая состоит из следующих столбцов: artist, track, genre1, genre2, genre3. Таблица содержит много значений, которые имеют одного и того же исполнителя и значения треков с разными значениями genre1, genre2, genre3. Как пример ниже:

artist | track   | genre1  | genre2 | genre3
ABBA   | Song 1  | Rock    | Rock   |  Rock
U2     | Song 4  | Rock    | Rock   |  Rock
ABBA   | Song 1  | Pop     | Pop    |  Pop
U2     | Song 4  | Pop     | Pop    |  Pop
ABBA   | Song 1  | 70s     | 70s    |  70s
U2     | Song 4  | 90s     | 90s    |  90s

Мне нужно создать оператор SQLite, который объединит все уникальные жанровые значения, где исполнитель и трек совпадают, как показано в примере ниже:

artist | track   | genre1  | genre2 | genre3
ABBA   | Song 1  | Rock    | Pop    |  70s
U2     | Song 4  | Pop     | Rock   |  90s

Любая помощь с этим будет очень цениться.

Answer 1

К сожалению, похоже, что вы страдаете от плохого дизайна базы данных. В приведенном выше дизайне вы ограничены только 3 жанрами на песню, и когда к песне применяется только один жанр, вы просто повторяете одно и то же значение во всех жанровых полях. В хорошем дизайне у вас должно быть столько же или меньше жанровых записей на песню, а также вы можете уменьшить объем данных, необходимых для хранения жанровых значений.

Итак, давайте поговорим об отношениях "многие ко многим" и таблицах сопоставления. Отношения многие ко многим в этой ситуации - это множество уникальных жанровых ценностей, принадлежащих многим уникальным записям песен. Это также должно быть верно для обратного, потому что у нас также будет много уникальных песен, принадлежащих ко многим (или, возможно, ни один) жанрам.

Итак, как нам этого добиться... Ну, во-первых, давайте составим таблицу под названием Жанры. У меня должно быть два поля: целочисленный первичный ключ и строка для значения жанра с уникальным ограничением для последнего. Далее нам понадобится таблица сопоставления (для справки в будущем) назовем ее SongGenre), таблица, которая имеет только два поля внешнего ключа: одно для ссылки на таблицу песен, а другое для ссылки на таблицу жанров. Каждая запись будет говорить, что этот жанр принадлежит этой песне, и мы можем иметь несколько записей для каждой песни.

как только вы настроите таблицу сопоставления, вы сможете добиться того, что хотите, с чем-то вроде этого

SELECT Artist, Track, Group_Concat(Genre.GenreName) 
FROM Song 
JOIN SongGenre USING (SongID) 
JOIN Genre USING (GenreID) 
GROUP BY Artist, Track;