Iskanje podvojenih vrednosti v tabeli SQL

Z enim poljem zlahka najdete podvojitve:

SELECT name, COUNT(email) 
FROM users
GROUP BY email
HAVING COUNT(email) > 1

Če imamo torej tabelo

ID   NAME   EMAIL
1    John   asd@asd.com
2    Sam    asd@asd.com
3    Tom    asd@asd.com
4    Bob    bob@asd.com
5    Tom    asd@asd.com

Ta poizvedba nam bo dala John, Sam, Tom, Tom, ker imajo vsi isti email.

Vendar pa želim dobiti podvojene osebe z istim naslovom in imenom.

To pomeni, da želim dobiti "Tom", "Tom".

Razlog, zakaj to potrebujem: Naredil sem napako in dovolil vstaviti podvojene vrednosti name in email. Zdaj moram podvojene vrednosti odstraniti/spremeniti, zato jih moram najprej najti.

Pang
Edited question Ndopoldan 25p13.9.20194tni 25trdopoldanni september 2019 в 2:18
Programiranje
sql duplicates
Npopoldan 29p7.4.20102tni 29trpopoldanni april 2010 в 6:17
18 ogledi
Rešitev
SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

Preprosto združite oba stolpca.

Opomba: po starejšem standardu ANSI naj bi bili v GROUP BY vsi stolpci, ki niso združeni, vendar se je to spremenilo z idejo "funkcionalne odvisnosti":

V teoriji relacijskih podatkovnih baz je funkcionalna odvisnost omejitev med dvema nizoma atributov v relaciji iz podatkovne baze. Z drugimi besedami, funkcionalna odvisnost je omejitev, ki opisuje razmerje med atributi v relaciji.

Podpora ni dosledna:

Andriy M
Edited answer Ndopoldan 42p1.6.20184tni 42trdopoldanni junij 2018 в 11:59
Komentarji (10)

poskusite to:

declare @YourTable table (id int, name varchar(10), email varchar(50))

INSERT @YourTable VALUES (1,'John','John-email')
INSERT @YourTable VALUES (2,'John','John-email')
INSERT @YourTable VALUES (3,'fred','John-email')
INSERT @YourTable VALUES (4,'fred','fred-email')
INSERT @YourTable VALUES (5,'sam','sam-email')
INSERT @YourTable VALUES (6,'sam','sam-email')

SELECT
    name,email, COUNT(*) AS CountOf
    FROM @YourTable
    GROUP BY name,email
    HAVING COUNT(*)>1

OUTPUT:

name       email       CountOf
---------- ----------- -----------
John       John-email  2
sam        sam-email   2

(2 row(s) affected)

če želite ID dvojnikov, uporabite to:

SELECT
    y.id,y.name,y.email
    FROM @YourTable y
        INNER JOIN (SELECT
                        name,email, COUNT(*) AS CountOf
                        FROM @YourTable
                        GROUP BY name,email
                        HAVING COUNT(*)>1
                    ) dt ON y.name=dt.name AND y.email=dt.email

OUTPUT:

id          name       email
----------- ---------- ------------
1           John       John-email
2           John       John-email
5           sam        sam-email
6           sam        sam-email

(4 row(s) affected)

za brisanje podvojitev poskusite:

DELETE d
    FROM @YourTable d
        INNER JOIN (SELECT
                        y.id,y.name,y.email,ROW_NUMBER() OVER(PARTITION BY y.name,y.email ORDER BY y.name,y.email,y.id) AS RowRank
                        FROM @YourTable y
                            INNER JOIN (SELECT
                                            name,email, COUNT(*) AS CountOf
                                            FROM @YourTable
                                            GROUP BY name,email
                                            HAVING COUNT(*)>1
                                        ) dt ON y.name=dt.name AND y.email=dt.email
                   ) dt2 ON d.id=dt2.id
        WHERE dt2.RowRank!=1
SELECT * FROM @YourTable

IZHODIŠČE:

id          name       email
----------- ---------- --------------
1           John       John-email
3           fred       John-email
4           fred       fred-email
5           sam        sam-email

(4 row(s) affected)
simhumileco
Edited answer Ndopoldan 10p10.4.20181tni 10trdopoldanni april 2018 в 8:46
Komentarji (0)

Poskusite to:

SELECT name, email
FROM users
GROUP BY name, email
HAVING ( COUNT(*) > 1 )
Komentarji (0)