常用查询收集(二)

学校表 lqy_educationadd                主要字段  id_(PK)      schoolName  （status = 0表示正常状态）

教师表  lqy_teacher   外键schoolId  主要字段  id_(PK)      schoolId(FK)  （status = 0表示正常状态）一个学校关联多个教师

学生表 lqy_student    外键schoolId  主要字段  id_(PK)      schoolId(FK)    name(学生姓名)  region(居住范围：区内/区外)   schoolType(学校性质：民办/公办)（status = 0表示正常状态）一个学校关联多个学生

班课表 lqy_course     外键schoolId  主要字段  id_(PK)      schoolId(FK)  （status = 0表示正常状态）一个学校关联多个班课

班课--学生表 lqy_course_student   主要字段  id_(PK)      courseId(FK)  （status = 1表示退学状态） studentId(FK)    班课和学生多对多

查询要求：查询每个学校下面的  学生参培总数、学生区内参培人数、学生区外参培人数、学生公办参培人数、学生民办参培人数、当年退费人次(分四个季度)、学科(5个大类)、总收入(分四个季度)、总退费(分四个季度)

SQL如下：

select e.id_, e.schoolName,
count(IF(cs.status = 0 && (STR_TO_DATE(cs.endTime,'%Y-%m-%d') >= CURDATE()),true,null)) as s1, #学生参培总数  cs.status = 0 && 班课 时间在期内
count(IF(cs.status = 0 && (STR_TO_DATE(cs.endTime,'%Y-%m-%d') >= CURDATE()) && cs.region = '区内',true,null)) as s2, #学生区内参培人数 cs.status = 0 && 班课 时间在期内 && region = 区内
count(IF(cs.status = 0 && (STR_TO_DATE(cs.endTime,'%Y-%m-%d') >= CURDATE()) && cs.region = '区外',true,null)) as s3, #学生区外参培人数 cs.status = 0 && 班课 时间在期内 && region = 区外
count(IF(cs.status = 0 && (STR_TO_DATE(cs.endTime,'%Y-%m-%d') >= CURDATE()) && cs.schoolType = '公办',true,null)) as s4, #学生公办参培人数 cs.status = 0 && 班课 时间在期内 && schoolType = 公办
count(IF(cs.status = 0 && (STR_TO_DATE(cs.endTime,'%Y-%m-%d') >= CURDATE()) && cs.schoolType = '民办',true,null)) as s5, #学生民办参培人数 cs.status = 0 && 班课 时间在期内 && schoolType = 民办
count(IF(cs.status = 1 && (YEAR(cs.leaveTime) = YEAR(NOW())) && (QUARTER(cs.leaveTime) = 1),true,null)) as s6_1,  #当年退费人次(分四个季度)
count(IF(cs.status = 1 && (YEAR(cs.leaveTime) = YEAR(NOW())) && (QUARTER(cs.leaveTime) = 2),true,null)) as s6_2,
count(IF(cs.status = 1 && (YEAR(cs.leaveTime) = YEAR(NOW())) && (QUARTER(cs.leaveTime) = 3),true,null)) as s6_3,
count(IF(cs.status = 1 && (YEAR(cs.leaveTime) = YEAR(NOW())) && (QUARTER(cs.leaveTime) = 4),true,null)) as s6_4,

count(IF(cs.status = 0 && (YEAR(cs.enterClassTime) = YEAR(NOW())) && (QUARTER(cs.enterClassTime) = 1),true,null)) as s7_1, #当年新增人次(分四个季度)
count(IF(cs.status = 0 && (YEAR(cs.enterClassTime) = YEAR(NOW())) && (QUARTER(cs.enterClassTime) = 2),true,null)) as s7_2,
count(IF(cs.status = 0 && (YEAR(cs.enterClassTime) = YEAR(NOW())) && (QUARTER(cs.enterClassTime) = 3),true,null)) as s7_3,
count(IF(cs.status = 0 && (YEAR(cs.enterClassTime) = YEAR(NOW())) && (QUARTER(cs.enterClassTime) = 4),true,null)) as s7_4,

count(IF(cs.status = 0 && cs.courseType = '学科类',true,null)) as s8_1, #学科类
count(IF(cs.status = 0 && cs.courseType = '艺体类',true,null)) as s8_2, #艺体类
count(IF(cs.status = 0 && cs.courseType = '成人助学类',true,null)) as s8_3, #成人助学类
count(IF(cs.status = 0 && cs.courseType = '科普类',true,null)) as s8_4, #科普类
count(IF(cs.status = 0 && cs.courseType = '营地教育类',true,null)) as s8_5,  #营地教育类

SUM(IF(QUARTER(cs.enterClassTime) = 1 && YEAR(cs.enterClassTime) = YEAR(NOW()), cs.money - cs.refund, 0)) as s10_1, #总收入(分四个季度)
SUM(IF(QUARTER(cs.enterClassTime) = 2 && YEAR(cs.enterClassTime) = YEAR(NOW()), cs.money - cs.refund, 0)) as s10_2,
SUM(IF(QUARTER(cs.enterClassTime) = 3 && YEAR(cs.enterClassTime) = YEAR(NOW()), cs.money - cs.refund, 0)) as s10_3,
SUM(IF(QUARTER(cs.enterClassTime) = 4 && YEAR(cs.enterClassTime) = YEAR(NOW()), cs.money - cs.refund, 0)) as s10_4,

SUM(IF(cs.status = 1 && QUARTER(cs.leaveTime) = 1 && YEAR(cs.enterClassTime) = YEAR(NOW()), cs.refund, 0)) as s11_1,#总退费(分四个季度)
SUM(IF(cs.status = 1 && QUARTER(cs.leaveTime) = 2 && YEAR(cs.enterClassTime) = YEAR(NOW()), cs.refund, 0)) as s11_2,
SUM(IF(cs.status = 1 && QUARTER(cs.leaveTime) = 3 && YEAR(cs.enterClassTime) = YEAR(NOW()), cs.refund, 0)) as s11_3,
SUM(IF(cs.status = 1 && QUARTER(cs.leaveTime) = 4 && YEAR(cs.enterClassTime) = YEAR(NOW()), cs.refund, 0)) as s11_4  
from 
(select e4.* from lqy_educationadd e4 where e4.status = 0) e 
LEFT JOIN 
(
select 
s.schoolId, #学校id
s.name, #学生姓名
cs.courseId, #班课id
cs.studentId, #学生id
cs.money, #实际缴费金额
cs.refund, #退费金额
cs.status, #是否退学
c.endTime, #班课结束时间
c.courseType,#类别
s.region, #居住范围(区内  区外)
s.schoolType, #学校性质(民办  公办) 
cs.leaveTime,#退学时间
cs.enterClassTime #入班时间

from lqy_course c,lqy_student s, lqy_course_student cs 
where cs.courseId = c.id_ 
and cs.studentId = s.id_ 
and c.status = 0 
and s.status = 0
) cs 
on 
e.id_ = cs.schoolId GROUP BY e.id_;