编辑代码

SELECT
  i.itemid,
  i.name itemName,
  i.Rating as rackRating,
  i.itemcode,
  i.LanguageType,
  i.`is_tme_musician`,
  i.Duration,
  i.sub_title,
  i.umcp,
  i.company_type,
  i.copyright_source,
  i.`lyricist`,
  i.`composer`,
  (
    SELECT
      GROUP_CONCAT(r.name)
    FROM
      artistinfo r,
      itemartist ir
    WHERE
      r.artistid = ir.artistid
      AND ir.itemid = i.itemid
  ) ArtistName,
  i.statusid itemStatus,
  i.Exclusivity,
  i.VERSION AS item_Version,
  i.takeoff_type,
  CASE
    WHEN i.step = 0 THEN 'encode done'
    WHEN i.step = 1 THEN 'processing file'
    WHEN i.step = 2 THEN 'wait for encode'
    WHEN i.step = 3 THEN 'encoding'
    WHEN i.step = 4 THEN 'encode exception'
    WHEN i.step = 5 THEN 'audio lost'
    WHEN i.step = 6 THEN 'jpg lost'
    WHEN i.step = 21 THEN 'wait for audition encode'
    ELSE ''
  END AS step,
  IF(i.refer_id <= 0, '', i.refer_id) itemreferid,
  DATE_FORMAT(i.ReleaseDate, '%Y-%m-%d %H:%i:%s') ReleaseDate,
  DATE_FORMAT(i.StartDate, '%Y-%m-%d %H:%i:%s') StartDate,
  DATE_FORMAT(i.ModifyDate, '%Y-%m-%d %H:%i:%s') Modified,
  i.Createuser,
  i.ModifyUser Modifieduser,
  CONCAT(
    GROUP_CONCAT(f.channelcode),
    '-',
    GROUP_CONCAT(DISTINCT IF(f.type = 1, '白', '黑'))
  ) channelcode,
  i.audition_begin AS item_Audition_Begin,
  i.audition_end AS item_Audition_End,
  i.sub_status,
  i.CPItemID as CPTrackID,
  a.AlbumId,
  a.name AlbumName,
  (
    SELECT
      GROUP_CONCAT(rrr.name)
    FROM
      artistinfo rrr,
      albumartist arrr
    WHERE
      rrr.artistid = arrr.artistid
      AND arrr.albumid = a.albumid
  ) AlbumArtist,
  a.statusid AlbumStatus,
  (
    SELECT
      cp.contentproviderid
    FROM
      contentproviderinfo cp
    WHERE
      i.contentproviderid = cp.contentproviderid
  ) cpid,
  (
    SELECT
      c.name
    FROM
      contentproviderinfo c
    WHERE
      i.contentproviderid = c.contentproviderid
  ) CPName,
  (
    SELECT
      sp.ContentProviderGroupID
    FROM
      contentprovidergroupinfo sp
    WHERE
      i.spid = sp.ContentProviderGroupID
  ) spid,
  (
    SELECT
      s.name
    FROM
      contentprovidergroupinfo s
    WHERE
      i.spid = s.ContentProviderGroupID
  ) SPName,
  i.using_range using_range,
  i.copyright_end_time,
  i.Explicit as Item_Explicit,
  s.hide_reason as item_hide_reason
FROM
  iteminfo i
  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
  LEFT JOIN safe_info s on s.entityid = i.itemid
  and s.type = 1
where
  i.itemid in (77075)
GROUP BY
  i.itemid