postgresql - 重复“IN”子句值列表的最佳方法

Question

我需要在多个IN子句中使用相同的值列表，我尝试使用WITH语句来执行此操作，但无法使其正常工作。

这是一个示例查询：

SELECT * FROM parent WHERE
id IN (SELECT first_id FROM child WHERE id=119896 UNION ALL
       SELECT second_id FROM child WHERE id=119896 UNION ALL
       SELECT third_id FROM child WHERE id=119896) OR
id IN (SELECT was_first_id FROM parent WHERE id IN (SELECT first_id FROM child WHERE id=119896 UNION ALL
                                                    SELECT second_id FROM child WHERE id=119896 UNION ALL
                                                    SELECT third_id FROM child WHERE id=119896)) OR
id IN (SELECT was_second_id FROM parent WHERE id IN (SELECT first_id FROM child WHERE id=119896 UNION ALL
                                                    SELECT second_id FROM child WHERE id=119896 UNION ALL
                                                    SELECT third_id FROM child WHERE id=119896)) OR
id IN (SELECT was_third_id FROM parent WHERE id IN (SELECT first_id FROM child WHERE id=119896 UNION ALL
                                                    SELECT second_id FROM child WHERE id=119896 UNION ALL
                                                    SELECT third_id FROM child WHERE id=119896));

我希望这样做，以便UNION ALL可以在 a 中定义组合的 3 个查询，WITH然后重新使用它来简化查询，如果它也能提高性能，那就太好了。

有没有好的方法来做到这一点？

标签： postgresqlwhere-in

Answer 1

我会建议

SELECT *
FROM parent
WHERE (
  SELECT id IN (first_id, second_id, third_id)
  FROM child
  WHERE id = 119896
) OR (
  SELECT id IN (was_first_id, was_second_id, was_third_id)
  FROM parent
  WHERE (
    SELECT id IN (first_id, second_id, third_id)
    FROM child
    WHERE id = 119896
  )
);

^{（我不知道性能 - 根据评论，这真的很糟糕）}

使用您建议的CTE的替代方法是

WITH child_ids AS (
  SELECT UNNEST(ARRAY[first_id, second_id, third_id]) AS id
  FROM child
  WHERE id = 119896
), all_ids AS (
  SELECT UNNEST(ARRAY[was_first_id, was_second_id, was_third_id]) AS id
  FROM parent
  JOIN child_ids USING (id) -- same as: WHERE id IN (SELECT id FROM child_ids)
  UNION
  TABLE child_ids
)
SELECT *
FROM parent
JOIN all_ids USING (id) -- same as: WHERE id IN (SELECT id FROM all_ids)