sql语句连接查询运算 if语句

最新推荐文章于 2024-07-28 01:05:05 发布

clwwlc

最新推荐文章于 2024-07-28 01:05:05 发布

阅读量1.4k

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/clwwlc/article/details/82801603

版权

sql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

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);