如何创建含process_time列、按运行小时拆分行的MySQL视图？

这个需求的核心是把跨多个小时的流程拆分成每个覆盖小时单独一行记录，同时生成指定格式的process_time列。下面给你两种适配不同MySQL版本的实现方案：

方案一：MySQL 8.0+（支持递归CTE） #

递归CTE可以灵活生成所需的小时偏移序列，不用手动写大量union语句：

CREATE VIEW process_hourly_view AS
WITH RECURSIVE hour_offsets AS (
    SELECT 0 AS offset_hour
    UNION ALL
    SELECT offset_hour + 1 FROM hour_offsets
    WHERE offset_hour < 47 -- 这里设置的数值要大于你系统中流程最长运行小时数，47覆盖2天时长
)
SELECT
    p.process_id,
    DATE_FORMAT(DATE_ADD(p.process_start, INTERVAL h.offset_hour HOUR), '%Y-%m-%d %H') AS process_time,
    p.data_1,
    p.data_2
FROM
    your_table_name p -- 替换成你的实际表名
JOIN
    hour_offsets h 
    ON DATE_ADD(p.process_start, INTERVAL h.offset_hour HOUR) <= p.process_end
WHERE
    -- 确保生成的小时不超过流程结束时间所在的小时
    DATE_FORMAT(DATE_ADD(p.process_start, INTERVAL h.offset_hour HOUR), '%Y-%m-%d %H') 
    <= DATE_FORMAT(p.process_end, '%Y-%m-%d %H')
ORDER BY
    p.process_id, process_time;

代码说明： #

• hour_offsets：递归生成从0开始的整数序列，每个数字代表从流程开始时间往后的小时偏移量。
• 关联条件：确保偏移后的时间不超过流程的结束时间，同时通过DATE_FORMAT过滤掉超过结束小时的记录（比如流程21:56结束，不会生成22点的行）。
• DATE_FORMAT：把偏移后的时间格式化为YYYY-MM-DD HH的格式，正好是你需要的process_time。

方案二：MySQL 5.x（不支持递归CTE） #

如果你的MySQL版本较低，就用手动生成数字序列的方式替代递归CTE：

CREATE VIEW process_hourly_view AS
SELECT
    p.process_id,
    DATE_FORMAT(DATE_ADD(p.process_start, INTERVAL h.offset_hour HOUR), '%Y-%m-%d %H') AS process_time,
    p.data_1,
    p.data_2
FROM
    your_table_name p -- 替换成你的实际表名
JOIN (
    -- 手动生成足够多的数字，覆盖最长的流程运行小时数
    SELECT 0 AS offset_hour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
    UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
    UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
    -- 可以继续添加更多数字，比如UNION ALL SELECT 25...来支持更长的流程
) h 
ON DATE_ADD(p.process_start, INTERVAL h.offset_hour HOUR) <= p.process_end
WHERE
    DATE_FORMAT(DATE_ADD(p.process_start, INTERVAL h.offset_hour HOUR), '%Y-%m-%d %H') 
    <= DATE_FORMAT(p.process_end, '%Y-%m-%d %H')
ORDER BY
    p.process_id, process_time;

验证结果 #

这两种方案都能生成你期望的视图结果：比如process_id=2会生成15到21点的7行，process_id=3会生成21和22点的两行，完全匹配需求。

内容的提问来源于stack exchange，提问作者Alon