编辑代码

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 '专辑名',
  (
    CASE
      WHEN i.is_tme_musician = 1 THEN '音乐人'
      WHEN i.is_tme_musician = 2 THEN '音乐人'
      WHEN i.is_tme_musician = 3 THEN '音乐人'
      WHEN i.is_tme_musician = 4 THEN '音乐人'
      ELSE '非音乐人'
    END
  ) AS '是否音乐人',
  (
    CASE
      WHEN i.company_type = 3 THEN '优选B'
      WHEN i.company_type = 4 THEN '优选A'
      WHEN i.company_type = 5 THEN '优选C'
      ELSE '非优选'
    END
  ) company_type,
  (
    CASE
      WHEN w.Exclusivity = '0010' THEN 'free'
      WHEN w.Exclusivity = '0011' THEN 'cache'
      WHEN w.Exclusivity = '0110' THEN 'paycachestr'
      WHEN w.Exclusivity = '0111' THEN 'paycache'
      WHEN w.Exclusivity = '0001' THEN 'normal'
      WHEN w.Exclusivity = '0100' THEN 'purchase'
      WHEN w.Exclusivity = '1000' THEN 'streaming'
    END
  ) AS 'op定制付费模式',
  (
    SELECT
      c.name
    FROM
      contentproviderinfo c
    WHERE
      i.contentproviderid = c.contentproviderid
  ) CPName,
  (
    SELECT
      s.name
    FROM
      contentprovidergroupinfo s
    WHERE
      i.spid = s.ContentProviderGroupID
  ) SPName,
  (
    CASE
      WHEN w.statusid = 1 THEN '可用'
      ELSE '不可用'
    END
  ) AS 'op可用情况',
  (
    CASE
      WHEN w.TYPE = 1 THEN '在op库'
      ELSE '不在op库'
    END
  ) AS 'op库情况',
  (
    CASE
      WHEN i.statusid IN (404) THEN '上架'
      ELSE '下架'
    END
  ) 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
  LEFT JOIN masterdb.`item_channel_w1` w ON i.`ItemID` = w.itemid
  AND w.channelcode = 'op'
WHERE
  ss.CTType = 'CT_SONGLIST'
  AND si.CTType = 'CT_SONGLIST'
  AND si.StationID IN(144854)
GROUP BY
  ss.songid,
  si.StationID
ORDER BY
  si.StationID DESC