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