我有兩張桌子需要連接在一起。但是，我只想在有 unqiue 匹配時加入記錄，而不是選擇幾個中的一個進行加入。

版本控制：

使用 MariaDB 版本 10.3.34

範例數據：

核心（左）數據towns

id     |       town        |    postcode
-----------------------------------------
1      |   Hudderfield     |    HD11 4ER
2      |   Manchester      |    MN14 3JE
3      |   Macklesfield    |    MK17 9FL
4      |   Edinburgh       |    ED5  3MJ
5      |   Liverpool       |    LV9  8XT

加入（右）數據peoples：

id     |       names        |    postcode
-----------------------------------------
1      |   Jimmy Saville   |    HD11 4ER
2      |   Jason Bomb      |    IP14 8FK
3      |   Micky Mouse     |    MK17 9FL
4      |   Bobby Dillian   |    ED5  3MJ
5      |   Lenny Davies    |    ED5  3MJ 

我的 SQL：

我的初始查詢將類似於：

SELECT towns.id, towns.town, peoples.name FROM towns 
      LEFT JOIN people ON towns.postcode = peoples.postcode

但這將包括Edinburgh但愛丁堡有兩個人，我只想在有一個單獨的行加入時加入。

我使用 LEFT 連接，因為我需要返回所有towns但只返回唯一的peoples.

預期成績：

id     |       town        |    names
-----------------------------------------
1      |   Hudderfield     |    Jimmy Saville
2      |   Manchester      |    <null>
3      |   Macklesfield    |    Micky Mouse 
4      |   Edinburgh       |    <null>
5      |   Liverpool       |    <null>

我試過的

我已經嘗試COUNT()在 JOIN 中使用，但無法使其正常工作，

SELECT towns.id, towns.town, peoples.names FROM towns 
      LEFT JOIN people ON towns.postcode = peoples.postcode AND count(peoples.id) = 1 

出現語法錯誤。

我想不出我如何才能限定這個只有在找到一個結果時才加入的加入。網際網路搜尋給了我很多更模糊和離題的參考。

我敢肯定這很簡單，但我做不到。另外，如果可能的話，我想避免子查詢？

---

數據庫小提琴

Lennart 的視窗函式答案的另一種方法是僅對錶使用GROUP BYandHAVING子句peoples來過濾掉具有相同的子句，如下所示postcode：

SELECT towns.id, towns.town, peoples.names
FROM towns
LEFT JOIN
(
   SELECT MAX(names) AS names, postcode
   FROM peoples
   GROUP BY postcode
   HAVING COUNT(name) = 1
) peoples
ON towns.postcode = peoples.postcode

你應該能夠離開加入人民：

(select name, postcode
from (
 select name, postcode, count(1) over (partition by postcode) as cnt
 from peoples
) as t
where cnt = 1)

IE

SELECT t.id, t.town, p.name 
FROM towns t
LEFT JOIN (SELECT name, postcode
          FROM (
              SELECT name, postcode
                   , count(1) over (partition by postcode) as cnt
              FROM peoples
          ) as x
          WHERE cnt = 1
) p
   USING (postcode)

編輯：

鑑於更新中提供的 ddl 我創建了db<>fiddle

SELECT t.id, t.town, p.names 
FROM towns t
LEFT JOIN (
   SELECT names, postcode
   FROM (
      SELECT names, postcode
           , count(1) over (partition by postcode) as cnt
      FROM peoples
   ) as x
   WHERE cnt = 1
) p
   USING (postcode);

它似乎給出了預期的結果

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