SELECT DISTINCT
cust.id,
cust.code,
count(m.media_url)
FROM
gtcom_cust_v2 cust
left JOIN gtcom_cust_media_rel_v2 rel ON cust.id = rel.cust_id
left JOIN gtcom_media_v2 m ON m.id = rel.media_id
WHERE
m.type_code = 'news'
GROUP BY
cust.id,m.media_url
-- 新闻
SELECT
cust.id,
cust.code,
COUNT(DISTINCT m.media_url)
FROM
gtcom_cust_v2 cust
LEFT JOIN gtcom_cust_media_rel_v2 rel ON cust.id = rel.cust_id
LEFT JOIN gtcom_media_v2 m ON m.id = rel.media_id
WHERE
m.type_code = 'news'
GROUP BY
cust.id,m.media_type
-- 社交
SELECT DISTINCT
cust.id,
cust.`code`,
m.type_code
COUNT(m.column_url)
FROM gtcom_cust_v2 cust LEFT JOIN gtcom_cust_media_rel_v2 rel ON cust.id = rel.cust_id LEFT JOIN gtcom_media_v2 m ON m.id = rel.media_id GROUP BY cust.id,m.type_code
--查询每个客户的媒体类型的主站个数（新闻、智库、论坛）
SELECT
cust.id,
cust.code,
m.media_type,
count(DISTINCT m.media_url) masterCount
FROM
gtcom_cust_v2 cust
INNER JOIN gtcom_cust_media_rel_v2 rel ON cust.id = rel.cust_id
INNER JOIN gtcom_media_v2 m ON m.id = rel.media_id
WHERE
m.media_type in ('新闻', '智库', '论坛')
GROUP BY
cust.id, m.media_type
