上线影片数量统计查询sql_获取每个地区电影上线数量的方法-CSDN博客

本文链接：https://blog.csdn.net/cornerzf/article/details/53300929

本文介绍了一个复杂的SQL查询案例，该查询涉及多个表的联接操作，包括内联接和左联接，并通过分组和条件筛选实现了对应用程序、内容提供商、多媒体资源及其分组等信息的综合统计。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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