sql server统计时间重叠

以下是两种SQL Server统计时间重叠的方法：

1. 统计不重叠时间的方法

SELECT alarm_type_id, SUM(DATEDIFF(SECOND, start_time, stop_time)) time_sum
FROM (
    SELECT *
    FROM time_table a
    WHERE a.id NOT IN (
        SELECT DISTINCT b.id
        FROM time_table a, time_table b
        WHERE a.id <> b.id AND a.alarm_type_id = b.alarm_type_id AND a.start_time <= b.stop_time AND a.stop_time >= b.start_time
    )
) k2
GROUP BY alarm_type_id;

2. 统计重叠时间的方法

SELECT alarm_type_id, DATEDIFF(SECOND, MIN(k1.start_time), MAX(k1.stop_time))
FROM (
    SELECT DISTINCT b.*
    FROM time_table a, time_table b
    WHERE a.id <> b.id AND a.alarm_type_id = b.alarm_type_id AND a.start_time <= b.stop_time AND a.stop_time >= b.start_time
) k1
GROUP BY k1.alarm_type_id;