SQL如何查询同时拥有多个指定Attribute_ID的Warehouse_ID

原自连接语句错误原因 #

原有自连接语句的关联条件使用错误：

• 表中ID是行级唯一主键，用w1.ID = w2.ID关联时，本质是匹配同一行数据，单行不可能同时存在两个不同的Attribute_ID值，因此查不到结果
• 改用w1.ID != w2.ID关联时，没有限定记录同属一个仓库，会把不同仓库的属性记录交叉拼接，最终返回大量不符合预期的结果

推荐实现方案 #

方案1：分组聚合写法（性能最优，最简洁） #

仅需一次表扫描即可返回结果，是仅需查询Warehouse_ID场景下的首选方案：

SELECT Warehouse_ID
FROM WH_AT
WHERE Attribute_ID IN (101, 201)
GROUP BY Warehouse_ID
HAVING COUNT(DISTINCT Attribute_ID) = 2;

优化提示：如果业务规则限定同一个Warehouse_ID不会重复关联同一个Attribute_ID，可以去掉DISTINCT关键字，直接写COUNT(Attribute_ID) = 2，查询效率会进一步提升。

方案2：修正后的自连接写法 #

如果需要同时获取两个属性对应的其他关联字段，可以使用修正关联条件后的自连接语句：

SELECT w1.Warehouse_ID
FROM WH_AT w1
INNER JOIN WH_AT w2
  ON w1.Warehouse_ID = w2.Warehouse_ID
WHERE w1.Attribute_ID = 101
  AND w2.Attribute_ID = 201;

注意：如果存在同一仓库重复绑定同一属性的情况，可以在SELECT后加DISTINCT去重，避免返回重复的仓库ID。

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