SELECT ak.dept_id deptId,ak.dept_name deptName,ak.parent_id parentId, IFNULL(pk.primaryShould,0) primaryShould,IFNULL(pk.primaryNot,0) primaryNot,IFNULL(pk.primaryGraduated,0) primaryGraduated, IFNULL(mk.middleShould,0) middleShould,IFNULL(mk.middleNot,0) middleNot,IFNULL(mk.middleGraduated,0) middleGraduated, IFNULL(hk.highShould,0) highShould,IFNULL(hk.highGraduated,0) highGraduated,IFNULL(hk.highNot,0) highNot FROM sys_dept ak LEFT JOIN (SELECT subordinate_school, SUM(CASE WHEN grade = 6 THEN 1 ELSE 0 END) primaryShould, SUM(CASE WHEN grade = 6 AND is_graduation = 'N' THEN 1 ELSE 0 END) primaryNot, SUM(CASE WHEN grade = 6 AND is_graduation = 'Y' THEN 1 ELSE 0 END) primaryGraduated FROM primary_school_roll WHERE primary_student_id LIKE concat('20', '%') GROUP BY subordinate_school) pk ON ak.dept_id = pk.subordinate_school LEFT JOIN (SELECT subordinate_school, SUM(CASE WHEN grade = 9 THEN 1 ELSE 0 END) middleShould, SUM(CASE WHEN grade = 9 AND is_graduation = 'N' THEN 1 ELSE 0 END) middleNot, SUM(CASE WHEN grade = 9 AND is_graduation = 'Y' THEN 1 ELSE 0 END) middleGraduated FROM middle_school_roll WHERE middle_student_id LIKE concat('20', '%') GROUP BY subordinate_school) mk ON ak.dept_id = mk.subordinate_school LEFT JOIN (SELECT subordinate_school, SUM(CASE WHEN grade = 12 THEN 1 ELSE 0 END) highShould, SUM(CASE WHEN graduation_way = '001' AND is_graduation = 'T' THEN 1 ELSE 0 END) highNot, SUM(CASE WHEN graduation_way = '000' AND is_graduation = 'Y' THEN 1 ELSE 0 END) highGraduated FROM high_school_roll WHERE high_student_id LIKE concat('20', '%') GROUP BY subordinate_school) hk ON ak.dept_id = hk.subordinate_school WHERE ak.state = 3 GROUP BY ak.dept_id
时间: 2024-02-15 13:28:10 浏览: 22
这是一条 SQL 查询语句,根据部门ID将三个子查询的结果进行合并,最终返回各部门的信息以及不同年级的学生人数及毕业情况。
具体实现过程是,使用 LEFT JOIN 连接 sys_dept 表和三个子查询的结果集,根据部门ID进行关联。在子查询中,使用 SUM() 函数对不同年级的学生人数进行求和,然后根据不同的条件进行分类统计。最后,使用 GROUP BY 对查询结果按部门ID进行分组,以得到每个部门的统计结果。
需要注意的是,这条查询语句中使用了 IFNULL() 函数对查询结果进行处理,以避免在统计结果为 NULL 时出现错误。同时,也需要注意在使用 LIKE 进行模糊查询时,应该使用索引来提高查询效率。
相关问题
SELECT t.dept_id FROM sys_dept t WHERE find_in_set(#{deptId}, ancestors)
这是一个SQL语句,它的作用是在sys_dept表中查询所有祖先节点包含指定deptId的部门ID。其中,#{deptId}是一个占位符,具体的值需要在执行SQL语句时传入。该语句使用了MySQL中的find_in_set函数,该函数可以在一个逗号分隔的字符串列表中查找指定的值,如果找到则返回该值在列表中的位置,否则返回0。在此语句中,ancestors列存储了每个部门的所有祖先节点的ID,以逗号分隔的形式存储。
SELECT t.id AS userId, t1.user_name AS userName, t.create_time AS createTime, t1.login_id AS loginId, t1.mobile_phone AS mobilePhone, t.user_status AS userStatus, t1.mailbox AS mailbox, t1.dept_id AS deptId, t3.dept_name AS deptName FROM sys_user t LEFT JOIN sys_user_info t1 ON (t1.login_id = t.login_id) left join sys_dept t3 ON (t1.dept_id = t3.dept_id) <where> t.del_flg = '0' <if test="loginId != null and loginId != ''"> and (t.login_id LIKE CONCAT('%',#{loginId},'%') or t1.user_name LIKE CONCAT('%',#{loginId},'%')) </if> <if test="deptId != null and deptId != ''"> and ( t1.dept_id = #{deptId} or t3.ANCESTORS like CONCAT(#{deptId},',%') or t3.ANCESTORS like CONCAT('%,',#{deptId},',%') or t3.ANCESTORS like CONCAT('%,',#{deptId})) </if> <if test="mobilePhone != null and mobilePhone != ''"> and t1.mobile_phone LIKE CONCAT('%',#{mobilePhone},'%') </if> <if test="userStatus != null and userStatus != ''"> and t.user_status = #{userStatus} </if> <if test="startDate != null and endDate != null"> and t.create_time >= #{startDate} </if> <if test="endDate != null"> and t.create_time <= #{endDate} </if> </where> order by t.create_time desc 帮我优化一下这段sql
这段 SQL 查询语句已经很好了,但是可以根据实际情况进行优化,下面是一些可能的优化建议:
1. 添加索引:可以为 `sys_user` 表的 `login_id` 和 `del_flg` 列以及 `sys_user_info` 表的 `dept_id` 和 `mobile_phone` 列添加索引,这有助于提高查询性能。
2. 使用 EXISTS 替代 LEFT JOIN:可以使用 EXISTS 子查询替代 LEFT JOIN 子查询,因为 EXISTS 子查询只需要判断是否存在满足条件的记录即可,而不需要返回所有匹配的记录集合,这样可以提高查询效率。
3. WHERE 子句中的条件顺序:将常量条件放在 WHERE 子句的前面,变量条件放在后面,这样可以优化查询执行计划。
4. 去掉不必要的括号:可以去掉 WHERE 子句中的一些不必要的小括号,这样可以使 SQL 语句更简洁明了。
下面是优化后的 SQL 查询语句:
```
SELECT t.id AS userId, t1.user_name AS userName, t.create_time AS createTime, t1.login_id AS loginId, t1.mobile_phone AS mobilePhone, t.user_status AS userStatus, t1.mailbox AS mailbox, t1.dept_id AS deptId, t3.dept_name AS deptName
FROM sys_user t
LEFT JOIN sys_user_info t1 ON t1.login_id = t.login_id
LEFT JOIN sys_dept t3 ON t1.dept_id = t3.dept_id
WHERE t.del_flg = '0'
AND t.login_id LIKE CONCAT('%', #{loginId}, '%') OR t1.user_name LIKE CONCAT('%', #{loginId}, '%')
AND t1.dept_id = #{deptId} OR EXISTS (SELECT 1 FROM sys_dept d WHERE d.dept_id = #{deptId} AND t3.ANCESTORS LIKE CONCAT(d.ANCESTORS, '%'))
AND t1.mobile_phone LIKE CONCAT('%', #{mobilePhone}, '%')
AND t.user_status = #{userStatus}
AND t.create_time >= #{startDate}
AND t.create_time <= #{endDate}
ORDER BY t.create_time DESC
```
注意:上述 SQL 查询语句仅供参考,具体优化建议需要根据实际情况进行调整。