求比现有双关键词产品查询更优的高效SQL语句

现有ProductValue表（字段：ID、ProductID、Value），需求为获取前100条同时存在包含"one"和"two"值记录的ProductID。目前已有以下两个查询实现，其中第二个效率相对更高，现提供几种更优的实现方案：

已有的两种查询实现 #

第一种（INTERSECT 实现） #

Select Top 100 ProductID From (
SELECT [ProductID]
  FROM [ProductValue]
  where [Value] like '%One%'
  intersect
SELECT [ProductID]
  FROM [ProductValue]
  where [Value] like '%Two%') g

第二种（IN子查询+分组 实现） #

Select Top 100 ProductID From [ProductValue] Where 
    ProductID in ( Select ProductID From [ProductValue] Where [Value] like '%One%' ) 
    and ProductID in ( Select ProductID From [ProductValue] Where [Value] like '%Two%' )
    group by ProductID 

更优的实现方案 #

方案一：分组统计+条件筛选（推荐） #

通过分组统计每个ProductID满足条件的记录数，直接筛选出同时包含两种值的ProductID，只需扫描一次表（或两次索引扫描，若有合适索引），效率更优：

SELECT TOP 100 ProductID
FROM ProductValue
WHERE Value LIKE '%One%' OR Value LIKE '%Two%'
GROUP BY ProductID
HAVING COUNT(DISTINCT CASE WHEN Value LIKE '%One%' THEN 'One' WHEN Value LIKE '%Two%' THEN 'Two' END) = 2

如果同一ProductID不会重复出现同一匹配值，可省去DISTINCT简化语句：

SELECT TOP 100 ProductID
FROM ProductValue
WHERE Value LIKE '%One%' OR Value LIKE '%Two%'
GROUP BY ProductID
HAVING SUM(CASE WHEN Value LIKE '%One%' THEN 1 ELSE 0 END) >= 1
   AND SUM(CASE WHEN Value LIKE '%Two%' THEN 1 ELSE 0 END) >= 1

方案二：EXISTS子查询优化 #

EXISTS的执行逻辑是找到匹配项就停止，相比IN子查询在大数据量下通常更高效，尤其是当ProductID有索引时：

SELECT TOP 100 DISTINCT pv.ProductID
FROM ProductValue pv
WHERE EXISTS (
    SELECT 1
    FROM ProductValue pv1
    WHERE pv1.ProductID = pv.ProductID
      AND pv1.Value LIKE '%One%'
)
AND EXISTS (
    SELECT 1
    FROM ProductValue pv2
    WHERE pv2.ProductID = pv.ProductID
      AND pv2.Value LIKE '%Two%'
)

额外优化建议 #

由于LIKE '%xxx%'无法利用普通前缀索引，若Value字段的模糊查询需求频繁，可考虑：

• 为Value字段创建全文索引，使用全文搜索语法（如CONTAINS(Value, 'One')、CONTAINS(Value, 'Two')），大幅提升查询效率；
• 若业务允许，调整模糊匹配规则为前缀匹配（如LIKE 'One%'），可利用普通非聚集索引。

内容的提问来源于stack exchange，提问作者msn.secret