请求协助设计带层级关系的SQL表及查询实现

先梳理下你的核心需求：要管理设备（及配套更新）的生命周期，查询指定月份时，返回当前生效的主设备及其关联的更新记录，且后续启用的新设备会完全替代旧设备（旧设备不再出现在结果中）。

一、表结构优化设计 #

为了让设备和更新的关联关系更清晰（避免依赖字符串名称匹配的脆弱逻辑），我建议在原表基础上增加一个parent_id字段，用来明确标记更新记录所属的主设备。

表结构定义（以MySQL为例） #

CREATE TABLE device_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    object_name VARCHAR(50) NOT NULL,
    start_month INT NOT NULL,
    end_month INT NOT NULL,
    parent_id INT NULL, -- 主设备此字段为NULL，更新记录此字段指向对应主设备的ID
    FOREIGN KEY (parent_id) REFERENCES device_records(id)
);

插入你的示例数据 #

INSERT INTO device_records (object_name, start_month, end_month, parent_id)
VALUES
('Phone 1', 1, 3, NULL),
('Phone 1 OS Update', 2, 3, 1), -- 关联主设备Phone 1（ID=1）
('Phone 2', 4, 5, NULL),
('Phone 3', 6, 7, NULL);

如果不想新增字段，也可以通过object_name的前缀匹配关联，但这种方式依赖命名规则，容易出错，优先推荐带parent_id的方案。

二、查询语句实现 #

核心逻辑分两步：

1. 找到指定月份下未被后续主设备替代的生效主设备
2. 关联查询该主设备及其所有在指定月份生效的更新记录

针对固定月份的查询（以Month=2为例） #

WITH active_main_devices AS (
    -- 第一步：筛选出指定月份生效且未被替代的主设备
    SELECT d1.id
    FROM device_records d1
    WHERE d1.parent_id IS NULL -- 仅筛选主设备
      AND d1.start_month <= 2 -- 查询月份在主设备生效区间内
      AND d1.end_month >= 2
      AND NOT EXISTS (
          -- 确保没有后续启用的主设备在当前查询月份生效
          SELECT 1
          FROM device_records d2
          WHERE d2.parent_id IS NULL
            AND d2.start_month <= 2
            AND d2.start_month > d1.end_month
      )
)
-- 第二步：返回主设备及其关联的生效更新记录
SELECT dr.id, dr.object_name
FROM device_records dr
WHERE (dr.parent_id IS NULL AND dr.id IN (SELECT id FROM active_main_devices))
   OR (dr.parent_id IN (SELECT id FROM active_main_devices) 
       AND dr.start_month <= 2 
       AND dr.end_month >= 2);

动态参数版本（支持任意月份查询） #

如果用参数化查询（比如Java PreparedStatement、Python SQLAlchemy），可以把月份替换为参数，适配所有场景：

WITH active_main_devices AS (
    SELECT d1.id
    FROM device_records d1
    WHERE d1.parent_id IS NULL
      AND d1.start_month <= ?
      AND d1.end_month >= ?
      AND NOT EXISTS (
          SELECT 1
          FROM device_records d2
          WHERE d2.parent_id IS NULL
            AND d2.start_month <= ?
            AND d2.start_month > d1.end_month
      )
)
SELECT dr.id, dr.object_name
FROM device_records dr
WHERE (dr.parent_id IS NULL AND dr.id IN (SELECT id FROM active_main_devices))
   OR (dr.parent_id IN (SELECT id FROM active_main_devices) 
       AND dr.start_month <= ? 
       AND dr.end_month >= ?);

三、结果验证 #

• 查询Month=2：返回ID=1（Phone 1）、ID=2（Phone 1 OS Update）
• 查询Month=4：返回ID=3（Phone 2），此时Phone 1已到期，被Phone 2完全替代
• 查询Month=6：返回ID=4（Phone 3），同理替代Phone 2

补充：无parent_id的兼容方案 #

如果必须使用原表结构，可以通过object_name的前缀匹配关联，但需保证命名规则稳定：

WITH active_main_devices AS (
    SELECT d1.id, d1.object_name
    FROM device_records d1
    WHERE d1.object_name NOT LIKE '%OS Update' -- 标记主设备
      AND d1.start_month <= 2
      AND d1.end_month >= 2
      AND NOT EXISTS (
          SELECT 1
          FROM device_records d2
          WHERE d2.object_name NOT LIKE '%OS Update'
            AND d2.start_month <= 2
            AND d2.start_month > d1.end_month
      )
)
SELECT dr.id, dr.object_name
FROM device_records dr
WHERE (dr.object_name NOT LIKE '%OS Update' AND dr.id IN (SELECT id FROM active_main_devices))
   OR (dr.object_name LIKE CONCAT((SELECT object_name FROM active_main_devices), '%')
       AND dr.start_month <= 2 
       AND dr.end_month >= 2);

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