10 lines
3.4 KiB
Plaintext
10 lines
3.4 KiB
Plaintext
教学课堂数量+实践课程数量+用户数量+老师数量+学生数量
|
||
select s.id,s.name as school_name,(select count(*) from courses c where c.school_id = s.id) as course_count,(select count(*) from subjects sb where sb.school_id = s.id) as subjects_count,(select count(*) from user_extensions ue where ue.school_id = s.id) as user_count,(select count(*) from user_extensions ue where ue.school_id = s.id and identity = 0) as teacher_count,(select count(*) from user_extensions ue where ue.school_id = s.id and identity = 1) as student_count from schools s where name in ("北京大学","清华大学","北京工业大学","北京航空航天大学","北京邮电大学","北京理工大学","南开大学","天津大学","东北大学","大连理工大学","吉林大学","哈尔滨工业大学","复旦大学","同济大学","上海交通大学","华东师范大学","南京大学","东南大学","浙江大学","中国科学技术大学","厦门大学","山东大学","武汉大学","华中科技大学","国防科学技术大学","湖南大学","中山大学","华南理工大学","重庆大学","四川大学","电子科技大学","云南大学","西安交通大学","西北工业大学","西安电子科技大学", "中南大学", "北京交通大学", "国防科技大学") order by s.id;
|
||
|
||
|
||
学校下做为合作者的实践项目数量
|
||
select school_id,school_name,count(*) as shixun_count from (select distinct shixun_id,sc.name as school_name,sm.user_id,sc.id as school_id from shixun_members sm LEFT JOIN user_extensions ue_2 on ue_2.user_id=sm.user_id LEFT JOIN schools sc on sc.id=ue_2.school_id where sc.name in ("北京大学","清华大学","北京工业大学","北京航空航天大学","北京邮电大学","北京理工大学","南开大学","天津大学","东北大学","大连理工大学","吉林大学","哈尔滨工业大学","复旦大学","同济大学","上海交通大学","华东师范大学","南京大学","东南大学","浙江大学","中国科学技术大学","厦门大学","山东大学","武汉大学","华中科技大学","国防科学技术大学","湖南大学","中山大学","华南理工大学","重庆大学","四川大学","电子科技大学","云南大学","西安交通大学","西北工业大学","西安电子科技大学", "中南大学", "北京交通大学", "国防科技大学")) as shixuns GROUP BY `school_name` order by school_id;
|
||
|
||
各学校实践课程数量
|
||
select school_id,school_name,count(*) as count from (select distinct subject_id,sc.name as school_name,sm.user_id,sc.id as school_id from subject_members sm LEFT JOIN user_extensions ue_2 on ue_2.user_id=sm.user_id LEFT JOIN schools sc on sc.id=ue_2.school_id where sc.name in ("北京大学","清华大学","北京工业大学","北京航空航天大学","北京邮电大学","北京理工大学","南开大学","天津大学","东北大学","大连理工大学","吉林大学","哈尔滨工业大学","复旦大学","同济大学","上海交通大学","华东师范大学","南京大学","东南大学","浙江大学","中国科学技术大学","厦门大学","山东大学","武汉大学","华中科技大学","国防科学技术大学","湖南大学","中山大学","华南理工大学","重庆大学","四川大学","电子科技大学","云南大学","西安交通大学","西北工业大学","西安电子科技大学","国防科技大学"，"中南大学", "北京交通大学")) as shixuns GROUP BY `school_name` order by school_id;
|