﻿-- 插入基础数据
insert into tt (rno,coursename,cb_user,cb_time,bigcate,islessonnumber,points2,mainresrate,schoolname,category,videocourselevel,year) select rno,coursename,c.cb_user,c.cb_time,bigcate,islessonnumber,points2,mainresrate, s.nameen schoolname,category,videocourselevel, year from metel2014.course c left join metel2014.school s on s.id=c.schoolid where c.cb_time>@start and c.cb_time<@end;

-- 修改课节数量
update tt, (select x.rno, count(*) as count from (select distinct c.rno, lessonnumber from metel2014.resource r, test.tt c where r.rno=c.rno and lessonnumber>0 and r.haspublished=1) as x group by x.rno) as y set tt.lessoncount=y.count where tt.rno=y.rno;

-- 修改视频数据
update tt, (select c.rno, count(*) as count from metel2014.resource r, test.tt c where r.rno=c.rno and r.doctype like '%vi%' and r.haspublished=1 group by r.rno) as x set tt.`vicount`=x.count where tt.rno = x.rno;

-- 修改资源数量
update tt, (select r.rno, count(*) as count from metel2014.resource r, test.tt c where c.rno=r.rno and r.haspublished=1 group by r.rno) as x set tt.rescount=x.count where tt.rno=x.rno;

-- 修改视频课件数量
update tt, (select r.rno,count(*) kvcount from test.tt c, metel2014.resource r where r.rno=c.rno and r.haspublished=1 and r.ban=0 and r.`doctype`='kv' group by r.rno) kvt set tt.kvcount=kvt.kvcount where tt.rno=kvt.rno;

-- 修改mp4文件数量
update tt,(select c.rno,count(*) mp4count from test.tt c, metel2014.resource r where r.rno=c.rno and r.haspublished=1 and r.ban=0 and r.`filetype` in ('flv') group by r.rno) mp4c set tt.mp4count=mp4c.mp4count where tt.rno=mp4c.rno;

-- 修改mp3文件数量
update tt,(select c.rno,count(*) mp3count from test.tt c, metel2014.resource r where r.rno=c.rno and r.haspublished=1 and r.ban=0 and r.`filetype` in ('mp3') group by r.rno) mp3c set tt.mp3count=mp3c.mp3count where tt.rno=mp3c.rno;

-- 修改office文件数量
update tt,(select c.rno,count(*) officecount from test.tt c, metel2014.resource r where r.rno=c.rno and r.haspublished=1 and r.ban=0 and r.`filetype` in ('doc','docx','ppt','pptx','xls','xlsx') group by r.rno) officec set tt.officecount=officec.officecount where tt.rno=officec.rno;