sql - 从子查询中按 ID 列表选择条目？

Question

在 PostgreSQL 数据库中，我发出这样的 SQL 请求:

SQL:

SELECT 
    ARRAY_AGG (QUESTION_ID) AS QUESTIONS 
FROM 
    factors_questions_relationship 
WHERE 
    FACTOR_ID IN (SELECT ARRAY_AGG (FACTOR_ID) AS FACTORS 
                  FROM surveys_factors_relationship 
                  WHERE SURVEY_ID = '9bef1274-f1ee-4879-a60e-16e94e88df38');

错误:

This SQL request raise error:
SQL Error [42883]: ERROR: operator does not exist: integer = integer[]
No operator matches the given name and argument types. You might need to add explicit type casts.

第二个子查询返回 ids 列表:{2,10,12,44,52}。我想在主查询中使用该 ID 列表。如何正确制作？

Answer 1

你可以试试下面-

SELECT ARRAY_AGG (QUESTION_ID) AS QUESTIONS FROM factors_questions_relationship 
where FACTOR_ID IN 
(
   SELECT FACTOR_ID AS FACTORS FROM surveys_factors_relationship 
   WHERE SURVEY_ID = '9bef1274-f1ee-4879-a60e-16e94e88df38'
)