如何直接复制已有表结构或者表&数据
1.复制表结构
create table table_name_new like table_name_old;
2.复制表结构&数据
create table table_name_new as (select * from table_name_old where 查询条件);
如何计算两个时间之间时间差、工作时间差、跨天工作时间差值
以下以 MySQL 数据库为例进行说明。
一、使用 TIMEDIFF 函数计算简单时间差
首先,可以使用 TIMEDIFF 函数计算两个时间的时间差,但这只是普通的时间差,不考虑工作时间。
SELECT TIMEDIFF(end_time, start_time) AS simple_time_diff;
二、考虑工作时间差的计算方法
定义工作日和工作时间范围,假设工作日为周一至周五,每天工作时间为 9:00 至 18:00。计算总分钟数:
将时间转换为分钟数进行计算。可以使用 HOUR 和 MINUTE 函数分别获取小时和分钟数,然后计算总分钟数。
SELECT
-- 开始时间的总分钟数
HOUR(start_time) * 60 + MINUTE(start_time) AS start_total_minutes,
-- 结束时间的总分钟数
HOUR(end_time) * 60 + MINUTE(end_time) AS end_total_minutes;
- 处理跨天情况
如果结束时间小于开始时间,说明跨天了,需要将结束时间加上一天的总分钟数(24 * 60 = 1440 分钟)。
SELECT
start_total_minutes,
IF(end_total_minutes < start_total_minutes, end_total_minutes + 1440, end_total_minutes) AS adjusted_end_total_minutesFROM (
SELECT
HOUR(start_time) * 60 + MINUTE(start_time) AS start_total_minutes,
HOUR(end_time) * 60 + MINUTE(end_time) AS end_total_minutes
FROM your_table) AS subquery;
- 计算工作时间差
遍历每一天,判断是否为工作日,如果是工作日并且在工作时间范围内,则将该时间段计入工作时间差。
SELECT
SUM(CASE
WHEN day_of_week BETWEEN 2 AND 6 AND -- 周二至周六(周日为 1,周一为 2,以此类推)
start_minutes <= end_minutes AND
start_minutes >= work_start_minutes AND end_minutes <= work_end_minutes THEN end_minutes - start_minutes
WHEN day_of_week BETWEEN 2 AND 6 AND
start_minutes <= end_minutes AND
start_minutes < work_start_minutes AND end_minutes <= work_end_minutes THEN end_minutes - work_start_minutes
WHEN day_of_week BETWEEN 2 AND 6 AND
start_minutes <= end_minutes AND
start_minutes >= work_start_minutes AND end_minutes > work_end_minutes THEN work_end_minutes - start_minutes
WHEN day_of_week BETWEEN 2 AND 6 AND
start_minutes <= end_minutes AND
start_minutes < work_start_minutes AND end_minutes > work_end_minutes THEN work_end_minutes - work_start_minutes
WHEN day_of_week BETWEEN 2 AND 6 AND
start_minutes > end_minutes THEN (work_end_minutes - start_minutes) + (end_minutes - work_start_minutes)
ELSE 0
END) AS working_time_diff_in_minutesFROM (
SELECT
start_time,
end_time,
-- 开始时间的总分钟数
HOUR(start_time) * 60 + MINUTE(start_time) AS start_minutes,
-- 结束时间的总分钟数,如果跨天则加上一天的总分钟数
IF(HOUR(end_time) * 60 + MINUTE(end_time) < HOUR(start_time) * 60 + MINUTE(start_time), HOUR(end_time) * 60 + MINUTE(end_time) + 1440, HOUR(end_time) * 60 + MINUTE(end_time)) AS end_minutes,
-- 开始时间的星期几,1 表示周日,2 表示周一,以此类推
WEEKDAY(start_time) + 1 AS day_of_week,
540 AS work_start_minutes, -- 9:00 的分钟数
1080 AS work_end_minutes -- 18:00 的分钟数
FROM your_table) AS subquery;
上述代码首先将时间转换为分钟数,然后根据星期几和工作时间范围进行判断,计算出工作时间差的分钟数。最后可以根据需要将分钟数转换为其他时间格式,如小时和分钟。
请注意,以上代码仅为示例,实际应用中可能需要根据具体情况进行调整,例如考虑节假日等因素。同时,不同的数据库系统可能有不同的函数和语法,需要根据实际情况进行修改。