從一個表中選擇一行，如果它不存在，從另一個表中選擇

如何從一個表中選擇一行，但如果它不存在，則退回到從輔助表中選擇它？這是我要實現的目標的簡化版本

create table table_1 (
 person TEXT,
 favourite_number INT
 );
 
 create table table_2 (
   person TEXT,
   favourite_number INT
 );
 
 insert into table_1 (person, favourite_number) values 
 ('Bob', 1),
 ('Fred', 2)
 ;
 
   insert into table_2 (person, favourite_number) values 
 ('Bob', 30),
 ('Alice', 70)
 ;

我想得到以下結果：

| person | favourite_number |
|--------|------------------|
| Bob    | 1                |
| Alice  | 70               |
| Fred   | 2                |

請注意它是如何從第一張桌子上挑選出 Bob 和 Fred 的。即使 Bob 出現在第二張桌子上，因為我們已經得到了他，所以我們將他從第一張桌子上帶走。Alice 只出現在第二張桌子上。

到目前為止，這是我嘗試過的，但我無法讓所有 3 個都返回，請幫忙。

select
 t1.*
from table_1 t1
where t1.person not in (select person from table_2)
union
select
 t2.*
from table_2 t2
where t2.person not in (select person from table_1)
;

如果最好從第一個表中選擇行，則應取出當該人存在於另一個表中時將其刪除的過濾器。

也不需要區分行，所以使用union all代替union

select
 t1.*
from table_1 t1
union all
select
 t2.*
from table_2 t2
where t2.person not in (select person from table_1)

如果您的第一張桌子總是優先考慮並且您只有兩張桌子要檢查並且非常簡單，那麼 Andrew Sayer 的回答應該就足夠了。這是一種不同的方式，只需幾個步驟，您就可以支持兩個以上的表，並允許您重新確定要從這些表中得到結果的順序的優先級：

WITH CTE_AllTables AS
(
   SELECT person, favourite_number, 1 AS table_priority
   FROM table_1 t1

   UNION ALL

   SELECT person, favourite_number, 2 AS table_priority
   FROM table_2 t2
),
CTE_AllTables_Sorted AS
(
   SELECT person, favourite_number, ROW_NUMBER() OVER (PARTITION BY person ORDER BY table_priority) AS sort_id -- Generates a unique ID per person sorted by table_priority
   FROM CTE_AllTables
)

SELECT person, favourite_number
FROM CTE_AllTables_Sorted
WHERE sort_id = 1 -- Filters out anything but the first occurrence of a person by table priority

請注意，您可能希望PARTITION在一個關鍵欄位上，person_id而不是person因為即使在同一個表中也可以有兩個同名的人，但我只有您的範例可供參考。

以下是如何將上述查詢用於具有多個表和不同優先級的範例：

WITH CTE_AllTables AS
(
   SELECT person, favourite_number, 1 AS table_priority
   FROM table_1 t1

   UNION ALL

   SELECT person, favourite_number, 2 AS table_priority
   FROM table_2 t2

   UNION ALL

   SELECT person, favourite_number, 4 AS table_priority
   FROM table_3 t3

   UNION ALL

   SELECT person, favourite_number, 3 AS table_priority
   FROM table_4 t4
),
CTE_AllTables_Sorted AS
(
   SELECT person, favourite_number, ROW_NUMBER() OVER (PARTITION BY person ORDER BY table_priority) AS sort_id -- Generates a unique ID per person sorted by table_priority
   FROM CTE_AllTables
)

SELECT person, favourite_number
FROM CTE_AllTables_Sorted
WHERE sort_id = 1 -- Filters out anything but the first occurrence of a person by table priority

四個表，不需要多個WHERE謂詞，並註意我如何根據我給它們的值table_4優先級。您甚至可以在 final 中包含該欄位以查看它來自哪個表。:)table_3``table_priority``table_priority``SELECT

引用自：https://dba.stackexchange.com/questions/285348