MySQL WHERE条件导致曲目关联风格无法全部显示的问题排查

你的问题根源在于WHERE子句直接过滤了vinyl_tracks_styles表的行：当你添加vts.styleID = 66时，数据库会只保留该曲目关联风格中ID为66的那条记录，其他风格的关联行被直接排除，导致GROUP_CONCAT只能拼接这单个风格。

要解决这个问题，你需要先筛选出包含风格ID66的曲目，再完整关联这些曲目的所有风格数据，而不是直接过滤风格表的行。以下是两种可行的修改方案：

方案1：使用EXISTS子查询筛选目标曲目 #

SELECT vt.trackID, vt.vinylID, vt.trackArtist, vt.side, vt.trackTitle, vt.notes, vt.bpm, vt.keyID, 
       vk.keyName, vt.length, vd.Artist, vd.Title, vd.Image, vk.Camelot, vk.Traktor,
       GROUP_CONCAT(vts.styleID SEPARATOR ',') AS 'styleIDs', 
       GROUP_CONCAT(vs.styleName SEPARATOR ',') AS 'styleNames'
FROM vinyl_tracks vt
INNER JOIN vinyl_data vd ON vt.vinylID = vd.vinylID
LEFT JOIN vinyl_keys vk ON vt.keyID = vk.keyID
LEFT JOIN vinyl_tracks_styles vts ON vt.trackID = vts.trackID
LEFT JOIN vinyl_styles vs ON vts.styleID = vs.styleID
WHERE vt.notes NOT LIKE '%reject%'
  AND EXISTS (
      SELECT 1 
      FROM vinyl_tracks_styles vts_sub 
      WHERE vts_sub.trackID = vt.trackID 
        AND vts_sub.styleID = 66
  )
GROUP BY vt.trackID, vt.vinylID, vt.trackArtist, vt.side, vt.trackTitle, vt.notes, 
         vt.bpm, vt.keyID, vk.keyName, vt.length, vd.Artist, vd.Title, vd.Image, 
         vk.Camelot, vk.Traktor
ORDER BY vt.trackID DESC LIMIT 0, 50;

方案2：使用IN子查询筛选目标曲目 #

SELECT vt.trackID, vt.vinylID, vt.trackArtist, vt.side, vt.trackTitle, vt.notes, vt.bpm, vt.keyID, 
       vk.keyName, vt.length, vd.Artist, vd.Title, vd.Image, vk.Camelot, vk.Traktor,
       GROUP_CONCAT(vts.styleID SEPARATOR ',') AS 'styleIDs', 
       GROUP_CONCAT(vs.styleName SEPARATOR ',') AS 'styleNames'
FROM vinyl_tracks vt
INNER JOIN vinyl_data vd ON vt.vinylID = vd.vinylID
LEFT JOIN vinyl_keys vk ON vt.keyID = vk.keyID
LEFT JOIN vinyl_tracks_styles vts ON vt.trackID = vts.trackID
LEFT JOIN vinyl_styles vs ON vts.styleID = vs.styleID
WHERE vt.notes NOT LIKE '%reject%'
  AND vt.trackID IN (
      SELECT trackID 
      FROM vinyl_tracks_styles 
      WHERE styleID = 66
  )
GROUP BY vt.trackID, vt.vinylID, vt.trackArtist, vt.side, vt.trackTitle, vt.notes, 
         vt.bpm, vt.keyID, vk.keyName, vt.length, vd.Artist, vd.Title, vd.Image, 
         vk.Camelot, vk.Traktor
ORDER BY vt.trackID DESC LIMIT 0, 50;

关键说明 #

1. 两种方案都是先通过子查询确定哪些曲目包含风格ID66，再对这些曲目关联所有风格数据，确保GROUP_CONCAT能拼接该曲目的全部风格。
2. 补充了GROUP BY的完整字段列表：在MySQL的严格SQL模式下，所有非聚合函数的查询字段必须包含在GROUP BY中，避免语法错误。

内容的提问来源于stack exchange，提问作者cheeseus