多匹配条件的MySQL JOIN查询：设备与组件属性表关联问题

别担心标题的事儿，这种涉及主表属性和关联子属性的查询需求确实不太好一下子精准归类，咱们先从梳理需求和写查询入手～

首先我先根据你的描述假设两张表的核心结构（你可以根据自己实际的字段名调整）：

-- 设备属性表
CREATE TABLE dev_prop (
    dev_id INT,          -- 设备ID
    prop_name VARCHAR(50), -- 属性名称（比如"color", "screen", "battery"）
    prop_value VARCHAR(100), -- 属性值（比如"red", "OLED", "4000mAh"）
    PRIMARY KEY (dev_id, prop_name)
);

-- 组件属性表（关联dev_prop中属于组件的属性）
CREATE TABLE comp_prop (
    comp_id INT AUTO_INCREMENT PRIMARY KEY,
    dev_id INT,          -- 关联的设备ID
    dev_prop_name VARCHAR(50), -- 关联的dev_prop中的属性名称（比如"screen"）
    comp_prop_name VARCHAR(50), -- 组件的子属性（比如"resolution", "size"）
    comp_prop_value VARCHAR(100) -- 组件子属性值（比如"1920x1080", "6.5inch"）
);

场景1：查询满足多条件的设备（包含设备自身属性+组件属性） #

比如要找颜色为红色，且屏幕分辨率为1920x1080的设备，我们可以用两种常用方式实现：

方法1：使用EXISTS子查询（高效去重）

SELECT DISTINCT dp.dev_id
FROM dev_prop dp
-- 匹配设备自身的颜色条件
WHERE dp.prop_name = 'color' AND dp.prop_value = 'red'
-- 匹配关联组件的分辨率条件
AND EXISTS (
    SELECT 1
    FROM comp_prop cp
    WHERE cp.dev_id = dp.dev_id
      AND cp.dev_prop_name = 'screen'
      AND cp.comp_prop_name = 'resolution'
      AND cp.comp_prop_value = '1920x1080'
);

方法2：多次JOIN关联（适合需要展示多属性值的场景）

如果需要同时输出设备的多个属性和组件属性，用多次JOIN更直观：

SELECT dp_dev.dev_id, dp_color.prop_value AS color, cp_res.comp_prop_value AS screen_resolution
FROM dev_prop dp_dev
-- 关联设备的颜色属性
JOIN dev_prop dp_color 
  ON dp_dev.dev_id = dp_color.dev_id 
  AND dp_color.prop_name = 'color' 
  AND dp_color.prop_value = 'red'
-- 关联屏幕组件的分辨率属性
JOIN comp_prop cp_res 
  ON dp_dev.dev_id = cp_res.dev_id 
  AND cp_res.dev_prop_name = 'screen' 
  AND cp_res.comp_prop_name = 'resolution' 
  AND cp_res.comp_prop_value = '1920x1080';

场景2：查询设备的所有属性（含关联组件属性） #

如果需要把设备自身的属性和组件的子属性统一展示出来，可以用UNION ALL合并结果：

SELECT dev_id, prop_name, prop_value, 'device' AS prop_type
FROM dev_prop
WHERE dev_id = 1 -- 指定要查询的设备ID
UNION ALL
SELECT dev_id, CONCAT(dev_prop_name, '_', comp_prop_name) AS prop_name, comp_prop_value, 'component' AS prop_type
FROM comp_prop
WHERE dev_id = 1;

关键注意事项 #

• 如果你的表是用外键（比如dev_prop的主键ID关联comp_prop），只需要把上面的dev_id + dev_prop_name关联逻辑换成对应的外键字段即可。
• 用DISTINCT或者EXISTS可以避免因多组件属性导致的重复设备记录。
• 如果需要模糊匹配，把=换成LIKE即可，比如prop_value LIKE '%red%'。

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