编辑代码

SELECT
  s1.stationid,
  COUNT(*)
FROM
  (
    SELECT
      s.stationid,
      i.itemid
    FROM
      masterdb.`stationinfo` s
      JOIN masterdb.`stationsongs` ss ON s.stationid = ss.stationid
      JOIN masterdb.`iteminfo` i ON ss.songid = i.itemid
      LEFT JOIN masterdb.`item_channelinfo` ic ON i.itemid = ic.itemid
    WHERE
      s.stationid IN (9689)
      AND ic.itemid IS NULL
      AND i.statusid = 404
    UNION
    SELECT
      s.stationid,
      i.itemid
    FROM
      masterdb.`stationinfo` s
      JOIN masterdb.`stationsongs` ss ON s.stationid = ss.stationid
      JOIN masterdb.`iteminfo` i ON ss.songid = i.itemid
      JOIN masterdb.`item_channelinfo` ic ON i.itemid = ic.itemid
      AND ic.type = 1
      AND ic.channelcode = 'yi'
    WHERE
      s.stationid IN (9689)
      AND i.statusid = 404
    UNION
    SELECT
      s3.stationid,
      s3.itemid
    FROM
      (
        SELECT
          s.stationid,
          i.itemid,
          GROUP_CONCAT(ic.channelcode) AS codes
        FROM
          masterdb.`stationinfo` s
          JOIN masterdb.`stationsongs` ss ON s.stationid = ss.stationid
          JOIN masterdb.`iteminfo` i ON ss.songid = i.itemid
          JOIN masterdb.`item_channelinfo` ic ON i.itemid = ic.itemid
          AND ic.type = 0
        WHERE
          s.stationid IN (9689)
          AND i.statusid = 404
        GROUP BY
          s.stationid,
          i.itemid
      ) s3
    WHERE
      ! FIND_IN_SET('yi', codes)
  ) s1
GROUP BY
  s1.stationid