PostgreSQL同一列多值AND条件查询及关联父表需求

我想获取同时满足两个条件的FormResponseQuestions数据，尝试了如下查询：

Select * from "FormResponseQuestions" as FQ where (
          FQ."qId" = 'a04c848c-9cf5-4de5-8d2b-b10d12a16501' 
          AND (
            FQ."oIds" @> ARRAY[ '1385b9cd-662d-43e0-8dbb-1bbc3977c7bb' ] :: UUID[]
          )
      ) AND (
          FQ."qId" = 'e04c848c-9cf5-4de5-8d2b-b10d12a16505' 
          AND (
            FQ."oIds" @> ARRAY[ '68f44665-0200-44dc-94d1-b43a4752b854' ] :: UUID[]
          )
    );

但这个查询返回0行，单独执行两个子查询分别能得到8行和55行结果。

我的实际需求是：获取第一个条件对应的FormResponseQuestions关联的父表FormResponses数据，再获取第二个条件对应的FormResponseQuestions关联的FormResponses数据，然后返回这两组数据的交集（即同时满足两个条件的响应记录）。

注：@>是PostgreSQL的数组包含运算符，用于检查oIds数组是否包含指定元素。我实际想实现的是按回答筛选响应，原查询如下：

SELECT 
  "FormResponse".*, 
  "questions"."id" AS "questions.id", 
  "questions"."rId" AS "questions.rId", 
  "questions"."qId" AS "questions.qId", 
  "questions"."oIds" AS "questions.oIds", 
  "questions"."shortAnswer" AS "questions.shortAnswer", 
  "questions"."longAnswer" AS "questions.longAnswer", 
FROM 
  (
    SELECT 
      "FormResponse"."id", 
      "FormResponse"."formId", 
      "FormResponse"."responderId", 
      "FormResponse"."responseLabels", 
      "FormResponse"."createdAt", 
      "FormResponse"."updatedAt" 
    FROM 
      "FormResponses" AS "FormResponse" 
    WHERE 
      "FormResponse"."formId" = 'a04c848c-9cf5-4de5-8d2b-b10d12a16547' 
      AND (
        SELECT 
          "rId" 
        FROM 
          "FormResponseQuestions" AS "questions" 
        WHERE 
          (
            (
              (
                "questions"."qId" = 'a04c848c-9cf5-4de5-8d2b-b10d12a16501' 
                AND (
                  "questions"."oIds" @> ARRAY[ '1385b9cd-662d-43e0-8dbb-1bbc3977c7bb' ] :: UUID[]
                )
              ) 
              AND (
                "questions"."qId" = 'c04c848c-9cf5-4de5-8d2b-b10d12a16503' 
                AND (
                  "questions"."oIds" @> ARRAY[ '2c47f7cf-0ff8-4741-a260-5f5bc2b39a43' ] :: UUID[]
                )
              )
            ) 
            AND "questions"."rId" = "FormResponse"."id"
          ) 
        LIMIT 
          1
      ) IS NOT NULL 
    ORDER BY 
      "FormResponse"."createdAt" DESC 
    LIMIT 
      100 OFFSET 0
  ) AS "FormResponse" 
  INNER JOIN "FormResponseQuestions" AS "questions" ON "FormResponse"."id" = "questions"."rId" 
  AND (
    (
      "questions"."qId" = 'a04c848c-9cf5-4de5-8d2b-b10d12a16501' 
      AND (
        "questions"."oIds" @> ARRAY[ '1385b9cd-662d-43e0-8dbb-1bbc3977c7bb' ] :: UUID[]
      )
    ) 
    AND (
      "questions"."qId" = 'c04c848c-9cf5-4de5-8d2b-b10d12a16503' 
      AND (
        "questions"."oIds" @> ARRAY[ '2c47f7cf-0ff8-4741-a260-5f5bc2b39a43' ] :: UUID[]
      )
    )
  ) 
ORDER BY 
  "FormResponse"."createdAt" DESC;

我试过用OR、IN等运算符，但结果都不符合预期。

最初的查询返回0行是因为同一个FormResponseQuestions记录不可能同时拥有两个不同的qId，需要换思路：先分别找到满足每个条件的rId（响应ID），再取这些rId的交集，最后关联获取完整的响应和问题数据。

方法一：使用INTERSECT获取共同响应ID #

-- 获取满足第一个条件的响应ID
WITH resp_ids_1 AS (
  SELECT DISTINCT rId
  FROM "FormResponseQuestions"
  WHERE qId = 'a04c848c-9cf5-4de5-8d2b-b10d12a16501'
    AND oIds @> ARRAY['1385b9cd-662d-43e0-8dbb-1bbc3977c7bb']::UUID[]
),
-- 获取满足第二个条件的响应ID
resp_ids_2 AS (
  SELECT DISTINCT rId
  FROM "FormResponseQuestions"
  WHERE qId = 'c04c848c-9cf5-4de5-8d2b-b10d12a16503'
    AND oIds @> ARRAY['2c47f7cf-0ff8-4741-a260-5f5bc2b39a43']::UUID[]
)
-- 关联获取符合条件的完整响应及对应的问题数据
SELECT 
  fr.*,
  fq.id AS "questions.id",
  fq.rId AS "questions.rId",
  fq.qId AS "questions.qId",
  fq.oIds AS "questions.oIds",
  fq.shortAnswer AS "questions.shortAnswer",
  fq.longAnswer AS "questions.longAnswer"
FROM "FormResponses" fr
JOIN (
  SELECT rId FROM resp_ids_1
  INTERSECT
  SELECT rId FROM resp_ids_2
) common_resp ON fr.id = common_resp.rId
JOIN "FormResponseQuestions" fq ON fr.id = fq.rId
WHERE fr.formId = 'a04c848c-9cf5-4de5-8d2b-b10d12a16547'
ORDER BY fr.createdAt DESC
LIMIT 100 OFFSET 0;

方法二：使用分组计数验证满足所有条件 #

这种方法适合扩展到更多条件的场景，只需调整HAVING子句的计数即可：

SELECT 
  fr.*,
  fq.id AS "questions.id",
  fq.rId AS "questions.rId",
  fq.qId AS "questions.qId",
  fq.oIds AS "questions.oIds",
  fq.shortAnswer AS "questions.shortAnswer",
  fq.longAnswer AS "questions.longAnswer"
FROM "FormResponses" fr
JOIN "FormResponseQuestions" fq ON fr.id = fq.rId
WHERE fr.formId = 'a04c848c-9cf5-4de5-8d2b-b10d12a16547'
  AND (
    (fq.qId = 'a04c848c-9cf5-4de5-8d2b-b10d12a16501' AND fq.oIds @> ARRAY['1385b9cd-662d-43e0-8dbb-1bbc3977c7bb']::UUID[])
    OR
    (fq.qId = 'c04c848c-9cf5-4de5-8d2b-b10d12a16503' AND fq.oIds @> ARRAY['2c47f7cf-0ff8-4741-a260-5f5bc2b39a43']::UUID[])
  )
GROUP BY fr.id, fq.id
HAVING COUNT(DISTINCT fq.qId) = 2 -- 确保两个条件都满足
ORDER BY fr.createdAt DESC
LIMIT 100 OFFSET 0;

说明 #

• 方法一通过INTERSECT明确获取两个条件对应的响应ID的交集，逻辑清晰，适合条件较少的场景。
• 方法二通过分组计数，确保每个响应至少匹配两个不同的条件（对应两个不同的问题），扩展性更强，后续增加筛选条件只需修改OR部分和HAVING的计数值。

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