如何编写MySQL查询获取满足strategy_id=1全匹配条件的device_id？

你的需求是要筛选出所有部件都符合策略要求的设备，而不是只要有一个部件符合就行。原SQL的问题在于仅匹配了单个符合条件的部件，没有校验设备的全部部件是否都满足要求。

方案一：分组校验法 #

通过分组统计设备的部件情况，确保两个核心条件都满足：

SELECT dp.device_id
FROM device_part dp
-- 关联策略表，筛选出strategy_id=1且版本匹配的部件记录
JOIN strategy_part sp 
  ON dp.part_id = sp.part_id 
  AND sp.strategy_id = 1
  AND dp.software_version = sp.source_version
-- 关联策略包含的所有部件ID，用于检查设备是否有超出范围的部件
LEFT JOIN (
    SELECT DISTINCT part_id 
    FROM strategy_part 
    WHERE strategy_id = 1
) sp_valid_parts 
  ON dp.part_id = sp_valid_parts.part_id
GROUP BY dp.device_id
-- 条件1：设备没有不属于策略范围的部件
HAVING COUNT(CASE WHEN sp_valid_parts.part_id IS NULL THEN 1 END) = 0
-- 条件2：设备所有部件都满足版本匹配要求（匹配上的部件数等于设备总部件数）
AND COUNT(DISTINCT dp.part_id) = COUNT(DISTINCT sp.part_id);

方案二：排除法 #

先找出所有不符合条件的设备，再从总列表中排除它们：

SELECT DISTINCT device_id
FROM device_part
WHERE device_id NOT IN (
    -- 排除包含策略外部件的设备
    SELECT device_id
    FROM device_part
    WHERE part_id NOT IN (
        SELECT DISTINCT part_id 
        FROM strategy_part 
        WHERE strategy_id = 1
    )
)
AND device_id NOT IN (
    -- 排除存在版本不匹配部件的设备
    SELECT dp.device_id
    FROM device_part dp
    LEFT JOIN strategy_part sp 
        ON dp.part_id = sp.part_id 
        AND sp.strategy_id = 1
        AND dp.software_version = sp.source_version
    WHERE sp.strategy_id IS NULL
);

结果验证 #

针对你提供的测试数据：

• jdvjdksfks的两个部件都在策略范围内，且版本均匹配，会被选中；
• peirizkdsf的part2版本v3不在策略part2的source_version集合中，会被排除。

最终查询结果仅返回jdvjdksfks，符合需求。

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