mysql - 当同时包含两个字段时，为什么此SQL不起作用

因此，此第一个SQL返回零行：

SELECT b.b_id
FROM `buy` b
    INNER JOIN `buy_multiples` AS bm
        ON b.b_id = bm.b_id
        AND b.buy_status IN (2)
WHERE bm.`option_type` = 'area_id'
AND bm.selected_option_key IN (10)
AND bm.`option_type` = 'bzips'
AND bm.selected_option_key IN (37027)
GROUP BY b.b_id

第二个SQL返回b_id 5024和4987：

SELECT b.b_id
FROM `buy` b
    INNER JOIN `buy_multiples` AS bm
        ON b.b_id = bm.b_id
        AND b.buy_status IN (2)
WHERE bm.`option_type` = 'bzips'
AND bm.selected_option_key IN (37027)
GROUP BY b.b_id

第三个SQL返回b_id 5024和4987和4989：

SELECT b.b_id
FROM `buy` b
    INNER JOIN `buy_multiples` AS bm
        ON b.b_id = bm.b_id
        AND b.buy_status IN (2)
WHERE bm.`option_type` = 'area_id'
AND bm.selected_option_key IN (10)
GROUP BY b.b_id

为什么第一个返回零行？

最佳答案

在第一个查询中，您要查找bm.option_type = 'area_id'
AND bm.option_type = 'bzips'。这将始终返回0个结果。您要使用：

SELECT b.b_id
FROM `buy` b
    INNER JOIN `buy_multiples` AS bm
        ON b.b_id = bm.b_id
        AND b.buy_status IN (2)
WHERE bm.`option_type` = 'area_id'
AND bm.selected_option_key IN (10)
OR bm.`option_type` = 'bzips'
AND bm.selected_option_key IN (37027)
GROUP BY b.b_id