如何批量填充DeviceDeviceStatuses映射表以记录设备全状态历史

现有两张表：Device和DeviceStatus，其中DeviceStatus的数据如下：

此前Device表仅关联单个DeviceStatus，且状态严格按 1→2→5→3→4 的顺序流转。现在需要让设备关联所有已历状态（含当前状态），因此创建了映射表DeviceDeviceStatuses。例如，若ID为42的设备当前状态为3，则DeviceDeviceStatuses需填充的数据如下：

请问如何编写SQL实现该需求？

核心思路 #

先明确每个状态的流转层级，再根据设备当前状态，关联出所有层级小于等于当前状态的历史状态，最后批量插入到映射表中。

完整SQL语句 #

-- 定义状态流转的层级顺序，严格遵循1→2→5→3→4的规则
WITH StatusOrder AS (
    SELECT Id AS StatusId, 1 AS OrderLevel FROM DeviceStatus WHERE Id = 1
    UNION ALL
    SELECT Id AS StatusId, 2 AS OrderLevel FROM DeviceStatus WHERE Id = 2
    UNION ALL
    SELECT Id AS StatusId, 3 AS OrderLevel FROM DeviceStatus WHERE Id = 5
    UNION ALL
    SELECT Id AS StatusId, 4 AS OrderLevel FROM DeviceStatus WHERE Id = 3
    UNION ALL
    SELECT Id AS StatusId, 5 AS OrderLevel FROM DeviceStatus WHERE Id = 4
)
-- 插入所有设备的已历状态到映射表
INSERT INTO DeviceDeviceStatuses (DeviceId, DeviceStatusId)
SELECT 
    d.Id AS DeviceId,
    so.StatusId AS DeviceStatusId
FROM Device d
-- 关联设备当前状态对应的层级
JOIN StatusOrder current_so ON d.CurrentStatusId = current_so.StatusId
-- 筛选出所有层级≤当前状态的历史状态
JOIN StatusOrder so ON so.OrderLevel <= current_so.OrderLevel
-- 按设备ID和状态顺序排序，保证数据有序
ORDER BY d.Id, so.OrderLevel;

说明 #

1. CTE StatusOrder：手动定义每个状态的流转层级，确保符合业务要求的1→2→5→3→4顺序，层级数值越小代表状态越靠前。
2. 关联逻辑：通过两次关联StatusOrder，先获取设备当前状态的层级，再筛选出所有层级不大于当前层级的状态，即为该设备的所有已历状态。
3. 字段适配：如果Device表中存储当前状态的字段不是CurrentStatusId，请替换为实际字段名。

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