<think>
我们已知：用户需要计算工作日的加班时长，规则如下：
1. 只考虑工作日（周末不考虑，但当前查询已经限定工作日）
2. 基础加班时长 = 实际在岗时长 - 上午、中午、下午休息时段的扣除
3. 净加班时长 = 基础加班时长 - 8小时（即扣除标准工作时长）
4. 如果净加班时长为负数，则不统计（即取0）
根据之前的逻辑，我们已经在在岗时长中扣除了三个休息时段。现在要求在此基础上再减去8小时（标准工作时间），并且当结果为负时取0。
因此，我们需要调整原来的计算：
total_hours = (在岗时长 - 休息时段扣除) - 8
如果total_hours < 0，则取0
但是注意：原查询已经计算了扣除休息时段后的在岗时长（即基础加班时长），现在需要在此基础上减去8，并处理负数。
原查询计算的是单条记录的时长（扣除休息时段），然后按用户分组求和。但这里有一个问题：我们是否应该先对每条记录计算扣除休息时段后的时长，再减去8小时？但注意，8小时是标准工作时间，而加班计算通常是在一个工作日内，所以应该是每条记录（代表一天）计算后减去8小时，然后取非负值，最后再按用户汇总。
所以步骤：
1. 对每条记录，计算扣除休息时段后的在岗时长（即原查询中的计算，但不要按用户分组，而是先计算每条记录的时长）
2. 然后，将该时长减去8小时，如果结果小于0则取0（即一天加班时长=MAX(0, 扣除休息时段后的时长-8)）
3. 最后按用户分组求和
因此，我们需要先构建子查询，计算每条记录的加班时长（已经扣除休息时段并减去8小时后的非负值），然后按用户分组求和。
修改后的SQL如下：
注意：原查询中的休息时段扣除逻辑保持不变（上午、中午、下午三个时段）
我们将原查询中的计算部分单独拿出来作为每条记录的基础时长（扣除休息时段），然后减去8，再取非负，最后分组求和。
由于原查询中已经计算了扣除休息时段后的时长（但原查询是直接按用户分组求和，没有先减8），现在需要调整。
修改步骤：
1. 将原查询中的计算部分作为子查询的内层，得到每条记录扣除休息时段后的时长（称为daily_hours）
2. 然后计算每日加班时长：GREATEST(0, daily_hours - 8)
3. 最后按用户分组求和，并保留两位小数
注意：原查询中的休息时段扣除逻辑中，使用了TRUNC(TO_DATE(starttime, ...))来获取日期部分。这里假设每条记录都是同一天的（即没有跨天记录）。如果有跨天记录，则需要另外处理，但用户需求是工作日加班，通常不跨天。
因此，SQL可以这样写：
```sql
SELECT
userid,
username,
ROUND(SUM(daily_overtime), 2) AS total_overtime_hours
FROM (
SELECT
userid,
username,
-- 计算扣除休息时段后的每日在岗时长
( (TO_DATE(endtime, 'YYYY-MM-DD HH24:MI:SS') - TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) * 24
-
(
-- 早餐重叠时长（小时）
GREATEST(0,
LEAST(TO_DATE(endtime, 'YYYY-MM-DD HH24:MI:SS'), TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + (8/24) + (45/24/60))
-
GREATEST(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS'), TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + (8/24) + (15/24/60))
) * 24
+
-- 午餐重叠时长（小时）
GREATEST(0,
LEAST(TO_DATE(endtime, 'YYYY-MM-DD HH24:MI:SS'), TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + (12/24) + (30/24/60))
-
GREATEST(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS'), TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + (12/24))
) * 24
+
-- 晚餐重叠时长（小时）
GREATEST(0,
LEAST(TO_DATE(endtime, 'YYYY-MM-DD HH24:MI:SS'), TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + (17/24) + (45/24/60))
-
GREATEST(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS'), TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + (17/24) + (15/24/60))
) * 24
)
) AS daily_hours
FROM work_record
-- 假设有一个字段is_weekday来标记工作日，或者根据日期判断，但用户要求只考虑工作日且不考虑周末，所以这里需要过滤
WHERE ... -- 这里添加条件过滤工作日，例如：TO_CHAR(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS'), 'D') NOT IN (6,7) 表示排除周六周日（根据数据库设置，周日为1，周六为6，周日为7？）
)
-- 然后计算每日加班时长（扣除8小时，负数取0）
, daily_overtime AS (
SELECT
userid,
username,
GREATEST(0, daily_hours - 8) AS daily_overtime
FROM daily_data
)
-- 最后按用户分组求和
GROUP BY userid, username;
```
但是，由于用户没有提供如何判断工作日的条件，这里假设有一个条件可以过滤工作日。如果不知道如何判断，可能需要用户提供。
另外，注意原查询中使用了TRUNC(TO_DATE(starttime, ...))来获取日期部分，这里要求每条记录都是同一天的。如果一条记录跨天，则计算结果可能不准确。
由于用户要求只统计工作日，所以我们在子查询中先过滤工作日记录。
另外，我们还需要注意：原查询中使用了starttime来截断日期，如果一条记录跨天，则使用starttime的日期可能不准确（比如从今天工作到明天），但通常加班记录不会跨天，所以暂时这样处理。
优化：我们可以使用TRUNC(starttime)来获取日期，但这里已经用了TO_DATE转换，所以直接使用TRUNC(TO_DATE(starttime, ...))。
因此，最终SQL如下（假设使用TO_CHAR判断工作日，排除周六周日）：
```sql
SELECT
userid,
username,
ROUND(SUM(
GREATEST(0, daily_hours - 8)
), 2) AS total_overtime_hours
FROM (
SELECT
userid,
username,
( (TO_DATE(endtime, 'YYYY-MM-DD HH24:MI:SS') - TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) * 24
-
(
-- 早餐重叠时长（小时）
GREATEST(0,
LEAST(TO_DATE(endtime, 'YYYY-MM-DD HH24:MI:SS'), TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + (8/24) + (45/24/60))
-
GREATEST(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS'), TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + (8/24) + (15/24/60))
) * 24
+
-- 午餐重叠时长（小时）
GREATEST(0,
LEAST(TO_DATE(endtime, 'YYYY-MM-DD HH24:MI:SS'), TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + (12/24) + (30/24/60))
-
GREATEST(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS'), TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + (12/24))
) * 24
+
-- 晚餐重叠时长（小时）
GREATEST(0,
LEAST(TO_DATE(endtime, 'YYYY-MM-DD HH24:MI:SS'), TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + (17/24) + (45/24/60))
-
GREATEST(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS'), TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + (17/24) + (15/24/60))
) * 24
)
) AS daily_hours
FROM work_record
WHERE TO_CHAR(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS'), 'D') NOT IN ('6','7') -- 排除周六和周日（根据实际设置调整，有些系统周日是1，周六是7，这里用'6'和'7'代表周六和周日）
) t
GROUP BY userid, username;
```
注意：这里的工作日判断可能因数据库设置不同而不同，例如，有些地区把周一定为1，周日定为7，而有些地区把周日定为1，周六定为7。所以需要根据实际情况调整。
另外，如果用户有节假日表，还需要排除节假日。但用户当前需求没有提到节假日，所以暂时只排除周末。
如果用户需要排除节假日，那么需要关联节假日表，这里不展开。
最后，我们按用户分组，将每日加班时长（已经减去8小时且负数取0）求和，并四舍五入到两位小数。
但是，注意：原查询中休息时段的扣除逻辑中，上午休息时段是8:15到8:45，午餐12:00到12:30，下午17:15到17:45。这些时段是固定的，所以计算重叠时间的方法是正确的。
另外，如果用户的一条记录并没有覆盖整个休息时段，那么重叠计算会自动取实际重叠部分。
因此，这个SQL应该能够满足需求。
最后，如果用户需要考虑跨天的情况，那么需要重新设计，但这里假设不跨天。
如果存在跨天记录，那么使用TRUNC(TO_DATE(starttime,...))获取的日期就不准确了（因为endtime可能到了第二天），此时应该用两个日期分别判断，但用户需求是工作日，跨天记录可能很少，而且通常加班不会跨天，所以暂时不考虑。
如果确实有跨天记录，建议将一条记录拆分成两天分别计算，但这样会复杂很多。
综上所述，我们给出上述SQL。
</think>### 工作日加班时长统计方案
根据需求，工作日加班时长的计算逻辑如下：
1. **只统计工作日**（排除周末）
2. **基础加班时长** = 实际在岗时长 - 休息时段（上午、中午、下午）
3. **净加班时长** = 基础加班时长 - 8小时（标准工作时长）
4. **负数处理**：当净加班时长 ≤ 0 时不统计（取0）
以下是完整的SQL实现方案：
```sql
SELECT
userid,
username,
ROUND(SUM(
GREATEST(0,
-- 基础在岗时长（小时）
(TO_DATE(endtime, 'YYYY-MM-DD HH24:MI:SS') -
TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) * 24
-- 扣除上午休息时段（08:15-08:45）
- GREATEST(0,
LEAST(TO_DATE(endtime, 'YYYY-MM-DD HH24:MI:SS'),
TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + 8/24 + 45/24/60
) -
GREATEST(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS'),
TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + 8/24 + 15/24/60
)
) * 24
-- 扣除中午休息时段（12:00-12:30）
- GREATEST(0,
LEAST(TO_DATE(endtime, 'YYYY-MM-DD HH24:MI:SS'),
TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + 12/24 + 30/24/60
) -
GREATEST(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS'),
TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + 12/24
)
) * 24
-- 扣除下午休息时段（17:15-17:45）
- GREATEST(0,
LEAST(TO_DATE(endtime, 'YYYY-MM-DD HH24:MI:SS'),
TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + 17/24 + 45/24/60
) -
GREATEST(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS'),
TRUNC(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS')) + 17/24 + 15/24/60
)
) * 24
- 8 -- 减去标准工作时长8小时
)
), 2) AS overtime_hours
FROM work_record
-- 排除周末（1=周日，7=周六，根据实际NLS设置调整）
WHERE TO_CHAR(TO_DATE(starttime, 'YYYY-MM-DD HH24:MI:SS'), 'D')
NOT IN ('1','7')
GROUP BY userid, username;
```
### 关键计算逻辑说明
1. **休息时段扣除公式**：
- 重叠时长 = $MAX(0, MIN(结束时间, 休息结束) - MAX(开始时间, 休息开始))$
- 上午休息：$休息开始 = TRUNC(日期) + \frac{8}{24} + \frac{15}{24×60}$
- 上午结束：$休息结束 = TRUNC(日期) + \frac{8}{24} + \frac{45}{24×60}$
2. **净加班时长计算**：
$$
\text{净加班时长} =
\begin{cases}
(\text{实际时长} - \sum{\text{休息重叠时长}} - 8) & \text{当结果} > 0 \\
0 & \text{当结果} \leq 0
\end{cases}
$$
3. **日期处理技巧**：
- `TRUNC(date)`：截取日期部分（去除时间）
- 时间计算：$\frac{小时}{24} + \frac{分钟}{24×60}$（转换为天的小数）
- `TO_CHAR(..., 'D')`：获取星期几（1=周日，7=周六）
### 执行效果示例
| USERID | USERNAME | OVERTIME_HOURS |
|--------|----------|----------------|
| 1001 | 张三 | 10.5 |
| 1002 | 李四 | 3.25 |
| 1003 | 王五 | 0.00 | <!-- 净加班≤0 -->
### 注意事项
1. **日期格式验证**：
```sql
-- 检查实际数据格式
SELECT starttime FROM work_record WHERE ROWNUM = 1;
```
确保格式与`'YYYY-MM-DD HH24:MI:SS'`完全匹配[^1]
2. **跨天记录处理**：
```sql
-- 添加跨天判断
CASE WHEN TRUNC(starttime) < TRUNC(endtime)
THEN 24*(TRUNC(endtime)-TRUNC(starttime)-1)
ELSE 0 END
```
3. **性能优化建议**：
```sql
-- 创建函数索引
CREATE INDEX idx_workdate ON work_record(TRUNC(TO_DATE(starttime)));
```
扫一扫
188
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
