我有一个表PropertyFeatures，该表具有三列。第一个PropertyFeatureId是自动递增的列，第二个Propertyid是Property表的外键，最后一个Featureid是Feature表的外键。
+------------------------------------------+
| PropertyFeatureId PropertyId FeatureId |
+------------------------------------------+
| 1 1 1 |
| 2 1 2 |
| 3 2 2 |
| 4 2 3 |
| 5 2 4 |
+------------------------------------------+
SELECT propertyFeatures.PropertyId
FROM PropertyFeatures propertyFeatures INNER JOIN Feature feature ON feature.id = propertyFeatures.FeatureId
WHERE propertyFeatures.[FeatureId] IN (1,2)
GROUP BY propertyFeatures.PropertyId
上面的查询给出以下结果：
PropertyId
1
2
但我想得到以下结果：
PropertyId
1
因为Featureid1和2仅适用于PropertyId1。
如果我将查询更改为以下内容：
SELECT propertyFeatures.PropertyId
FROM PropertyFeatures propertyFeatures INNER JOIN Feature feature ON feature.id = propertyFeatures.FeatureId
WHERE propertyFeatures .[FeatureId] IN (2,3,4)
GROUP BY propertyFeatures.PropertyId
它只会显示：
PropertyId
2
因为Featureid2、3和4仅适用于PropertyId2。
如何获得理想的结果？
请记住，这WHERE a IN (1, 2)实际上是的快捷方式WHERE a = 1 OR a = 2。这不会给您两个集合的交集，只有第一个过滤器有效的集合与第二个过滤器有效的集合的交集。在您的情况下，EXISTS您要搜索的是-或INTERSECT。
范例INTERSECT：
SELECT propertyFeatures.PropertyId
FROM PropertyFeatures AS propertyFeatures
INNER JOIN Feature AS feature ON feature.id = propertyFeatures.FeatureId
WHERE propertyFeatures.[FeatureId] = 1
GROUP BY propertyFeatures.PropertyId
INTERSECT
SELECT propertyFeatures.PropertyId
FROM PropertyFeatures AS propertyFeatures
INNER JOIN Feature AS feature ON feature.id = propertyFeatures.FeatureId
WHERE propertyFeatures.[FeatureId] = 2
GROUP BY propertyFeatures.PropertyId;
范例EXISTS：
SELECT propertyFeatures.PropertyId
FROM PropertyFeatures AS propertyFeatures
INNER JOIN Feature AS feature ON feature.id = propertyFeatures.FeatureId
WHERE EXISTS (SELECT TOP 1 * FROM PropertyFeatures AS P
INNER JOIN Feature AS F ON F.id = P.FeatureId
WHERE P.FeatureId = 1)
AND
EXISTS (SELECT TOP 1 * FROM PropertyFeatures AS P
INNER JOIN Feature AS F ON F.id = P.FeatureId
WHERE P.FeatureId = 2);
（代码未经测试，因此请自行检查是否有效！）
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句