select t.seriesId,t.seriesName 剧集名称,t.channel 频道,t.categoryName 分类名称,t.episodes 集数,m.totalDuration 总时长秒,m.totalSize 总大小M,IF(t.status=0,'上架','下架') 状态 from (select series_id,series.id seriesId,series.name seriesName,series.channel,category.name categoryName,series.episodes,series.status from series left join category on series.category_id=category.id) t left join (select series_id,FLOOR(sum(duration/frame_ratio)) totalDuration,round(sum(size/1024/1024),1) totalSize from movie group by series_id) m on t.series_id=m.series_id;
select t.seriesId,t.seriesName 剧集名称,t.channel 频道,t.categoryName 分类名称,t.episodes 集数,m.totalDuration 总时长(秒),m.totalSize 总大小(M),IF(t.status=0,'上架','下架') 状态,t.year 年份,t.first_letter 首字母,t.provider 内容方,t.summary 简介 from (select series_id,series.id seriesId,series.name seriesName,series.channel,category.name categoryName,series.episodes,series.status,series.year,series.first_letter,series.provider,series.summary from series left join category on series.category_id=category.id) t left join (select series_id,FLOOR(sum(duration/frame_ratio)) totalDuration,round(sum(size/1024/1024),1) totalSize from movie group by series_id) m on t.series_id=m.series_id;
select id,name from series where series_id not in (select series_id from movie where status!=0 and episode<=6 group by series_id);
select id,name from series where series_id in (select series_id from movie where status!=0 and episode<=6 group by series_id);
select id,name from (select series_id from (select series_id from movie group by series_id) t where series_id not in (select series_id from movie where status!=0 and episode<=6 group by series_id)) t1 left join series on t1.series_id=series.series_id order by id;
sec_to_time函数:
select t.seriesId,t.seriesName 剧集名称,t.categoryName 分类名称,t.episodes 集数,sec_to_time(m.totalDuration) 总时长,m.totalSize 总大小(G) from (select series_id,series.id seriesId,series.name seriesName,series.channel,category.name categoryName,series.episodes,series.status from series left join category on series.category_id=category.id) t left join (select series_id,FLOOR(sum(duration/frame_ratio)) totalDuration,round(sum(size/1024/1024/1024),1) totalSize from movie group by series_id) m on t.series_id=m.series_id;
select series.name 系列名称,movie.name 单集名称,sec_to_time(FLOOR(movie.duration/movie.frame_ratio)) 时长 from movie left join series on series.series_id=movie.series_id;
查询注入完成的:
select * from (select * from series where id in (1,2,3)) t where series_id not in (select series_id from movie where status!=0 group by series_id);
查询注入未完成的:
select * from (select * from series where id in (1,2,3)) t where series_id in (select series_id from movie where status!=0 group by series_id);