SQL Server查询求助：按Model筛选Reference在'400a'-'850a'的数据

首先咱们先揪出错误根源：Subquery returns multiple rows这个报错，是因为你写的两个子查询SELECT IndexOp FROM table where Model = '405a' and Reference = '850a'返回了不止一条记录——SQL没法拿单个字段值和一组值做>=或<=比较，自然就卡壳了。

接下来分两种场景帮你解决，对应不同的真实需求：

场景1：直接筛选每个Model下Reference在'400a'到'850a'的记录 #

如果你的需求只是获取所有Model中，Reference值落在'400a'到'850a'之间的原始数据，那完全不需要子查询，直接用BETWEEN筛选就够了：

SELECT Model, IndexOp, Reference, value 
FROM table
WHERE Reference BETWEEN '400a' AND '850a'

要是还需要按Model做分组统计（比如每个符合条件的Model的记录数、value平均值），加上GROUP BY就行：

SELECT 
  Model,
  COUNT(*) AS TotalRecords,
  AVG(value) AS AverageValue
FROM table
WHERE Reference BETWEEN '400a' AND '850a'
GROUP BY Model

场景2：通过IndexOp间接筛选（按Model匹配对应Reference的Index范围） #

如果你的需求是每个Model下，筛选出IndexOp介于该Model的'400a'对应IndexOp和'850a'对应IndexOp之间的记录（比如Reference是字符串，排序逻辑和IndexOp的顺序绑定），那得先解决子查询返回多行的问题：

方法1：用聚合函数确保子查询只返回一行 #

如果同一个(Model, Reference)组合有多条记录，你需要指定取哪个IndexOp（比如最大或最小，根据业务逻辑选）：

SELECT t.Model, t.IndexOp, t.Reference, t.value 
FROM table t
WHERE 
  -- 取当前Model下Reference='400a'的最大IndexOp作为下限
  t.IndexOp >= (SELECT MAX(IndexOp) FROM table WHERE Model = t.Model AND Reference = '400a')
  -- 取当前Model下Reference='850a'的最大IndexOp作为上限
  AND t.IndexOp <= (SELECT MAX(IndexOp) FROM table WHERE Model = t.Model AND Reference = '850a')

要是需要最小的IndexOp，把MAX换成MIN就行。

方法2：用CTE+JOIN更高效地获取范围 #

如果数据量较大，多次子查询会拖慢性能，推荐用CTE先预计算每个Model的Index范围，再关联原表：

WITH ModelIndexRanges AS (
  SELECT 
    Model,
    -- 提取每个Model下Reference='400a'的IndexOp（用MAX确保唯一值）
    MAX(CASE WHEN Reference = '400a' THEN IndexOp END) AS MinIndexOp,
    -- 提取每个Model下Reference='850a'的IndexOp
    MAX(CASE WHEN Reference = '850a' THEN IndexOp END) AS MaxIndexOp
  FROM table
  WHERE Reference IN ('400a', '850a')
  GROUP BY Model
)
SELECT t.Model, t.IndexOp, t.Reference, t.value
FROM table t
-- 关联到每个Model的Index范围
JOIN ModelIndexRanges r ON t.Model = r.Model
-- 筛选IndexOp在范围内的记录
WHERE t.IndexOp BETWEEN r.MinIndexOp AND r.MaxIndexOp

如果有些Model没有'400a'或'850a'的记录，上面的查询会自动排除这些Model；要是需要保留它们，可以把JOIN换成LEFT JOIN，再根据需求处理NULL的情况（比如加OR (r.MinIndexOp IS NULL OR r.MaxIndexOp IS NULL)）。

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