编辑代码

SELECT
  i.ItemID,
  i.name itemName,
  (
    SELECT
      GROUP_CONCAT(r.name)
    FROM
      artistinfo r,
      itemartist ir
    WHERE
      r.artistid = ir.artistid
      AND ir.itemid = i.itemid
  ) ArtistName,
  i.VERSION AS item_Version,
  a.name AlbumName,
  i.rating AS 曲库rating,
  (
    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 i.Exclusivity = '0010' THEN 'free'
      WHEN i.Exclusivity = '0011' THEN 'cache'
      WHEN i.Exclusivity = '0110' THEN 'paycachestr'
      WHEN i.Exclusivity = '0111' THEN 'paycache'
      WHEN i.Exclusivity = '0001' THEN 'normal'
      WHEN i.Exclusivity = '0100' THEN 'purchase'
      WHEN i.Exclusivity = '1000' THEN 'streaming'
    END
  ) AS 原付费模式,
  CONCAT(
    GROUP_CONCAT(f.channelcode),
    '-',
    GROUP_CONCAT(DISTINCT IF(f.type = 1, '白', '黑'))
  ) channelcode,
  (
  CASE
      WHEN w.audition_type = '0' THEN '关闭且可被覆盖'
      WHEN w.audition_type = '1' THEN '开启'
      WHEN w.audition_type = '2' THEN '关闭且不可被覆盖'
    END
  ) AS 'op定制片段情况',
  (
    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
  iteminfo i
  LEFT JOIN masterdb.`item_channel_w1` w ON i.`ItemID` = w.itemid
  AND w.channelcode = 'op'
  LEFT JOIN itemalbum ia ON ia.itemid = i.itemid
  LEFT JOIN albuminfo a ON a.albumid = ia.albumid
  LEFT JOIN item_channelinfo f ON i.itemid = f.itemid
WHERE
  i.itemid IN (73663)
GROUP BY
  i.ItemID