DROP VIEW IF EXISTS class_data;
CREATE VIEW class_data AS 
SELECT t1.id, t1.name, t1.code,t2.name AS gradeName,t1.grade_id AS gradeId,t3.name AS teacherName,t3.mobile AS teacherMobile,t1.teacher_id AS teacherId,
t4.name AS enrollTypeCn,t1.enroll_type AS enrollType,t5.name AS classTypeCn,t1.class_type AS classType,
t6.name AS classroomName,t1.classroom_id AS classroomId,t7.name AS orgName,t1.org_id AS orgId,t8.name AS majorSetName,t1.major_set_id AS majorSetId,
t1.is_order_class AS isOrderClass,t1.is_graduate AS isGraduate,
(SELECT COUNT(DISTINCT(a1.id)) FROM xjr_user a1
LEFT JOIN base_student_school_roll a2 ON a1.id = a2.user_id
WHERE a1.delete_mark = 0 AND a2.delete_mark = 0
AND a1.gender = 'SB10001' AND a2.class_id = t1.id
AND a2.archives_status = 'FB2901') AS maleCount,
(SELECT COUNT(DISTINCT(a1.id)) FROM xjr_user a1
LEFT JOIN base_student_school_roll a2 ON a1.id = a2.user_id
WHERE a1.delete_mark = 0 AND a2.delete_mark = 0
AND a2.class_id = t1.id AND a2.stduy_status = 'FB3002'
AND a2.archives_status = 'FB2901' AND a1.gender = 'SB10001') AS maleStayCount,
(SELECT COUNT(DISTINCT(a1.id)) FROM xjr_user a1
LEFT JOIN base_student_school_roll a2 ON a1.id = a2.user_id
WHERE a1.delete_mark = 0 AND a2.delete_mark = 0
AND a2.class_id = t1.id AND a2.stduy_status = 'FB3001'
AND a2.archives_status = 'FB2901' AND a1.gender = 'SB10001') AS maleNotStayCount,
(SELECT COUNT(DISTINCT(a1.id)) FROM xjr_user a1
LEFT JOIN base_student_school_roll a2 ON a1.id = a2.user_id
WHERE a1.delete_mark = 0 AND a2.delete_mark = 0
AND a1.gender = 'SB10002' AND a2.class_id = t1.id
AND a2.archives_status = 'FB2901') AS femaleCount,
(SELECT COUNT(DISTINCT(a1.id)) FROM xjr_user a1
LEFT JOIN base_student_school_roll a2 ON a1.id = a2.user_id
WHERE a1.delete_mark = 0 AND a2.delete_mark = 0
AND a2.class_id = t1.id AND a2.stduy_status = 'FB3002'
AND a2.archives_status = 'FB2901' AND a1.gender = 'SB10002') AS femaleStayCount,
(SELECT COUNT(DISTINCT(a1.id)) FROM xjr_user a1
LEFT JOIN base_student_school_roll a2 ON a1.id = a2.user_id
WHERE a1.delete_mark = 0 AND a2.delete_mark = 0
AND a2.class_id = t1.id AND a2.stduy_status = 'FB3001'
AND a2.archives_status = 'FB2901' AND a1.gender = 'SB10002') AS femaleNotStayCount,
(SELECT COUNT(DISTINCT(a1.id)) FROM xjr_user a1
LEFT JOIN base_student_school_roll a2 ON a1.id = a2.user_id
WHERE a1.delete_mark = 0 AND a2.delete_mark = 0
AND a2.class_id = t1.id
AND a2.archives_status = 'FB2901') AS allCount
 FROM base_class t1
LEFT JOIN base_grade t2 ON t1.grade_id = t2.id
LEFT JOIN xjr_user t3 ON t1.teacher_id = t3.id
LEFT JOIN xjr_dictionary_detail t4 ON t4.code = t1.enroll_type
LEFT JOIN xjr_dictionary_detail t5 ON t5.code = t1.class_type
LEFT JOIN base_classroom t6 ON t6.id = t1.classroom_id
LEFT JOIN xjr_department t7 ON t7.id = t1.org_id
LEFT JOIN base_major_set t8 ON t8.id = t1.major_set_id
WHERE t1.delete_mark = 0;