传入分组ID数组查询关联不重复计分卡的单SQL优化方案

原有写法报错原因 #

你之前写的单SQL有两个核心语法问题：

• SQL执行顺序优先处理FROM/WHERE子句，再计算SELECT段的列值，同层WHERE条件无法直接引用SELECT里定义的"scIds"别名，且双引号包裹的标识符会被解析为表字段名，自然触发「列不存在」报错
• 写在SELECT段的子查询属于标量子查询，要求必须返回单行单列结果，你的子查询会返回多个符合条件的scorecardId，本身就不符合语法要求，完全没必要先聚合数组再拆分数组传参。

推荐实现（性能最优） #

优先用EXISTS写法，不需要额外做结果去重，只要中间表存在匹配的关联关系就返回对应记分卡数据，大数据量下性能比JOIN/IN写法更好：

SELECT sc."id", sc."name"
FROM "scorecard" sc
WHERE EXISTS (
    SELECT 1
    FROM scorecard_group sg
    WHERE sg."groupId" IN (${groupIds.concat()})
      AND sg."scorecardId" = sc."id"
)

等价JOIN写法 #

如果你更习惯关联查询写法，记得加DISTINCT避免同一个记分卡关联多个传入分组时返回重复数据：

SELECT DISTINCT sc."id", sc."name"
FROM "scorecard" sc
INNER JOIN scorecard_group sg
  ON sc."id" = sg."scorecardId"
WHERE sg."groupId" IN (${groupIds.concat()})

方案优势 #

• 省去应用层两次数据库交互的开销，也避免第一次查询返回的ID数组过长触发传参长度限制
• 数据库优化器可以自动利用scorecard_group表上groupId、scorecardId的关联索引，执行效率比两次拆分查询更高
• 逻辑和你原有两条SQL的执行结果完全一致：先筛选出指定分组下关联的所有去重记分卡ID，再返回对应记分卡的ID和名称字段

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