select pvr.*, ((select sd.dept_id from statistic_dept sd where sd.parent_id='0044' and sd.statistic_type != '3' and sd.statistic_level='2') - COALESCE(s1.city, 0))+'/' +(select sd.dept_id from statistic_dept sd where sd.parent_id='0044' and sd.statistic_type = '3' and sd.statistic_level='2') city, ((select sd.dept_id from statistic_dept sd where sd.parent_id='0044' and sd.statistic_type = '3' and sd.statistic_level='2') - COALESCE(s2.province, 0)) +'/'+(select sd.dept_id from statistic_dept sd where sd.parent_id='0044' and sd.statistic_type = '3' and sd.statistic_level='2') province from portal_virtual_rule pvr left join ( select COALESCE(COUNT(pvrcr.city_dept_id), 0) city, pvrcr.rule_code from portal_virtual_rule_check_record pvrcr where pvrcr.province_dept_id = '0044' and pvrcr.check_result = '2' and pvrcr.dept_id not in (select sd.dept_id from statistic_dept sd where sd.parent_id='0044' and sd.statistic_type = '3' and sd.statistic_level='2') group by pvrcr.city_dept_id , pvrcr.rule_code) s1 on s1.rule_code = pvr.code left join ( select COALESCE(COUNT(pvrcr.city_dept_id), 0) province, pvrcr.rule_code from portal_virtual_rule_check_record pvrcr where pvrcr.province_dept_id = '0044' and pvrcr.check_result = '2' and pvrcr.dept_id in (select sd.dept_id from statistic_dept sd where sd.parent_id='0044' and sd.statistic_type = '3' and sd.statistic_level='2') group by pvrcr.city_dept_id , pvrcr.rule_code) s2 on s2.rule_code = pvr.code mysql5.7如何修改

### 复杂 SQL 查询语句优化与修改 #### 使用 COALESCE 函数处理 NULL 值 `COALESCE` 是一种用于返回第一个非 `NULL` 表达式的函数，在复杂查询中常用来替代多个 `IFNULL` 或者手动判断逻辑。例如，当需要计算某个字段的默认值时，可以直接使用 `COALESCE(column_name, default_value)` 来简化代码结构[^1]。 ```sql SELECT job, COALESCE(avg_sal, 0) AS avg_salary FROM ( SELECT job, AVG(sal) AS avg_sal FROM emp GROUP BY job ) subquery; ``` --- #### 避免深层嵌套子查询 尽管可以通过多层嵌套实现功能需求，但为了提高性能和可维护性，应尽量减少嵌套层数。通过合理利用临时表或者视图存储中间结果，能够显著改善查询效率并增强可读性。 示例：将原本三层嵌套改为两步操作——先创建一个带聚合结果的临时表/视图，再基于此进一步筛选： ```sql -- 创建临时表保存每种职位对应的平均薪资 CREATE TEMPORARY TABLE temp_avg_salaries AS SELECT job, AVG(sal) AS avg_sal FROM emp GROUP BY job; -- 利用 JOIN 实现最终目标而不增加额外层次 SELECT e.job, t.avg_sal FROM emp e LEFT JOIN temp_avg_salaries t ON e.job = t.job; DROP TEMPORARY TABLE temp_avg_salaries; ``` --- #### 正确应用 GROUP BY 和 HAVING 子句 在 MySQL 中，`GROUP BY` 主要用于按特定列分组统计汇总信息；而 `HAVING` 则是对这些已分组的数据施加过滤条件。注意区分它与普通的 WHERE 子句作用范围不同之处。 错误示范 (未正确匹配分组字段): ```sql SELECT job, AVG(sal), COUNT(*) FROM emp WHERE AVG(sal) > 2000 -- 错误写法: aggregate function cannot be used here. GROUP BY job; ``` 修正后的版本: ```sql SELECT job, AVG(sal) as average_salary, COUNT(*) as employee_count FROM emp GROUP BY job HAVING AVG(sal) > 2000 AND COUNT(*) >= 5; -- 只保留满足条件的结果集 ``` --- #### Join 类型的选择及其影响 根据实际业务场景选取合适的连接方式至关重要。常见的几种形式如下所示: - **INNER JOIN**: 返回两张表格中共有的记录部分； - **LEFT OUTER JOIN / RIGHT OUTER JOIN**: 获取左表或右表全部数据加上另一侧符合条件的部分； - **FULL OUTER JOIN** *(不支持于标准MySql)* : 结合上述两种情况展示完全集合关系； 下面是一个例子说明如何运用 LEFT JOIN 解决可能存在的缺失关联键问题： ```sql SELECT d.department_id, d.name AS department_name, IFNULL(COUNT(e.id), 0) AS num_employees FROM departments d LEFT JOIN employees e USING(department_id) GROUP BY d.department_id, d.name; ``` 这里假设某些部门暂时没有任何雇员分配，则仍需呈现该条目，并设置人数计数为零. --- #### Binlog 对触发器定义的影响 如果数据库启用了二进制日志 (`bin-log`) 功能，则编写自定义存储过程或触发器时需要注意声明其行为特性。具体来说，这涉及到了四个选项之一：`DETERMINISTIC`, `NOT DETERMINISTIC`, `READS SQL DATA`, `MODIFIES SQL DATA`. 这些标签帮助复制机制更好地理解和同步事务变化[^2]. 例如，当我们希望确保某项更新不会改变原始输入参数状态的同时也不会触碰其他外部资源的话，应该这样标记我们的程序单元： ```sql DELIMITER $$ CREATE TRIGGER before_emp_update BEFORE UPDATE ON emp FOR EACH ROW BEGIN SET NEW.modified_date = NOW(); END$$ DELIMITER ; ``` 此时无需特别注明附加属性，默认视为安全无害的操作. ---