编辑代码

SELECT
  t.`trackid`,
  trackname,
  tcp.spid as '录音cc_spid',
  tcp.spname as '录音spname',
  tcp.cpid as '录音cc_cpid',
  tcp.cpname as '录音cpanme',
  tcp.tcp_isexclusive as '录音是否独家',
  tcp.t_contractnumber as '录音合同号',
  tcp.t_other as '录音其他权利',
  tcp.t_canauth as '录音有转授权',
  tcp.authform as '录音authfrom',
  (
    CASE
      WHEN FIND_IN_SET('5', tcp.authproduct) THEN 'Y'
      ELSE 'N'
    END
  ) as '录音授权是否有爱听',
  (
    CASE
      WHEN tcp.endtime is not null THEN tcp.endtime
      WHEN (
        tcp.endtime is null
        and tcp.endtime2 is not null
      ) THEN tcp.endtime2
      WHEN (
        tcp.endtime is null
        and tcp.endtime2 is null
      ) THEN '/'
    END
  ) AS '录音到时日期',
  tcp.t_from as '录音合同来源',
  `wordauthor` as '词作者',
  wcp.companyname as '词spname',
  wcp.w_isexclusive as '词是否独家',
  wcp.w_contractnumber as '词合同号',
  wcp.w_other as '词其他权利',
  wcp.w_canauth as '词有转授权',
  wcp.w_authform as '词authfrom',
  wcp.w_endtime as '词到期时间',
  wcp.percent as '词占比',
  wcp.w_authproduct as '词授权是否有爱听',
  wcp.w_from as '词合同来源',
  `musicauthor` as '曲作者',
  mcp.companyname as '曲spname',
  mcp.m_isexclusive as '曲是否独家',
  mcp.m_contractnumber as '曲合同号',
  mcp.m_other as '曲其他权利',
  mcp.m_canauth as '曲有转授权',
  mcp.m_authform as '曲authfrom',
  mcp.m_endtime as '曲到期时间',
  mcp.percent as '曲占比',
  mcp.m_authproduct as '曲授权是否有爱听',
  mcp.m_from as '曲合同来源'
