使用不存在匹配鍵的SQL查找資料庫表中的匹配行

我有一個包含舊資料和大約 10,000 行的舊表和一個包含大約 500 行的新表。兩個表中的列相同。我需要將新表中的幾列與舊表進行比較，并報告新表中重復的資料。

我研究了具有類似問題的文章，嘗試了表連接以及 where exists / where not exists 子句，但我無法正確獲取 SQL。我已經包含了我的最新版本。

我認為，給我帶來麻煩的一個問題是，在任一表中都沒有像用戶 ID 或類似的唯一識別符號這樣的“密鑰”。

我想要做的是在“新”表中找到資料，其中除了“reference_number”之外的所有行（不管它是否存在）都是重復的，即已經存在于“舊”表中。

到目前為止我有這個...

select 
old.reference_number
new.reference_number
new.component
new.privileges
new.protocol
new.authority
new.score
new.means
new.difficulty
new.hierarchy
new.interaction
new.scope
new.conf
new.integrity
new.availability
new.version
from old, new
where
old.component = new.component
old.privileges = new.privileges
old.protocol = new.protocol
old.authority = new.authority
old.score = new.score
old.means = new.means
old.difficulty = new.difficulty
old.hierarchy = new.hierarchy
old.interaction = new.interaction
old.scope = new.scope
old.conf = new.conf
old.integrity = new.integrity
old.availability = new.availability
old.version = new.version

我在這里嘗試過，但由于某種原因它似乎沒有提取所有資料。

很明顯，實際上舊表中有更多行在新表中重復，但我只得到查詢回傳的少量行。

誰能發現為什么會這樣，我應該用另一種方法來解決這個問題嗎？

如果重要的話，這就是 Postgresql。

感謝您提供的任何幫助。

uj5u.com熱心網友回復：

以下應該做你想要的：

select distinct o.reference_number,
                n.reference_number,
                n.component,
                n.privileges,
                n.protocol,
                n.authority,
                n.score,
                n.means,
                n.difficulty,
                n.hierarchy,
                n.interaction,
                n.scope,
                n.conf,
                n.integrity,
                n.availability,
                n.version
  from new n
  inner join old o
    on o.component = n.component and
       o.privileges = n.privileges and
       o.protocol = n.protocol and
       o.authority = n.authority and
       o.score = n.score and
       o.means = n.means and
       o.difficulty = n.difficulty and
       o.hierarchy = n.hierarchy and
       o.interaction = n.interaction and
       o.scope = n.scope and
       o.conf = n.conf and
       o.integrity = n.integrity and
       o.availability = n.availability and
       o.version = n.version

uj5u.com熱心網友回復：

您應該使用左連接，然后僅選擇具有新值的行為空。sql應該是這樣的：

select 
old.reference_number
new.reference_number
new.component
new.privileges
new.protocol
new.authority
new.score
new.means
new.difficulty
new.hierarchy
new.interaction
new.scope
new.conf
new.integrity
new.availability
new.version
from old 
  left join new
  on
old.component = new.component
old.privileges = new.privileges
old.protocol = new.protocol
old.authority = new.authority
old.score = new.score
old.means = new.means
old.difficulty = new.difficulty
old.hierarchy = new.hierarchy
old.interaction = new.interaction
old.scope = new.scope
old.conf = new.conf
old.integrity = new.integrity
old.availability = new.availability
old.version = new.version
where new.component is null

標籤：sql PostgreSQL

上一篇：[SQLCASEWHEN]當我同時指定兩個條件時，為什么casewhen不起作用？

下一篇：如何將計算輸出作為mysql列？