如何计算包含所有子工单的工单总工时？

需求说明 #

• 工单支持任意层级嵌套，子工单可继续嵌套子工单，深度无固定上限（目前观测最深7层）
• 工单可作为顶层工单（无父工单），也可无任何子工单
• 需计算每个工单的两个核心字段：
  • 直接工时：该工单自身所有工时记录的总和
  • 总工时：该工单直接工时 + 所有下属子工单（含所有层级）的总工时之和

表结构与测试数据 #

表定义 #

DROP TABLE IF EXISTS tickets;
CREATE TABLE tickets (
  ticketid INT,
  parentID INT
);

DROP TABLE IF EXISTS Hours;
CREATE TABLE Hours (
  ticketid INT,
  hours INT
);

测试数据 #

INSERT INTO tickets (ticketid, parentID)
VALUES 
('1','6'),('2','7'),('3','8'),('4','9'),('5','10'),
('6','18'),('7','19'),('8','20'),('9','21'),('10','22'),
('11','23'),('12','18'),('13','19'),('14','20'),('15','21'),
('16','22'),('17','23'),('18','24'),('19','25'),('20','26'),
('21','27'),('22','28'),('23','29'),('24','30'),('25','30'),
('26','30'),('27','30'),('28','30'),('29','30'),('30','');

INSERT INTO hours (ticketid, hours) 
VALUES 
('22','4'),('28','1'),('15','1'),('23','2'),('16','1'),('7','4'),('12','2'),('28','3'),('19','4'),('14','1'),
('10','4'),('18','5'),('3','2'),('6','3'),('7','2'),('23','5'),('26','2'),('26','4'),('14','2'),('6','1'),
('24','4'),('4','1'),('4','5'),('25','2'),('0','4'),('8','4'),('20','2'),('19','1'),('6','1'),('24','1'),
('26','2'),('15','5'),('1','1'),('2','5'),('3','2'),('4','1'),('5','2'),('6','3'),('7','2'),('8','5'),
('9','2'),('10','5'),('11','1'),('12','3'),('13','2'),('14','5'),('15','3'),('16','2'),('17','4'),('18','1'),
('19','3'),('20','2'),('21','5'),('22','3'),('23','4'),('24','4'),('25','3'),('26','2'),('27','3'),('28','5'),
('29','4'),('30','0'),('1','2'),('2','1'),('3','5'),('4','4'),('5','3'),('6','2'),('7','1'),('8','2'),('9','5'),
('10','4'),('11','1'),('12','5'),('13','2'),('14','1'),('15','5'),('16','5'),('17','4'),('18','1'),('19','1'),
('20','5'),('21','3'),('22','5'),('23','3'),('24','4'),('25','4'),('26','2'),('27','2'),('28','1'),('29','1'),
('30','0');

原尝试的SQL（存在逻辑问题） #

WITH tot_hours AS (
  SELECT t.ticketid, 
         t.parentid,
         SUM(hours) tot_hours
  FROM tickets t
  LEFT JOIN hours h ON t.ticketid = h.ticketid
  GROUP BY t.ticketid
  
  UNION ALL 
  
  SELECT t.parentid, 
         ti.parentid,
         SUM(tot_hours) tot_hours
  FROM tot_hours t
  LEFT JOIN tickets ti ON t.parentid = ti.ticketid
  JOIN hours h ON t.ticketid = h.ticketid
  GROUP BY t.parentid  
) 
SELECT tot.*, SUM(h.hours) 
FROM tot_hours tot
LEFT JOIN hours h ON tot.ticketid = h.ticketid 
GROUP BY tot.ticketid, tot.parentid, tot.tot_hours

正确解决方案：递归CTE实现 #

核心思路：

1. 先预计算每个工单的直接工时，处理无工时记录的工单（直接工时为0）
2. 用递归CTE从最底层工单向上聚合，累加所有子工单的总工时到父工单
3. 最终每个工单的总工时 = 自身直接工时 + 所有子工单的总工时之和

-- 第一步：预计算每个工单的直接工时
WITH direct_hours AS (
  SELECT 
    t.ticketid,
    t.parentid,
    COALESCE(SUM(h.hours), 0) AS direct_hours
  FROM tickets t
  LEFT JOIN hours h ON t.ticketid = h.ticketid
  GROUP BY t.ticketid, t.parentid
),
-- 第二步：递归CTE，从底层到顶层聚合总工时
recursive_totals AS (
  -- 锚点成员：所有无子女的工单（或最底层工单），总工时=直接工时
  SELECT 
    ticketid,
    parentid,
    direct_hours,
    direct_hours AS total_hours
  FROM direct_hours dh
  WHERE NOT EXISTS (SELECT 1 FROM tickets t WHERE t.parentid = dh.ticketid)
  
  UNION ALL
  
  -- 递归成员：向上聚合子工单的总工时到父工单
  SELECT 
    dh.ticketid,
    dh.parentid,
    dh.direct_hours,
    dh.direct_hours + SUM(rt.total_hours) AS total_hours
  FROM direct_hours dh
  JOIN recursive_totals rt ON dh.ticketid = rt.parentid
  GROUP BY dh.ticketid, dh.parentid, dh.direct_hours
)
-- 最终结果：包含所有工单的直接工时和总工时
SELECT 
  ticketid,
  direct_hours,
  total_hours
FROM recursive_totals
ORDER BY ticketid;

方案说明 #

• direct_hours CTE 先统计每个工单自身的直接工时，用COALESCE确保无工时记录的工单直接工时为0
• 递归CTE的锚点成员先取所有没有子工单的底层工单，它们的总工时等于直接工时
• 递归成员不断向上找到父工单，将所有子工单的总工时累加后加上自身直接工时，得到父工单的总工时
• 该方案自动适配任意嵌套深度，无需预设循环次数，适合用于视图

简化示例效果 #

• 工单X直接工时为8小时，其子工单Y的总工时为16小时，则工单X的总工时为8+16=24小时
• 顶层工单Z无直接工时，但所有工单都归属于它，则Z的总工时为所有工单的工时总和

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