FROM
  `central_content`.`trackinfo` t
  LEFT JOIN (
    SELECT
      w.trackid,
      GROUP_CONCAT(
        c.companyname
        order by
          w.id
      ) companyname,
      GROUP_CONCAT(
        CONCAT(percent)
        order by
          w.id
      ) percent,
      (
        CASE
          WHEN c2.isexclusive = 1 THEN '独家'
          WHEN c2.isexclusive = 0 THEN '非独家'
          WHEN (
            c2.isexclusive is null
            and w.isexclusive = 1
          ) THEN '独家'
          WHEN (
            c2.isexclusive is null
            and w.isexclusive = 0
          ) THEN '非独家'
          WHEN (
            c2.isexclusive is null
            and w.isexclusive is null
          ) THEN '非独家'
          ELSE '/'
        END
      ) as w_isexclusive,
      group_concat(
        (
          CASE
            WHEN c2.
          end is not null THEN c2.
        end
        WHEN (c2.end is null
        and w.
      end is not null
  ) THEN w.
end
WHEN (c2.end is null
and w.
end is null
) THEN '/'
END
)
order by
  w.id
) as 'w_endtime',
group_concat(DISTINCT c2.authform SEPARATOR '^') as 'w_authform',
group_concat(
  (
    CASE
      WHEN FIND_IN_SET('5', w.authproduct) THEN 'Y'
      ELSE 'N'
    END
  )
  order by
    w.id
) AS 'w_authproduct',
group_concat(DISTINCT c2.contractnumber SEPARATOR '^') as 'w_contractnumber',
group_concat(c2.otherright) as 'w_other',
group_concat(
  DISTINCT (
    CASE
      WHEN c2.canauth in (1) THEN 'Y'
      WHEN c2.canauth in (0) THEN 'N'
      ELSE '/'
    END
  )
) AS 'w_canauth',
group_concat(
(
  CASE
    when c2.copyrightfrom in (0) then '0未知'
    when c2.copyrightfrom in (1) then '1版权部'
    when c2.copyrightfrom in (2) then '2免费授权'
    when c2.copyrightfrom in (3) then '3业管部'
    when c2.copyrightfrom in (4) then '4腾讯音乐人开平'
    when c2.copyrightfrom in (5) then '5腾讯音乐人BD'
    when c2.copyrightfrom in (6) then '6平台自签约'
    when c2.copyrightfrom in (7) then '7创作者中心'
    else c2.copyrightfrom
  end

)
  order by
  w.id
) as 'w_from'
FROM
  central_content.`wordcpinfo` w
  left JOIN central_content.`company` c ON w.`companyid` = c.`companyid`
  left join central_content.`copyright` c2 ON w.`copyrightid` = c2.`copyrightid`
WHERE
  w.trackid IN (487593195,487593199)
GROUP BY
  w.trackid
) wcp ON t.`trackid` = wcp.`trackid`
LEFT JOIN (
  SELECT
    m.trackid,
    GROUP_CONCAT(
      c.companyname
      order by
        m.id
    ) companyname,
    GROUP_CONCAT(
      CONCAT(percent)
      order by
        m.id
    ) percent,
    (
      CASE
        WHEN c2.isexclusive = 1 THEN '独家'
        WHEN c2.isexclusive = 0 THEN '非独家'
        WHEN (
          c2.isexclusive is null
          and m.isexclusive = 1
        ) THEN '独家'
        WHEN (
          c2.isexclusive is null
          and m.isexclusive = 0
        ) THEN '非独家'
        WHEN (
          c2.isexclusive is null
          and m.isexclusive is null
        ) THEN '非独家'
        ELSE '非独家'
      END
    ) as m_isexclusive,
    group_concat(
      (
        CASE
          WHEN c2.
        end is not null THEN c2.
      end
      WHEN (c2.end is null
      and m.
    end is not null
) THEN m.
end
WHEN (c2.end is null
and m.
end is null
) THEN '/'
END
)
order by
  m.id
) as 'm_endtime',
group_concat(DISTINCT c2.authform SEPARATOR '^') as 'm_authform',
group_concat(
  (
    CASE
      WHEN FIND_IN_SET('5', m.authproduct) THEN 'Y'
      ELSE 'N'
    END
  )
  order by
    m.id
) AS 'm_authproduct',
group_concat(DISTINCT c2.contractnumber SEPARATOR '^') as 'm_contractnumber',
group_concat(c2.otherright) as 'm_other',
group_concat(
  DISTINCT (
    CASE
      WHEN c2.canauth in (1) THEN 'Y'
      WHEN c2.canauth in (0) THEN 'N'
      ELSE '/'
    END
  )
) AS 'm_canauth',
group_concat(
(
  CASE
    when c2.copyrightfrom in (0) then '0未知'
    when c2.copyrightfrom in (1) then '1版权部'
    when c2.copyrightfrom in (2) then '2免费授权'
    when c2.copyrightfrom in (3) then '3业管部'
    when c2.copyrightfrom in (4) then '4腾讯音乐人开平'
    when c2.copyrightfrom in (5) then '5腾讯音乐人BD'
    when c2.copyrightfrom in (6) then '6平台自签约'
    when c2.copyrightfrom in (7) then '7创作者中心'
    else c2.copyrightfrom
  end

)
  order by
  m.id
) as 'm_from'
FROM
  central_content.`musiccpinfo` m
  left JOIN central_content.`company` c ON m.`companyid` = c.`companyid`
  left JOIN central_content.`copyright` c2 ON m.`copyrightid` = c2.`copyrightid`
WHERE
  m.trackid IN (487593195,487593199)
GROUP BY
  m.trackid
) mcp ON t.`trackid` = mcp.`trackid`
LEFT JOIN (
  SELECT
    cp.trackid,
    (
      CASE
        WHEN c2.isexclusive = 1 THEN '独家'
        WHEN c2.isexclusive = 0 THEN '非独家'
        WHEN (
          c2.isexclusive is null
          and cp.isexclusive = 1
        ) THEN '独家'
        WHEN (
          c2.isexclusive is null
          and cp.isexclusive = 0
        ) THEN '非独家'
        WHEN (
          c2.isexclusive is null
          and cp.isexclusive is null
        ) THEN '非独家'
        ELSE '非独家'
      END
    ) as tcp_isexclusive,
    cp.
end as 'endtime2',
c2.
end as 'endtime',
c2.authform,
cp.trackcpid as 'spid',
c3.companyname as 'spname',
cp.origincpid as 'cpid',
c4.companyname as 'cpname',
c2.contractnumber as 't_contractnumber',
group_concat(c2.otherright) as 't_other',
group_concat(
  DISTINCT (
    CASE
      WHEN c2.canauth in (1) THEN 'Y'
      WHEN c2.canauth in (0) THEN 'N'
      ELSE '/'
    END
  )
) AS 't_canauth',
cp.authproduct,
(
  CASE
    when c2.copyrightfrom in (0) then '0未知'
    when c2.copyrightfrom in (1) then '1版权部'
    when c2.copyrightfrom in (2) then '2免费授权'
    when c2.copyrightfrom in (3) then '3业管部'
    when c2.copyrightfrom in (4) then '4腾讯音乐人开平'
    when c2.copyrightfrom in (5) then '5腾讯音乐人BD'
    when c2.copyrightfrom in (6) then '6平台自签约'
    when c2.copyrightfrom in (7) then '7创作者中心'
    else c2.copyrightfrom
  end

)

as 't_from'
FROM
  central_content.`trackcpinfo` cp
  left JOIN central_content.`copyright` c2 ON cp.`copyrightid` = c2.`copyrightid`
  left JOIN central_content.`trackinfo` trackinfo ON cp.`trackid` = trackinfo.`trackid`
  left JOIN central_content.`company` c3 ON c3.`companyid` = cp.`trackcpid`
  left JOIN central_content.`company` c4 ON c4.`companyid` = cp.`origincpid`
WHERE
  cp.trackid IN (487593195,487593199)
GROUP BY
  cp.trackid
) tcp ON t.`trackid` = tcp.`trackid`
WHERE
  t.trackid IN (487593195,487593199)
GROUP BY
  t.`trackid`