Saya mencoba menemukan duplikat berdasarkan nama depan, nama keluarga, dan tanggal lahir di database saya. Di bawah ini adalah apa yang saya miliki

Meja pelanggan:

custid  cust_refno  forename surname dateofbirth
1           10        David   John     10-02-1980
2           20        Peter   Broad    15-08-1978
3           30        Sarah   Holly    16-09-1982
4           40        Mathew  Mark     25-08-2001
5           50        Matt    Mark     25-08-2001

Tabel alamat:

addid cust_refno addresstype   line1 
1       10         address     No. 10, Mineview Road
2       10         address     No. 20, Mineview Lane
3       20         address     Rockview cottage, blackthorn
4       30         mobile      0504135864
5       40         address     No. 64, New Lane 
6       40         mobile      0504896532
7       50         address     No. 11, John's cottage 

Beberapa pelanggan memiliki beberapa alamat, jadi mereka bukan duplikat. Saya mencoba menemukan cara untuk menghindari menampilkannya sebagai duplikat. Bisakah Anda memberi saran bagaimana saya bisa melakukannya?

Pertanyaan saya:

SELECT DISTINCT t.FORENAME, t.SURNAME, t.CUST_REFNO, t.DATE_OF_BIRTH , a.LINE1 FROM CUSTOMERS AS t 
LEFT OUTER JOIN dbo.ADDRESS a
ON t.CUST_REFNO = a.CUST_REFNO
    INNER JOIN (
        SELECT FORENAME, surname, DTTM_OF_BIRTH
        FROM CUSTOMERS GROUP BY FORENAME, SURNAME, DATE_OF_BIRTH
        HAVING COUNT(*) > 1) AS td 
    ON t.FORENAME = td.FORENAME AND t.DTTM_OF_BIRTH = td.DATE_OF_BIRTH
        AND t.SURNAME = td.SURNAME 
WHERE a.addresstype = 'address'

Hasil saya adalah:

Forename surname cust_refno dateofbirth line1 
David    John       10       10-02-1980  No. 10, Mineview Road
David    John       10       10-02-1980  No. 20, Mineview Lane

Namun pada kenyataannya itu bukan duplikat. Hanya saja alamatnya berbeda. Apakah ada cara untuk membandingkan cust_refno dan melihat apakah sudah ada sehingga meskipun alamatnya berbeda jika cust_refno sama tidak ditampilkan lagi?

0
newdbcoder 5 Januari 2021, 14:20

3 jawaban

Jawaban Terbaik

Anda dapat menggunakan fungsi jendela untuk menyaring pelanggan dengan lebih dari satu alamat. Kemudian agregasi dapat digunakan untuk mengembalikan duplikat:

select forename, surname, dateofbirth
from customers c join
     (select a.*,
             count(*) over (partition by cust_refno) as cnt
      from addresses a
      where addresstype = 'address'
     ) a
     on c.cust_refno = a.cust_refno
where cnt = 1
group by forename, surname, dateofbirth
having count(*) > 1;

Jika Anda ingin catatan pelanggan lengkap, cukup gunakan fungsi jendela dua kali:

select c.*
from (select c.*,
             count(*) over (partition by forename, surname, dateofbirth) as cnt
      from customers c 
     ) c join
     (select a.*,
             count(*) over (partition by cust_refno) as cnt
      from addresses a
      where addresstype = 'address'
     ) a
     on c.cust_refno = a.cust_refno
where a.cnt = 1 and c.cnt > 1;
1
Gordon Linoff 5 Januari 2021, 11:28

Jika Anda ingin mendapatkan pelanggan dengan alamat duplikat, Anda dapat menghitung berapa kali pelanggan memiliki alamat yang sama dan mengembalikannya hanya dengan lebih dari satu:

SELECT t.FORENAME, t.SURNAME, t.CUST_REFNO, t.DATE_OF_BIRTH , a.LINE1
FROM CUSTOMERS AS t INNER JOIN ADDRESS a ON t.CUST_REFNO = a.CUST_REFNO
GROUP BY t.FORENAME, t.SURNAME, t.CUST_REFNO, t.DATE_OF_BIRTH , a.LINE1
HAVING COUNT(a.LINE1) > 1
2
José Niño 5 Januari 2021, 11:55

Anda dapat menggunakan fungsi analitik count dan row_number sebagai berikut:

select * from
(SELECT t.FORENAME, t.SURNAME, t.CUST_REFNO, t.DATE_OF_BIRTH , 
        a.LINE1,
        row_number() over (partition by t.FORENAME, t.SURNAME, t.DATE_OF_BIRTH 
                           order by 1) as rn,
        count(1) over (partition by t.FORENAME, t.SURNAME, t.DATE_OF_BIRTH) as cnt
   FROM CUSTOMERS AS t 
   LEFT OUTER JOIN dbo.ADDRESS a ON t.CUST_REFNO = a.CUST_REFNO
  WHERE a.addresstype = 'address') t
where cnt > 1 and rn = 1
1
Popeye 5 Januari 2021, 11:27