上线影片数量统计查询sql

SELECT a.appid,a.`name`,c.cpname,f.topname,
count(DISTINCT b.mvid) mvnum,
count(d.volumeid) volnum
FROM appinfo_manage a 


INNER JOIN mv_cpapp_relation b on b.appid=a.appid
INNER JOIN cp_info_manage c on c.cpid=b.cpid
INNER JOIN mv_volume_info d on d.mvid=b.mvid
INNER JOIN (SELECT DISTINCT mvid,topid FROM mv_top_sort) e ON e.mvid=b.mvid
INNER JOIN mv_group_top f ON f.topid=e.topid
<where>
<if test="appid != null and appid != ''">and b.appid=#{appid} </if>
<if test="cpid != null and cpid != ''">and b.cpid=#{cpid}</if>
<if test="topid != null and topid != ''">and f.topid=#{topid}</if>
</where>
GROUP BY a.appid,a.`name`,c.cpname,f.topname 




select a.appid,a.`name`,c.cpname,f.topname,
count(distinct b.mvid) mvnum,
count(d.volumeid) volnum 
from appinfo_manage a
inner join mv_cpapp_relation b on b.appid=a.appid
inner join cp_info_manage c on c.cpid=b.cpid
inner join mv_info_cp p on p.mvid=b.mvid and p.cpid=c.cpid
inner join mv_operate_data o on o.mvid=b.mvid and o.state=1 and o.verifystate=4
left join mv_volume_info d on d.mvid=b.mvid and d.state=1
inner join (select distinct mvid,topid from mv_top_sort) e on e.mvid=b.mvid
inner join mv_group_top f on f.topid=e.topid
group by a.appid,a.`name`,c.cpname,f.topname