原始数据 SELECT d.waybill_no, @group_row:=CASE WHEN @parent_code=picked_by THEN @group_row+1 ELSE 1 END AS id, @parent_code1:=picked_by AS picked_by1, MIN(spd.picked_date) start_time, MAX(spd.picked_date) end_time, TIMESTAMPDIFF(HOUR,MIN(spd.picked_date),MAX(spd.picked_date)) times, spd.picked_by FROM `shipping_order` d LEFT JOIN shipping_pick_detail spd ON d.`SHIPPING_ORDER_ID`=spd.SHIPPING_ORDER_ID GROUP BY spd.picked_by,d.waybill_no 如图: 添加分组行号: SELECT start_time, end_time, picked_by, @group_row:=CASE WHEN @parent_code=picked_by THEN @group_row+1 ELSE 1 END AS id, @parent_code:=picked_by AS parent_code FROM ( SELECT d.waybill_no, @group_row:=CASE WHEN @parent_code=picked_by THEN @group_row+1 ELSE 1 END AS id, @parent_code1:=picked_by AS picked_by1, MIN(spd.picked_date) start_time, MAX(spd.picked_date) end_time, TIMESTAMPDIFF(HOUR,MIN(spd.picked_date),MAX(spd.picked_date)) times, spd.picked_by FROM `shipping_order` d LEFT JOIN shipping_pick_detail spd ON d.`SHIPPING_ORDER_ID`=spd.SHIPPING_ORDER_ID GROUP BY spd.picked_by,d.waybill_no) iop LEFT JOIN ( SELECT @group_row:=1, @parent_code:='',@parent_code1:='' ) b ON 1=1 ORDER BY picked_by 分组汇总效果 SELECT picked_by, SUM(t.duration) - SUM(t.overlap) AS filtered_duration FROM ( SELECT t1.picked_by, t1.start_time, t1.end_time, TIMESTAMPDIFF(HOUR,t1.start_time,t1.end_time) AS duration, SUM( IF(t2.start_time < t1.start_time AND t2.end_time > t1.end_time , TIMESTAMPDIFF(HOUR,t1.start_time,t1.end_time), 0) -- t2 completely around t1 + IF(t2.start_time >= t1.start_time AND t2.end_time <= t1.end_time , TIMESTAMPDIFF(HOUR,t2.start_time,t2.end_time), 0) -- t2 completely within t1 + IF(t2.start_time < t1.start_time AND t2.end_time > t1.start_time AND t2.end_time < t1.end_time , TIMESTAMPDIFF(HOUR,t1.start_time,t2.end_time), 0) -- t2 starts before t1 starts and overlaps partially + IF(t2.start_time < t1.end_time AND t2.end_time > t1.end_time AND t2.start_time > t1.start_time, TIMESTAMPDIFF(HOUR,t2.start_time,t1.end_time), 0) -- t2 starts before t1 ends and overlaps partially ) AS overlap FROM ( SELECT start_time, end_time, picked_by, @group_row:=CASE WHEN @parent_code=picked_by THEN @group_row+1 ELSE 1 END AS id, @parent_code:=picked_by AS parent_code FROM ( SELECT d.waybill_no, @group_row:=CASE WHEN @parent_code=picked_by THEN @group_row+1 ELSE 1 END AS id, @parent_code1:=picked_by AS picked_by1, MIN(spd.picked_date) start_time, MAX(spd.picked_date) end_time, TIMESTAMPDIFF(HOUR,MIN(spd.picked_date),MAX(spd.picked_date)) times, spd.picked_by FROM `shipping_order` d LEFT JOIN shipping_pick_detail spd ON d.`SHIPPING_ORDER_ID`=spd.SHIPPING_ORDER_ID GROUP BY spd.picked_by,d.waybill_no) iop LEFT JOIN ( SELECT @group_row:=1, @parent_code:='',@parent_code1:='' ) b ON 1=1 ORDER BY picked_by ) t1 LEFT JOIN ( SELECT start_time, end_time, picked_by, @group_row:=CASE WHEN @parent_code=picked_by THEN @group_row+1 ELSE 1 END AS id, @parent_code:=picked_by AS parent_code FROM ( SELECT d.waybill_no, @group_row:=CASE WHEN @parent_code=picked_by THEN @group_row+1 ELSE 1 END AS id, @parent_code1:=picked_by AS picked_by1, MIN(spd.picked_date) start_time, MAX(spd.picked_date) end_time, TIMESTAMPDIFF(HOUR,MIN(spd.picked_date),MAX(spd.picked_date)) times, spd.picked_by FROM `shipping_order` d LEFT JOIN shipping_pick_detail spd ON d.`SHIPPING_ORDER_ID`=spd.SHIPPING_ORDER_ID GROUP BY spd.picked_by,d.waybill_no) iop LEFT JOIN ( SELECT @group_row:=1, @parent_code:='',@parent_code1:='' ) b ON 1=1 ORDER BY picked_by ) t2 ON t1.picked_by=t2.picked_by AND t2.id > t1.id AND ( (t2.start_time < t1.start_time AND t2.end_time > t1.end_time ) OR (t2.start_time >= t1.start_time AND t2.end_time <= t1.end_time ) OR (t2.start_time < t1.start_time AND t2.end_time > t1.start_time) OR (t2.start_time < t1.end_time AND t2.end_time > t1.end_time ) ) GROUP BY t1.start_time, t1.end_time,t1.picked_by ) AS t GROUP BY picked_by 注意,时间差取的是小时。