如果不用判断类型，只要非工作时间拟稿的，处室、人名称

<think> 根据用户需求，不再区分文件类型，只需统计非工作时间的拟稿数量，按处室和拟稿人分组。由于达梦数据库中，星期几的表示：1代表星期日，2代表星期一，...，7代表星期六。非工作时间包括周末和工作日的9点前或18点后。以下是简化后的SQL： ```sql WITH draft_data AS ( SELECT biz_info_oid, created_by_name AS operator_name, NVL(unit_name, '未知处室') AS dept_name, created_date, TO_CHAR(created_date, 'MM') AS month_id FROM oa_common_biz_info WHERE TO_CHAR(created_date, 'YYYY') = '2025' -- 指定年份 ), offtime_drafts AS ( SELECT biz_info_oid, dept_name, operator_name, month_id FROM draft_data WHERE -- 周末判断 (达梦: 1=星期日, 7=星期六) TO_CHAR(created_date, 'd') IN ('1','7') OR -- 工作日非工作时间 (9:00前或18:00后) ( TO_CHAR(created_date, 'd') BETWEEN '2' AND '6' -- 星期一至星期五 AND (TO_CHAR(created_date, 'hh24mi') < '0900' OR TO_CHAR(created_date, 'hh24mi') >= '1800' ) ) ) SELECT dept_name AS 处室, operator_name AS 拟稿人, COUNT(CASE WHEN month_id = '01' THEN biz_info_oid END) AS 一月, COUNT(CASE WHEN month_id = '02' THEN biz_info_oid END) AS 二月, COUNT(CASE WHEN month_id = '03' THEN biz_info_oid END) AS 三月, COUNT(CASE WHEN month_id = '04' THEN biz_info_oid END) AS 四月, COUNT(CASE WHEN month_id = '05' THEN biz_info_oid END) AS 五月, COUNT(CASE WHEN month_id = '06' THEN biz_info_oid END) AS 六月, COUNT(CASE WHEN month_id = '07' THEN biz_info_oid END) AS 七月, COUNT(CASE WHEN month_id = '08' THEN biz_info_oid END) AS 八月, COUNT(CASE WHEN month_id = '09' THEN biz_info_oid END) AS 九月, COUNT(CASE WHEN month_id = '10' THEN biz_info_oid END) AS 十月, COUNT(CASE WHEN month_id = '11' THEN biz_info_oid END) AS 十一月, COUNT(CASE WHEN month_id = '12' THEN biz_info_oid END) AS 十二月, COUNT(biz_info_oid) AS 年度合计 FROM offtime_drafts GROUP BY dept_name, operator_name ORDER BY dept_name, operator_name; ``` ### 修改说明： 1. **移除文件类型**：不再需要CASE语句定义文类，直接统计拟稿记录。 2. **简化CTE结构**：`draft_data`中只保留必要的字段（部门、拟稿人、月份等）。 3. **非工作时间条件**：直接使用达梦的日期函数进行判断，确保逻辑正确。 4. **聚合方式**：使用`COUNT(CASE ...)`按月统计，最后汇总年度合计。 §§相关问题§§ 1. 如果拟稿人姓名和部门名称需要关联其他表获取（如用户表），SQL应如何修改？ 2. 如何调整非工作时间的定义（例如将中午休息时间12:00-14:00也排除）？ 3. 若需要同时展示总拟稿量（包括正常时间）和非工作时间拟稿量，应如何扩展SQL？ 4. 如何为这个查询创建视图以便重复使用？ 5. 达梦数据库是否有更高效的方式处理月份统计（如PIVOT函数）？ </think> 根据您的需求，简化查询仅为统计非工作时间拟稿数量，不再区分文件类型，按处室和拟稿人分组展示每月数据。以下是优化后的SQL： ```sql WITH draft_data AS ( SELECT biz_info_oid, created_by_name AS operator_name, NVL(unit_name, '未知处室') AS dept_name, created_date, TO_CHAR(created_date, 'MM') AS month_id FROM oa_common_biz_info WHERE TO_CHAR(created_date, 'YYYY') = '2025' -- 指定年份 ), offtime_drafts AS ( SELECT biz_info_oid, dept_name, operator_name, month_id FROM draft_data WHERE -- 周末判断 (达梦: 1=星期日, 7=星期六) TO_CHAR(created_date, 'd') IN ('1','7') OR -- 工作日非工作时间 (9:00前或18:00后) ( TO_CHAR(created_date, 'd') BETWEEN '2' AND '6' -- 星期一至星期五 AND (TO_CHAR(created_date, 'hh24mi') < '0900' OR TO_CHAR(created_date, 'hh24mi') >= '1800' ) ) ) SELECT dept_name AS 处室, operator_name AS 拟稿人, COUNT(CASE WHEN month_id = '01' THEN biz_info_oid END) AS 一月, COUNT(CASE WHEN month_id = '02' THEN biz_info_oid END) AS 二月, COUNT(CASE WHEN month_id = '03' THEN biz_info_oid END) AS 三月, COUNT(CASE WHEN month_id = '04' THEN biz_info_oid END) AS 四月, COUNT(CASE WHEN month_id = '05' THEN biz_info_oid END) AS 五月, COUNT(CASE WHEN month_id = '06' THEN biz_info_oid END) AS 六月, COUNT(CASE WHEN month_id = '07' THEN biz_info_oid END) AS 七月, COUNT(CASE WHEN month_id = '08' THEN biz_info_oid END) AS 八月, COUNT(CASE WHEN month_id = '09' THEN biz_info_oid END) AS 九月, COUNT(CASE WHEN month_id = '10' THEN biz_info_oid END) AS 十月, COUNT(CASE WHEN month_id = '11' THEN biz_info_oid END) AS 十一月, COUNT(CASE WHEN month_id = '12' THEN biz_info_oid END) AS 十二月, COUNT(biz_info_oid) AS 年度合计 FROM offtime_drafts GROUP BY dept_name, operator_name ORDER BY dept_name, operator_name; ``` ### 优化说明： 1. **简化结构**： - 移除文件类型分类（`WHEN...THEN`逻辑） - 减少不必要字段，只保留处室、拟稿人和月份信息 2. **达梦时间函数适配**： - 使用`TO_CHAR(created_date, 'd')`获取星期几（1=周日 → 7=周六） - 时间比较使用`TO_CHAR(created_date, 'hh24mi')`格式化成HH24MI字符串 3. **聚合优化**： - 使用`COUNT(CASE WHEN...END)`替代SUM，更直观统计计数 - 直接计数`biz_info_oid`确保每个OID只计一次 4. **结果展示**： - 按月分列统计非工作时间拟稿量 - 最后列显示年度合计总量 - 按处室和拟稿人分组排序