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

Question

所以第一个 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 

为什么第一个返回零行？

Answer 1

在第一个查询中，您要查找 bm.option_type = 'area_id' 的任何记录
并且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