mysql 的一点点记录
16lz
2021-01-22
以后再来整理。
-- 查询一个学校的报修单数 SELECT s.id AS schoolId , -- 学校ID COUNT(i.id) as cntId, -- 报修单数 IFNULL(t1.nohandlecnt,0) AS nohandlecnt, IFNULL(t2.overcnt,0) AS overcnt, ROUND(overcnt * 100 /COUNT(i.id),1) AS okRate FROM `t_issue` i LEFT JOIN ( -- 完成报修单数 SELECT s.id AS schoolId , -- 学校ID IFNULL(COUNT(i.id),0) as overcnt -- 完成的报修单数 FROM `t_issue` i INNER JOIN t_school s ON i.school_id = s.id WHERE i.completed_on IS NOT NULL and i.acknowledged_on IS NOT NULL GROUP BY i.school_id ) t2 ON t2.schoolId = i.school_id LEFT JOIN ( -- 未处理数 SELECT s.id AS schoolId , -- 学校ID IFNULL(COUNT(i.id),0) as nohandlecnt -- 未处理报修单数 FROM `t_issue` i INNER JOIN t_school s ON i.school_id = s.id WHERE i.acknowledged_on IS NULL AND i.completed_on IS NULL GROUP BY i.school_id ) t1 ON t1.schoolId = i.school_id INNER JOIN t_school s ON i.school_id = s.id GROUP BY i.school_id
更多相关文章
- 简单数据的最佳服务器端数据存储方法
- 在单页中使用ajax和php上传多个表单数据
- Spark RDD算子/SparkSQL分别实现对电影数据集的简单数据分析
- 求VB.net2010做一个简单数据库系统具体操作(用SQL SERVER2008)
- Android 简单数独开发
- 【JavaWeb-6】HttpServletResponse的字符字节输出流、编码、文件