SQL根据双行值条件筛选products表数据的查询实现

核心逻辑是先拿到每个product_id对应的load类型记录的value值，再按规则过滤load和deliverable两类记录，其余类型的记录全部保留。

支持CTE的数据库（MySQL 8.0+、PostgreSQL、SQL Server等） #

WITH load_config AS (
    SELECT product_id, value AS load_val
    FROM products
    WHERE detail = 'load'
)
SELECT p.id, p.product_id, p.detail, p.value
FROM products p
INNER JOIN load_config lc ON p.product_id = lc.product_id
WHERE
    p.detail NOT IN ('load', 'deliverable')
    OR (lc.load_val = 'yes' AND p.detail = 'load')
    OR (lc.load_val = 'no' AND p.detail = 'deliverable')
ORDER BY p.product_id, p.id;

兼容老版本MySQL 5.x #

不支持CTE的场景可以用关联子查询实现：

SELECT id, product_id, detail, value
FROM products p
WHERE
    detail NOT IN ('load', 'deliverable')
    OR (
        detail = 'load'
        AND (SELECT value FROM products WHERE product_id = p.product_id AND detail = 'load') = 'yes'
    )
    OR (
        detail = 'deliverable'
        AND (SELECT value FROM products WHERE product_id = p.product_id AND detail = 'load') = 'no'
    )
ORDER BY product_id, id;

执行结果 #

运行以上任意一段SQL，都会返回你期望的结果集：

id | product_id | detail       | value
----+------------+--------------+--------
 1  | 1          | size         | medium
 2  | 1          | load         | yes
 5  | 1          | color        | green
 3  | 1          | availability | 10
 5  | 2          | size         | small
 7  | 2          | color        | bleu
 8  | 2          | availability | 5
 9  | 2          | deliverable  | yes

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