全外連接沒有給出我需要的答案

我正在使用 PostgreSQL，并且很難獲得一系列查詢，這些查詢將兩個表（t1，t2）中的資料組合在一起

t1 是

并且 t2 是

t1 和 t2 通過 StudyIDA 和性別相關聯。我需要的是兩個表的綜合串列，包括年齡。有時 t1 中的年齡等于 t2 中的年齡（例如 StudyIDA=a，age=3），但大多數情況下并非如此。

我正在尋找這樣的表

我在想，首先 t1 和 t2 的完全外部連接會給我想要的東西，但事實并非如此。

然后我想我需要一個所有個人的串列（我們稱之為 t3），然后在一個新表中進行一系列插入（例如 t1 t3 和 t1 t3）以“構建”我需要的內容。當 t1 中的年齡等于 t2 中的年齡（例如 StudyIDA=a，age=3）時，我真的很困惑。

我仍然沒有得到我需要的東西。到目前為止，這是我的代碼

DROP TABLE IF EXISTS t1, t2, t3;

CREATE TEMPORARY TABLE t1 (StudyIDA VARCHAR, gender VARCHAR, age int);
INSERT INTO t1 VALUES 
    ('a','M', 1),('a','M', 2),('a','M', 3),
    ('b','F', 4),('b','F', 5),('b','F', 6),
    ('c','M', 13),('c','M', 14),('c','M', 15);
SELECT * FROM t1;

CREATE TEMPORARY TABLE t2 (StudyIDA VARCHAR, StudyIDB varchar, gender VARCHAR, age int);
INSERT INTO t2 VALUES 
    ('a','z','M', 3), ('a','z','M', 4), ('a','z','M', 5),
    (NULL,'y','F', 7),(NULL,'y','F', 8),(NULL,'y','F', 9),
    ('c','x','M', 10),('c','x','M', 11),('c','x','M', 12),
    (NULL,'w','F', 7),(NULL,'w','F', 8),(NULL,'w','F', 9),
    (NULL,'u','M', 7),(NULL,'u','M', 8),(NULL,'u','M', 9);
SELECT * FROM t2;

CREATE TEMPORARY TABLE t3 (StudyIDA_t1 VARCHAR, gender_t1 VARCHAR, StudyIDA_t2 VARCHAR,StudyIDB varchar,
    gender_t2 VARCHAR);

INSERT INTO t3
    SELECT * FROM (SELECT DISTINCT StudyIDA, gender FROM t1) a FULL OUTER JOIN 
    (SELECT DISTINCT StudyIDA, StudyIDB, gender FROM t2) b 
    ON a.StudyIDA=b.StudyIDA AND a.gender=b.gender
    ORDER BY a.StudyIDA;

SELECT * FROM t3 ORDER BY StudyIDA_t1;


SELECT 'IN t1', * 
    FROM t3 JOIN t1 on t1.StudyIDA=t3.StudyIDA_t1 AND t1.gender=t3.gender_t1
    ORDER BY StudyIDA_t1, StudyIDB;

SELECT 'In t2',* 
    FROM t3 JOIN t2 on t3.StudyIDA_t1=t2.StudyIDA AND t3.gender_t1=t2.gender
    ORDER BY StudyIDA_t1, t3.StudyIDB;

DROP TABLE IF EXISTS t1, t2, t3;

uj5u.com熱心網友回復：

可能包含年齡的完整加入？
和一些共同領域的合并。

SELECT DISTINCT 
  COALESCE(t1.StudyIDA, t2.StudyIDA) AS StudyIDA
, t2.StudyIDB 
, COALESCE(t1.gender, t2.gender) AS gender
, t1.age as ageA
, t2.age as ageB
FROM t1
FULL JOIN t2 
  ON t2.StudyIDA is not distinct from t1.StudyIDA
 AND t2.gender = t1.gender 
 AND t2.age = t1.age
ORDER BY StudyIDA, gender, ageA, ageB;

學習達 | 學習資料庫 | 性別 | 阿吉亞 | 年齡b
:------- | :------- | :----- | ---: | ---:
一個 | 空     | 中號 | 1 | 空
一個 | 空     | 中號 | 2 | 空值
一個 | z | 中號 | 3 | 3
一個 | z | 中號 | 空| 4
一個 | z | 中號 | 空| 5
乙 | 空     | F | 4 | 空
b | 空     | F | 5 | 空
b | 空     | F | 6 | 空
c | 空     | 中號 | 13 | 空
c | 空     | 中號 | 14 | 空
c | 空     | 中號 | 15 | 空
c | x | 中號 | 空| 10
c | x | 中號 | 空| 11
c | x | 中號 | 空| 12
空     | w | F | 空| 7
空     | 是 | F | 空| 7
空     | w | F | 空| 8
空     | 是 | F | 空| 8
空     | w | F | 空| 9
空     | 是 | F | 空| 9
空     | 你| 中號 | 空| 7
空     | 你| 中號 | 空| 8
空     | 你| 中號 | 空| 9

db<>在這里擺弄

uj5u.com熱心網友回復：

您的示例資料表明 only t2.studyidacan beNULL并且所有其他列都應該真正宣告為NOT NULL.
如果是這樣，我建議這個更簡單的查詢：

SELECT studyida, b.studyidb, gender, age
     , CASE WHEN a.age IS NULL THEN 'b'
            WHEN b.age IS NULL THEN 'a'
                               ELSE 'a and b' END as source
FROM   t1 a
FULL   JOIN t2 b USING (studyida, gender, age)
ORDER  BY studyida, gender, age;

db<>在這里擺弄

該USING子句便于同名連接列。結果集中只有一個連接列的實體，有效地COALESCE(a.col, b.col)為您提供了其他資訊。（您可能只使用SELECT *.）
您仍然可以參考具有表限定的源列，例如a.age.

我減少到一age列并添加了source. 你可能想要也可能不想要。

無論哪種方式，“年齡”都會受到 bitrot 的影響，對于表格列幾乎總是錯誤的選擇，并且通常應替換為“生日”或類似內容。

標籤：sql PostgreSQL 外连接

上一篇：在postgresql中選擇2個日期之間的月份

下一篇：具有更改行的PostgreSQL交叉表查詢