如何修改子查询，使其能通过WHERE IN获取多行数据？

你的原查询里每个子查询都用了TOP 1，所以只能返回单个obj值，没办法拿到所有符合条件的行来配合WHERE IN使用。下面根据不同的使用场景给你对应的修改方案：

场景1：让查询返回的列包含所有符合条件的obj（拼接为字符串） #

如果是想让responsible_inverted这类列返回所有对应RACI角色的obj（比如用逗号分隔），之后可以拆分这个字符串用于WHERE IN，可以用字符串聚合函数来替代TOP 1。不同数据库的函数略有区别：

SQL Server 版本 #

SELECT 
  (SELECT STRING_AGG(obj, ',') FROM rel_raci responsible_raci_r WHERE responsible_raci_r.PERSON_ID = organization.id AND responsible_raci_r.RACI ='R') AS responsible_inverted,
  (SELECT STRING_AGG(obj, ',') FROM rel_raci responsible_raci_r WHERE responsible_raci_r.PERSON_ID = organization.id AND responsible_raci_r.RACI ='A') AS accountable_inverted,
  (SELECT STRING_AGG(obj, ',') FROM rel_raci responsible_raci_r WHERE responsible_raci_r.PERSON_ID = organization.id AND responsible_raci_r.RACI ='C') AS consulted_inverted,
  (SELECT STRING_AGG(obj, ',') FROM rel_raci responsible_raci_r WHERE responsible_raci_r.PERSON_ID = organization.id AND responsible_raci_r.RACI ='I') AS informed_inverted
FROM obj_resource organization 
WHERE CONTAINS('2cef8e3d:15992b7f51e:33f', organization.id, -1) 
  AND getOrgtype(organization.id) != 1

之后如果要把拼接后的字符串用于WHERE IN，可以用STRING_SPLIT函数拆分，比如：

SELECT * 
FROM some_table 
WHERE id IN (SELECT value FROM STRING_SPLIT('你的拼接字符串', ','))

MySQL 版本 #

把STRING_AGG换成GROUP_CONCAT即可：

SELECT 
  (SELECT GROUP_CONCAT(obj SEPARATOR ',') FROM rel_raci responsible_raci_r WHERE responsible_raci_r.PERSON_ID = organization.id AND responsible_raci_r.RACI ='R') AS responsible_inverted,
  (SELECT GROUP_CONCAT(obj SEPARATOR ',') FROM rel_raci responsible_raci_r WHERE responsible_raci_r.PERSON_ID = organization.id AND responsible_raci_r.RACI ='A') AS accountable_inverted,
  (SELECT GROUP_CONCAT(obj SEPARATOR ',') FROM rel_raci responsible_raci_r WHERE responsible_raci_r.PERSON_ID = organization.id AND responsible_raci_r.RACI ='C') AS consulted_inverted,
  (SELECT GROUP_CONCAT(obj SEPARATOR ',') FROM rel_raci responsible_raci_r WHERE responsible_raci_r.PERSON_ID = organization.id AND responsible_raci_r.RACI ='I') AS informed_inverted
FROM obj_resource organization 
WHERE CONTAINS('2cef8e3d:15992b7f51e:33f', organization.id, -1) 
  AND getOrgtype(organization.id) != 1

场景2：直接获取所有符合条件的obj作为WHERE IN的数据源 #

如果你的需求是直接拿到所有对应RACI角色的obj值，用于其他查询的WHERE IN条件，可以直接重构查询，避免子查询嵌套：

-- 获取所有R角色的obj
SELECT obj 
FROM rel_raci 
WHERE PERSON_ID IN (
  SELECT id 
  FROM obj_resource 
  WHERE CONTAINS('2cef8e3d:15992b7f51e:33f', id, -1) 
    AND getOrgtype(id) != 1
) 
AND RACI = 'R'

这个查询会返回所有符合条件的多行obj，可以直接作为WHERE IN的数据源使用。

场景3：用JOIN+聚合重构原查询（更高效） #

原查询多次扫描rel_raci表，效率较低，你可以用LEFT JOIN配合聚合函数来重构，同时获取所有符合条件的obj：

SELECT 
  organization.id,
  STRING_AGG(CASE WHEN r.RACI = 'R' THEN r.obj END, ',') AS responsible_inverted,
  STRING_AGG(CASE WHEN r.RACI = 'A' THEN r.obj END, ',') AS accountable_inverted,
  STRING_AGG(CASE WHEN r.RACI = 'C' THEN r.obj END, ',') AS consulted_inverted,
  STRING_AGG(CASE WHEN r.RACI = 'I' THEN r.obj END, ',') AS informed_inverted
FROM obj_resource organization
LEFT JOIN rel_raci r ON r.PERSON_ID = organization.id
WHERE CONTAINS('2cef8e3d:15992b7f51e:33f', organization.id, -1) 
  AND getOrgtype(organization.id) != 1
GROUP BY organization.id

这种写法只需要扫描一次rel_raci表，性能更好，同时也能得到每个组织对应的所有R/A/C/I角色的obj。

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