postgresql - Postgres 根据查询结果从表中选择

Question

我有两个表，它们的列相同，顺序相同。我希望根据子查询条件连接两个表之一。例如，假设我有以下架构:

CREATE TABLE b (
    bid SERIAL PRIMARY KEY,
    cid INT NOT NULL
);

CREATE TABLE a1 (
    aid SERIAL PRIMARY KEY,
    bid INT NOT NULL REFERENCES b
);

CREATE TABLE a2 (
    aid SERIAL PRIMARY KEY,
    bid INT NOT NULL REFERENCES b
);

我想要一个查询，它根据某些条件在 a1 或 a2 之间执行连接。像这样的东西:

WITH z AS (
  SELECT cid, someCondition FROM someTable
)
SELECT *
FROM CASE z.someCondition THEN a1 ELSE a2 END
JOIN b USING (bid)
WHERE cid = (SELECT cid FROM z);

但是，上述方法不起作用。是否有某种方法可以根据存储在表 z 中的某些 bool 条件，有条件地连接 a1 或 a2？

Answer 1

如果条件是互斥的(我希望它们是):只需执行两个 查询和UNION ALL 它们，使用smart union 构造:

WITH z AS (
  SELECT cid
        , (cid %3) AS some_condition -- Fake ... 
        FROM  b
  )
SELECT *
  FROM a1
  JOIN b USING (bid)
 WHERE EXISTS( SELECT * FROM z
        WHERE some_condition = 1 AND cid = b.cid )
UNION ALL
SELECT *
  FROM a2
  JOIN b USING (bid)
 WHERE EXISTS( SELECT * FROM z
        WHERE some_condition = 2 AND cid = b.cid )
        ;

---

实现相同功能的语法略有不同:

WITH z AS (
  SELECT cid
        , (cid %3) AS some_condition 
        FROM  b
)
SELECT *
  FROM a1
  JOIN b ON a1.bid = b.bid
        AND EXISTS( SELECT * FROM z
        WHERE some_condition = 1 AND cid = b.cid )
UNION ALL
SELECT *
  FROM a2
  JOIN b ON a2.bid = b.bid
        AND EXISTS( SELECT * FROM z
        WHERE some_condition = 2 AND cid = b.cid )
        ;