编辑代码

SELECT
  si.StationID AS '歌单ID',
  si.name AS '歌单名',
  ss.rank,
  si.createuser AS '歌单创建者',
  si.createdate AS '歌单创建时间',
  (
    CASE
      WHEN si.BadgeType IN (1) THEN '上架'
      WHEN si.BadgeType IN (0) THEN '下架'
    END
  ) AS '歌单状态',
  GROUP_CONCAT(CONCAT(gi.groupid, '-', gi.name)) AS '一级分类ID-分类名',
  GROUP_CONCAT(CONCAT(g.GenreID, '-', g.name)) AS '二级分类ID-分类名',
  (
    SELECT
      COUNT(IF(i.statusid IN (405, 406), 1, NULL))
    FROM
      masterdb.`stationinfo` s
      JOIN masterdb.`stationsongs` ss ON s.`StationID` = ss.`StationID`
      JOIN masterdb.`iteminfo` i ON ss.`SongID` = i.`ItemID`
    WHERE
      s.`StationID` = si.`StationID`
    GROUP BY
      s.`StationID`
  ) AS '歌单下架歌曲数',
  (
    SELECT
      COUNT(IF(i.statusid IN (404), 1, NULL))
    FROM
      masterdb.`stationinfo` s
      JOIN masterdb.`stationsongs` ss ON s.`StationID` = ss.`StationID`
      JOIN masterdb.`iteminfo` i ON ss.`SongID` = i.`ItemID`
    WHERE
      s.`StationID` = si.`StationID`
    GROUP BY
      s.`StationID`
  ) AS '歌单上架歌曲数',
  SongID AS '歌曲ID',
  i.`Name` AS '歌曲名',
  i.`version`,
  (
    SELECT
      GROUP_CONCAT(r.name)
    FROM
      artistinfo r,
      itemartist ir
    WHERE
      r.artistid = ir.artistid
      AND ir.itemid = i.itemid
  ) AS '歌手名',
  a.`Name` AS '专辑名'
FROM
  masterdb.stationsongs ss
  LEFT JOIN masterdb.stationinfo si ON si.StationID = ss.StationID
  JOIN masterdb.`genrestations` gs ON gs.`StationID` = si.StationID
  JOIN masterdb.`groupgenres` gg ON gg.`GenreID` = gs.`GenreID`
  JOIN masterdb.groupinfo gi ON gi.groupid = gg.`GroupID`
  JOIN masterdb.`genreinfo` g ON g.`GenreID` = gg.`GenreID`
  JOIN masterdb.`iteminfo` i ON i.itemid = ss.`SongID`
  LEFT JOIN `masterdb`.`itemalbum` AS ia ON i.ItemID = ia.ItemID
  LEFT JOIN `masterdb`.`albuminfo` AS a ON ia.albumid = a.albumid
WHERE
  ss.CTType = 'CT_SONGLIST'
  AND si.CTType = 'CT_SONGLIST'
  AND ss.SongID IN()
GROUP BY
  ss.songid,
  si.StationID
ORDER BY
  si.StationID DESC