在没有自联接表的情况下将单个列与多个值匹配

好像

SELECT routing_id
FROM ec.production_autoroutingtag
WHERE (tag_type, tag_value) IN (('mounting_type', 'smd'),
                                ('x_ray', 'true'),
                                ('depaneling', 'false'))
GROUP BY routing_id
HAVING COUNT(DISTINCT tag_type, tag_value) = 3

• 更新

如果您需要使用非等值条件来检查值，那么必须结合使用OR将条件分别检查每对（标记，值）：

SELECT routing_id,
       COUNT( tag_type) 
FROM ec.production_autoroutingtag 
WHERE (tag_type, tag_value) IN (('mounting_type','qfn'), ('panel_qty','1')) 
   OR (tag_type = 'bom' and tag_value >= '10')
   OR (tag_type = 'cpl' and tag_value >= '158')
GROUP BY routing_id 
HAVING COUNT(tag_type) = 4; 

注意-比较“大于或等于”将按STRINGS进行（即“ 5”将为TRUE）！如果需要比较为数字，则必须对字段值使用正确的类型转换，对引用值使用正确的数据类型：

SELECT routing_id,
       COUNT( tag_type) 
FROM ec.production_autoroutingtag 
WHERE (tag_type, tag_value) IN (('mounting_type','qfn'), ('panel_qty','1')) 
   OR (tag_type = 'bom' and tag_value + 0 >= 10)
   OR (tag_type = 'cpl' and tag_value + 0 >= 158)
GROUP BY routing_id 
HAVING COUNT(tag_type) = 4; 

回答来源：stackoverflow