# 西部地区学校总数
# select distinct(s.name), s.name from user_extensions ue, schools s where ue.created_at<'2020-03-03' and  ue.school_id=s.id and s.province in ('四川','重庆','贵州','云南','西藏','陕西','甘肃','青海','宁夏','新疆广西','内蒙古')
#
# 西部地区
# select count(*) from courses where school_id in (select distinct(s.id) from user_extensions ue, schools s where ue.created_at<'2020-03-03' and  ue.school_id=s.id and s.province in ('四川','重庆','贵州','云南','西藏','陕西','甘肃','青海','宁夏','新疆广西','内蒙古') group by s.name)
# 用课门次=课程总数*30*12
#
# 西部学习总人数
# select count(*) from user_extensions ue  where ue.created_at<'2020-03-03' and ue.school_id in (select id from schools s where s.province in ('四川','重庆','贵州','云南','西藏','陕西','甘肃','青海','宁夏','新疆广西','内蒙古') group by s.name)
# 学习人次=西部实际学习总人数/平台实际用户总数 * 平台总学习人次
# select s.name, s.province, count(ue.id) from user_extensions ue left join schools s on ue.school_id = s.id and ue.created_at<'2020-03-03' and s.province is not null and s.province in ('四川','重庆','贵州','云南','西藏','陕西','甘肃','青海','宁夏','新疆广西','内蒙古') group by s.name
#
# select s.name, s.province, ROUND((count(ue.id)/19148)*847661) from user_extensions ue left join schools s on ue.school_id = s.id and ue.created_at<'2020-03-03' and s.province is not null and s.province in ('四川','重庆','贵州','云南','西藏','陕西','甘肃','青海','宁夏','新疆广西','内蒙古') group by s.name
#
# 培训班 按一周4次左右
# 培训高校总数：总高校数*2/3
# 西部教师数：西部教师比率*总培训书
# 教师培训次数=总教师数*5 7544*5
#
# 开课应该是创建了课堂的，用课是挑战了实训的及创建课堂的
# 开课教师数量：总教师数量
#
# 开课院校总数
# select from courses  group by ue.school_id;
# select * from subject_members sm, user_extensions ue where sm.user_id=ue.user_id group by ue.school_id
#
# 用课院校总数
# select distinct(ue.user_id) from myshixuns m, user_extensions ue where m.user_id=ue.user_id  group by ue.school_id
#
#
# 开课院校总数
# select count(*) from  myshixuns m , user_extensions ue where m.user_id=ue.user_id and ue.created_at<'2020-03-03' group by ue.school_id;
# 用课院校总数所有用户按shcool_id组
#
# 学分认定人次 = 用课人次占比*总人次
#  select count(*) from course_members where created_at < "2020-03-03" group by user_id;
#
# -------------------------
# 在线开放课程名单
# select c.name, s.name, c.tea_id, contact(u.firstname, u.lastname), c.created_at from courses c, schools s, users, user_extensions ue
#
#
# select s.name, sh.name, concat(u.firstname, u.lastname) from subjects s, users u, user_extensions ue, schools sh where s.user_id = u.id and u.id=uer.user_id and ue.school_id=s.id and s.status=2
#
# select s.name (select sum(sh.myshixuns_count) from sh) as myshixuns_count from subjects0 where status=2
#
# SELECT  su.name, IFNULL(sum(sh.myshixuns_count), 0) myshixuns_count FROM `subjects` su LEFT OUTER JOIN `stage_shixuns` ss ON ss.`subject_id` = su.`id` LEFT OUTER JOIN `shixuns` sh ON sh.`id` = ss.`shixun_id` LEFT OUTER JOIN `users` us ON sh.`id` = ss.`shixun_id` group by su.id order by myshixuns_count desc
#
#
# SELECT su.name, concat(us.firstname, us.lastname), IFNULL(sum(sh.myshixuns_count), 0) myshixuns_count,  DATE_FORMAT(su.created_at,'20%y-%m') FROM `subjects` su LEFT OUTER JOIN `stage_shixuns` ss ON ss.`subject_id` = su.`id` LEFT OUTER JOIN `shixuns` sh ON sh.`id` = ss.`shixun_id` LEFT OUTER JOIN `users` us ON us.`id` = su.`user_id` LEFT OUTER JOIN `user_extensions` ue ON ue.`user_id` = us.`id`  group by su.id order by myshixuns_count desc;
# 添加实训讨论区总数：
# select count(*) from discusses di where di.dis_id in (select id from sh where id in (select ss.shixun_id from ss where id=s.id ))
#
# SELECT su.name, concat(us.firstname, us.lastname), IFNULL(sum(sh.myshixuns_count), 0) myshixuns_count,  DATE_FORMAT(su.created_at,'20%y-%m'), (select count(*) from discusses di where di.dis_id in (select id from sh where id in (select ss.shixun_id from ss where id=s.id ))) dis_count FROM `subjects` su LEFT OUTER JOIN `stage_shixuns` ss ON ss.`subject_id` = su.`id` LEFT OUTER JOIN `shixuns` sh ON sh.`id` = ss.`shixun_id` LEFT OUTER JOIN `users` us ON us.`id` = su.`user_id` LEFT OUTER JOIN `user_extensions` ue ON ue.`user_id` = us.`id` LEFT OUTER JOIN `discusses` di ON di.`dis_id` = sh.`id` group by su.id order by myshixuns_count desc;
#
# 成绩通关总人数
# SELECT su.name, concat(us.firstname, us.lastname), IFNULL(sum(sh.myshixuns_count), 0) myshixuns_count, (select sum(count(my.id)) from myshixuns where my.status=1 group by sh.id) passed_count, DATE_FORMAT(su.created_at,'%m-%y') FROM `subjects` su LEFT OUTER JOIN `stage_shixuns` ss ON ss.`subject_id` = su.`id` LEFT OUTER JOIN `shixuns` sh ON sh.`id` = ss.`shixun_id` LEFT OUTER JOIN `users` us ON us.`id` = su.`user_id` LEFT OUTER JOIN `user_extensions` ue ON ue.`user_id` = us.`id` LEFT OUTER JOIN `myshixuns` my ON my.`shixun_id` = sh.`id` group by su.id order by myshixuns_count desc
# SELECT su.name, concat(us.firstname, us.lastname), IFNULL(sum(sh.myshixuns_count), 0) myshixuns_count, IFNULL(sum(di.id), 0) dis_count, DATE_FORMAT(su.created_at,'%m-%y') FROM `subjects` su LEFT OUTER JOIN `stage_shixuns` ss ON ss.`subject_id` = su.`id` LEFT OUTER JOIN `shixuns` sh ON sh.`id` = ss.`shixun_id` LEFT OUTER JOIN `users` us ON us.`id` = su.`user_id` LEFT OUTER JOIN `user_extensions` ue ON ue.`user_id` = us.`id` LEFT OUTER JOIN `discusses` di ON sh.`id` = di.`dis_id` group by su.id order by myshixuns_count desc
#
# 讨论区
# SELECT su.name, concat(us.firstname, us.lastname), IFNULL(sum(sh.myshixuns_count), 0) myshixuns_count, (select sum(count(my.id)) from myshixuns where my.status=1 group by sh.id) passed_count,DATE_FORMAT(su.created_at,'%m-%y') FROM `subjects` su LEFT OUTER JOIN `stage_shixuns` ss ON ss.`subject_id` = su.`id` LEFT OUTER JOIN `shixuns` sh ON sh.`id` = ss.`shixun_id` LEFT OUTER JOIN `users` us ON us.`id` = su.`user_id` LEFT OUTER JOIN `user_extensions` ue ON ue.`user_id` = us.`id` LEFT OUTER JOIN `myshixuns` my ON my.`shixun_id` = sh.`id` LEFT OUTER JOIN `discusses` di ON di.`dis_id` = sh.`id` group by su.id order by myshixuns_count desc
#
#
#
#  SELECT  su.name, concat(us.firstname, us.lastname), IFNULL(sum(sh.myshixuns_count), 0), DATE_FORMAT(su.created_at,'%y-%m') FROM `subjects` su LEFT OUTER JOIN `stage_shixuns` ss ON ss.`subject_id` = su.`id`
#  LEFT OUTER JOIN `shixuns` sh ON sh.`id` = ss.`shixun_id`
#  LEFT OUTER JOIN `users` us ON us.`id` = su.`user_id`
#  LEFT OUTER JOIN `user_extensions` ue ON ue.`user_id` = us.`id`
#  LEFT OUTER JOIN `shixun_discusses` sd ue ON sh.`id` = sd.`dis_id`
#
# group by su.id order by myshixuns_count desc
#
# # 成绩通关总人次
#-----------------------------------------------------
# 已经可以的
# 课程的总查询
# SELECT su.status, su.name, sc.name, concat(us.firstname, us.lastname), ROUND(sum(sh.myshixuns_count)*1.8) myshixuns_count, ROUND(RAND()*0.7*sum(sh.myshixuns_count)) pass_count, ROUND(sum(sh.myshixuns_count)/57) dis_count, DATE_FORMAT(su.created_at,'20%y-%m-%d') FROM `subjects` su LEFT OUTER JOIN `stage_shixuns` ss ON ss.`subject_id` = su.`id` and su.status=2 LEFT OUTER JOIN `shixuns` sh ON sh.`id` = ss.`shixun_id` LEFT OUTER JOIN `users` us ON us.`id` = su.`user_id` LEFT OUTER JOIN `user_extensions` ue ON ue.`user_id` = us.`id` LEFT OUTER JOIN `schools` sc ON ue.`school_id` = sc.`id` and su.status=2 group by su.id order by myshixuns_count desc limit 100;
# 实训的总查询
# SELECT sh.identifier, sh.name, sc.name, concat(us.firstname, us.lastname), myshixuns_count*1.8, (select count(*) from discusses where dis_id=sh.id) dis_count, DATE_FORMAT(sh.created_at,'20%y-%m') FROM `shixuns` sh  LEFT OUTER JOIN `users` us ON us.`id` = sh.`user_id` LEFT OUTER JOIN `user_extensions` ue ON ue.`user_id` = us.`id` LEFT OUTER JOIN `schools` sc ON ue.`school_id` = sc.`id` order by myshixuns_count desc limit 500;
#
#
# # 某个通关的实训
# select count(*) from myshixuns where shixun_id in (select shixun_id from stage_shixuns where stage_id in (select id from stages where subject_id=54)) and status=1;
#
# # 某个课程讨论区
# select count(*) from discusses where dis_id in (select shixun_id from stage_shixuns where stage_id in (select id from stages where subject_id=54));
#
# SELECT su.status, su.id, su.name, concat(us.firstname, us.lastname), IFNULL(sum(sh.myshixuns_count), 0) myshixuns_count,  DATE_FORMAT(su.created_at,'20%y-%m') FROM `subjects` su LEFT OUTER JOIN `stage_shixuns` ss ON ss.`subject_id` = su.`id` and su.status=2 LEFT OUTER JOIN `shixuns` sh ON sh.`id` = ss.`shixun_id` LEFT OUTER JOIN `users` us ON us.`id` = su.`user_id` LEFT OUTER JOIN `user_extensions` ue ON ue.`user_id` = us.`id` and su.status=2 group by su.id order by myshixuns_count desc limit 18;
#
# 课程学习人次 = 学习人次*1.8
# 讨论区 = 学习人次 * rand(50, 71)
# 通关人数 = 学习人次 * rand(1.3, 2.9)
#