sql - 左连接后如何找到重复的记录\值？

Question

我有:

• 包含 ID 和名称的 TABLE1。
• 带有 ID 和地址的 TABLE2。

我想获取所有 TABLE1 记录并添加一个 STATUS 列:

• 如果此记录存在于 TABLE2 - 'OK'。
• 如果 TABLE2 中不存在此记录，则通过最后 2 位数字查找匹配项，否则为“NO_RECORD”。
• 如果此记录在 TABLE2 中有重复记录，则如果重复记录具有相同的地址，则选择一个记录 -“确定”，如果它们不具有相同的地址 -“重复”。

与此同时，我已经开始了:

SELECT t1.id,
 t1.name,
 t2.Address,
 iif(Address is null, 'No_RECORD', 'Ok') as 'status'
FROM Table1 as t1
left join Table2 as t2 on t1.id = t2.id

例如:

表1

 id     Name
 111    aaa
 222    bbb
 333    ccc 
 444    ddd
 555    eee
 666    fff
 777    ggg
 888    hhh
 999    iii

表 2:

id  Address
111 rr
922 hfh 
444 vbv
444 vbv
555 xxa
555 plo
555 plo
666 wqq
777 gyt
999 ree
999 ree

我接受的结果是:

id  name Address 'status'
111 aaa  rr      Ok
222 bbb  hfh     Ok
333 ccc          No_RECORD
444 ddd  vbv     Ok
555 eee          Duplicate
666 fff  fff     Ok
777 ggg  wqq     Ok
888 hhh          No_RECORD
999 iii  ree     Ok

• 444不重复，因为2个table2的记录地址匹配，
• 555 是重复的，因为 3 table2 的记录地址不匹配，
• 999 不重复，因为 2 表 2 的记录地址匹配。
• 222 可以，因为 tabl2 中存在其 ID 的最后 2 位数字:“922”。

我该如何继续？ (我在access中使用sql查询)。

Answer 1

我不知道“按最后五位数字匹配”是什么意思。它与您的样本数据无关，所以我只是忽略了问题的这一部分。

你想要做的是在执行 join 之前在 table2 上聚合:

select t1.id, t1.name,
       iif(multiple_addresses = 0, address, null) as address,
       switch(t2.id is null, "No_Record",
              multiple_addresses = 1, "Duplicate",
              1=1, "OK"
             ) as status
from table1 as t1 left join
     (select id, min(address) as address
             iif(min(address) = max(address), min(address), 0, 1) as multiple_addresses
      from table2
      group by id
     ) as t2
     on t1.id = t2.id;