SQL Server中拆分Downtime表时间记录为状态事件的高效查询

你的需求是把Downtime表的每条记录拆成两条：一条用StartTime作为t_stamp、CurrentlyDown设为1；另一条用EndTime作为t_stamp、CurrentlyDown设为0。针对你考虑的两种思路，直接说结论：用UNION ALL合并两个结果集是性能最优的方案，比自连接和CTE拆分更高效。

为什么排除你提到的两种思路？ #

• ID自连接+CASE：这种方式本质是做笛卡尔积（每条记录和自己关联），然后用CASE过滤出需要的时间和状态，会产生大量冗余中间数据，数据量越大，性能损耗越明显，完全没必要。
• CTE拆分再关联：CTE的写法只是把两个查询逻辑封装起来，执行计划和UNION ALL基本一致，但写法更繁琐，没有性能优势，反而增加不必要的代码复杂度。

最优实现代码 #

SELECT 
    ID,
    StartTime AS t_stamp,
    1 AS CurrentlyDown
FROM Downtime
UNION ALL
SELECT 
    ID,
    EndTime AS t_stamp,
    0 AS CurrentlyDown
FROM Downtime

性能优势说明 #

• 仅需扫描Downtime表两次（SQL Server优化器甚至可能合并扫描，进一步减少IO），没有额外的连接、过滤或排序开销。
• 直接生成目标数据，中间过程无冗余，最终结果集大小是原表的2倍，完全符合需求。
• 执行计划简单直观，适合大数据量场景，即使表中有百万级记录也能高效运行。

额外性能优化建议 #

如果Downtime表数据量很大，可以给StartTime和EndTime单独建索引，或者创建ID + StartTime、ID + EndTime的复合索引，能让扫描过程更快。

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