如何实现SQL多特征各自条件同时满足的精准过滤？

你遇到的问题核心在于：单条记录只能对应一个特征，直接用AND连接不同特征的条件会导致无符合结果（一条记录不可能同时属于特征4和特征5），而OR又只能满足单个特征要求。下面提供两种可行的实现方式：

方法一：分组统计法 #

先筛选出符合任一特征条件的记录，再按产品分组，确保该产品同时拥有三个符合条件的特征。

SELECT
    p.id_product
FROM
    ps_feature_product                   fp
    INNER JOIN ps_product                p   ON p.id_product = fp.id_product
    INNER JOIN ps_feature_value          fv  ON fp.id_feature_value = fv.id_feature_value
    INNER JOIN ps_feature_value_position fvp ON fv.id_feature_value = fvp.id_feature_value
    INNER JOIN ps_feature_value_lang     fvl ON fv.id_feature_value = fvl.id_feature_value
WHERE
    fvl.id_lang = 1
    AND (
        (fp.id_feature = 4 AND fvp.position >= 100)
        OR (fp.id_feature = 5 AND fvp.position >= 20)
        OR (fp.id_feature = 7 AND fvp.position >= 20)
    )
GROUP BY
    p.id_product
HAVING
    COUNT(DISTINCT fp.id_feature) = 3;

逻辑说明 #

1. WHERE子句先筛选出所有满足单个特征条件的记录；
2. 按id_product分组后，用COUNT(DISTINCT fp.id_feature)统计该产品拥有的符合条件的特征数量；
3. HAVING子句要求数量等于3，确保三个特征的条件都被满足。

如果需要获取每个特征的详细信息，可以用这个结果集再关联原数据表查询。

方法二：多表内连接法 #

通过多次内连接，分别匹配每个特征的条件，只有同时满足所有连接条件的产品才会被返回。

SELECT
    p.id_product,
    fvl4.value AS feature_4_value,
    fvp4.position AS feature_4_position,
    fvl5.value AS feature_5_value,
    fvp5.position AS feature_5_position,
    fvl7.value AS feature_7_value,
    fvp7.position AS feature_7_position
FROM
    ps_product p
    -- 匹配特征4的条件
    INNER JOIN ps_feature_product fp4 
        ON p.id_product = fp4.id_product AND fp4.id_feature = 4
    INNER JOIN ps_feature_value fv4 
        ON fp4.id_feature_value = fv4.id_feature_value
    INNER JOIN ps_feature_value_position fvp4 
        ON fv4.id_feature_value = fvp4.id_feature_value AND fvp4.position >= 100
    INNER JOIN ps_feature_value_lang fvl4 
        ON fv4.id_feature_value = fvl4.id_feature_value AND fvl4.id_lang = 1
    -- 匹配特征5的条件
    INNER JOIN ps_feature_product fp5 
        ON p.id_product = fp5.id_product AND fp5.id_feature = 5
    INNER JOIN ps_feature_value fv5 
        ON fp5.id_feature_value = fv5.id_feature_value
    INNER JOIN ps_feature_value_position fvp5 
        ON fv5.id_feature_value = fvp5.id_feature_value AND fvp5.position >= 20
    INNER JOIN ps_feature_value_lang fvl5 
        ON fv5.id_feature_value = fvl5.id_feature_value AND fvl5.id_lang = 1
    -- 匹配特征7的条件
    INNER JOIN ps_feature_product fp7 
        ON p.id_product = fp7.id_product AND fp7.id_feature = 7
    INNER JOIN ps_feature_value fv7 
        ON fp7.id_feature_value = fv7.id_feature_value
    INNER JOIN ps_feature_value_position fvp7 
        ON fv7.id_feature_value = fvp7.id_feature_value AND fvp7.position >= 20
    INNER JOIN ps_feature_value_lang fvl7 
        ON fv7.id_feature_value = fvl7.id_feature_value AND fvl7.id_lang = 1;

逻辑说明 #

每个特征对应一组内连接，并且在连接条件中直接加入该特征的position要求。只有当产品同时存在三个特征的符合条件记录时，才能通过所有内连接，最终被筛选出来。这种方式可以直接获取每个特征的详细值和位置信息。

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