Tässä artikkelissa kerrotaan, kuinka löydetään päällekkäisiä rivejä tietokantataulukosta. Tämä on hyvin yleinen aloittelijakysymys. Perustekniikka on yksinkertainen. Näytän myös joitain muunnelmia, kuten ”kaksoiskappaleiden löytäminen kahdesta sarakkeesta” (äskettäinen kysymys #mysql IRC -kanavalla).

Kuinka löytää päällekkäisiä rivejä

Ensimmäinen askel on määritellä, mikä tekee rivistä tarkalleen toisen rivin kopion. Useimmiten tämä on helppoa: niillä on sama arvo joissakin sarakkeissa. Otan tämän toimivaksi määritelmäksi tälle artikkelille, mutta saatat ehkä sinun on muutettava alla olevia kyselyitä, jos käsite ”kopio” on monimutkaisempi.

Tässä artikkelissa käytän näitä esimerkkitietoja:

Kahdella ensimmäisellä rivillä on samat arvo day -sarakkeessa, joten jos pidän niitä kaksoiskappaleina, tässä on kysely niiden löytämiseksi. Kysely käyttää GROUP BY -lauseketta laittamaan kaikki rivit, joilla on sama arvo day, yhteen ryhmään ja laskemaan sitten ryhmä:

Monistettujen rivien lukumäärä on suurempi kuin yksi. Jos haluat nähdä vain päällekkäisiä rivejä, sinun on käytettävä HAVING -lauseke (ei WHERE -lauseke), kuten tämä:

Tämä on perustekniikka: ryhmittele kaksoiskappaleita sisältävän sarakkeen mukaan ja näytä vain ne ryhmät, joilla on enemmän kuin yksi rivi.

Miksi et voi käyttää WHERE-lauseketta?

A WHERE -lauseke suodattaa rivit ennen kuin ne ryhmitellään yhteen. Lauseke HAVING suodattaa ne ryhmittelyn jälkeen. Siksi et voi käyttää WHERE -lauseke edellisessä kyselyssä.

Päällekkäisten rivien poistaminen

Aiheeseen liittyvä kysymys on, kuinka ”kaksoiskappaleet” -rivit poistetaan, kun olet löytää ne. Yleinen t Kysy, kun huono data puhdistetaan, poistamalla kaikki kaksoiskappaleet paitsi yksi, jotta voit laittaa oikeat hakemistot ja ensisijaiset avaimet pöydälle ja estää kaksoiskappaleita pääsemästä taulukkoon uudelleen.

Jälleen ensimmäinen asia on varmistaa, että määritelmäsi on selkeä. Minkä rivin haluat säilyttää? Ensimmäinen? Se, jolla on jonkin sarakkeen suurin arvo? Oletan, että tässä artikkelissa haluat säilyttää ensimmäisen rivin – sen, jolla on pienin arvo id -sarakkeessa. Tämä tarkoittaa, että haluat poistaa kaikki muut rivit.

Luultavasti helpoin tapa tehdä tämä on väliaikainen taulukko. Erityisesti MySQL: ssä on joitain rajoituksia taulukkoon valitsemisesta ja sen päivittämisestä samassa kyselyssä. Voit kiertää nämä, kuten selitän artikkelissani Kuinka valita päivityskohteesta MySQL: ssä, mutta vältän vain nämä komplikaatiot ja käytän väliaikaista taulukkoa.

Tehtävän tarkka määritelmä on poistaa kaikki rivit, joilla on kaksoiskappale, paitsi rivi, jonka arvo on pienin id tälle ryhmälle. Joten sinun on löydettävä paitsi rivit, joissa ryhmässä on enemmän kuin yksi, sinun on myös löydettävä rivi, jonka haluat säilyttää. Voit tehdä sen MIN() -toiminnolla. Tässä on joitain kyselyjä väliaikaisen taulukon luomiseen ja DELETE -toiminnon tekemiseen tarvittavien tietojen löytämiseen:

Nyt kun sinulla on nämä tiedot, voit jatkaa poistamista ”huonot” rivit. Voit tehdä tämän monilla tavoilla, ja jotkut ovat parempia kuin toiset (katso artikkeli monien ongelmien ratkaisemisesta SQL: ssä), mutta vältän jälleen hienommat kohdat ja näytän sinulle vain tavallisen syntaksin, jonka pitäisi toimia mikä tahansa RDBMS, joka tukee alikyselyjä:

Jos RDBMS-järjestelmäsi ei tue alikyselyjä tai jos se on tehokkaampaa, kannattaa ehkä poistaa monitaulukko. Tämän syntaksit vaihtelevat järjestelmittäin, joten sinun on tarkasteltava järjestelmän dokumentaatiota. Saatat joutua tekemään kaiken tämän myös tapahtumassa, jotta muut käyttäjät eivät voi muuttaa tietoja työskentelyn aikana, jos se on huolestuttavaa.

Kuinka löytää kaksoiskappaleita useista sarakkeista

Joku esitti äskettäin samanlaisen kysymyksen #mysql IRC -kanavalla:

Minulla on taulukko, jossa on sarakkeita b ja c, jotka linkittävät kaksi muuta taulukkoa b ja c, ja haluan löytää kaikki rivit, joilla on kaksoiskappaleita joko b tai c.

Oli vaikea ymmärtää tarkalleen, mitä tämä tarkoitti, mutta keskustelun jälkeen ymmärsin sen: henkilö halusi pystyä lisäämään yksilölliset hakemistot sarakkeisiin b ja c erikseen.

On melko helppoa löytää kaksoisarvoisia rivejä jostakin toisesta sarakkeesta, kuten edellä osoitin: ryhmittele vain kyseisen sarakkeen mukaan mn ja lasketaan ryhmän koko. Ja on helppo löytää kokonaisia rivejä, jotka ovat tarkkoja kopioita muista riveistä: ryhmittele vain niin monta saraketta kuin tarvitset.Mutta on vaikeampaa tunnistaa rivejä, joilla on joko päällekkäinen b -arvo tai päällekkäinen c -arvo. Ota seuraava esimerkkitaulukko, joka on suunnilleen sama kuin henkilö kuvaili:

Nyt voit helposti nähdä, että tässä taulukossa on joitain ”päällekkäisiä” rivejä, mutta kahdella rivillä ei ole tosiasiallisesti samaa saraketta {b, c}. Siksi tätä on hieman vaikeampaa ratkaista.

Kyselyt, jotka eivät toimi

Jos ryhmittelet kaksi saraketta yhteen, saat erilaisia tuloksia ryhmittelytapasi mukaan. ja laskea. Täällä IRC-käyttäjä oli tönäistynyt. Joskus kyselyt löysivät joitain kopioita, mutta eivät toisia. Tässä on joitain asioita, joita tämä henkilö kokeili:

Tämä kysely palauttaa taulukon jokaisen rivin COUNT(*) / 1, joka näyttää olevan väärä käytös, mutta se ei todellakaan ole. Miksi? Koska > 1 on ryhmän COUNT() sisällä. Se on melko helppo jättää väliin, mutta tämä kysely on itse asiassa sama kuin

Miksi? Koska (b > 1) on looginen lauseke. Se ei ole ollenkaan mitä haluat. Haluat

Tämä palauttaa tietysti nollan rivin, koska kaksoiskappaleita {b, c} ei ole. Henkilö kokeili monia muita HAVING -lausekkeiden ja OR- ja AND-yhdistelmiä ryhmittelemällä yhden sarakkeen mukaan ja laskemalla toisen ja niin edelleen:

Mikään ei kuitenkaan löytänyt kaikkia kaksoiskappaleita. Mielestäni eniten turhauttavaa on se, että se osittain toimi, mikä sai henkilön ajattelemaan, että se oli melkein oikea kysely … ehkä vain yksi muunnelma saisi sen …

Itse asiassa on mahdotonta tehdä tämän tyyppisellä kyselyllä yksinkertainen GROUP BY kysely. Miksi tämä on? Tämä johtuu siitä, että kun ryhmität yhden sarakkeen mukaan, jaat toisen sarakkeen arvot samanlaisille ryhmille. Näet tämän visuaalisesti järjestämällä kyseisten sarakkeiden mukaan, mitä ryhmittely tekee. Järjestä ensin sarakkeen b mukaan ja katso, miten ne on ryhmitelty:

a	b	c
7	1	1
8	1	2
9	1	3
10	2	1
11	2	2
12	2	3
13	3	1
14	3	2
15	3	3

Kun tilaat (ryhmittele) sarakkeen mukaan b, sarakkeen c jaetaan eri ryhmiin, joten et voi laskea niitä COUNT(DISTINCT c): llä, kuten henkilö yritti tehdä. Kokoomatoiminnot, kuten COUNT(), toimivat vain ryhmässä, eikä niillä ole pääsyä muihin ryhmiin sijoitettuihin riveihin. Vastaavasti, kun tilaat c, sarakkeen b kaksoisarvot jaetaan eri ryhmiin. Tätä kyselyä ei voida tehdä haluamallasi tavalla.

Joitakin oikeita ratkaisuja

Todennäköisesti yksinkertaisin ratkaisu on löytää kaksoiskappaleet jokaiselle sarakkeelle erikseen ja UNION ne yhdessä, näin:

Lähdön what_col -sarakkeessa näkyy, mistä sarakkeesta kaksoisarvo löytyi. Toinen tapa on käyttää alakyselyt:

Tämä on todennäköisesti paljon vähemmän tehokas kuin UNION -lähestymistapa, ja se näyttää kaikki päällekkäiset rivit, ei vain päällekkäisiä arvoja. Vielä yksi lähestymistapa on tehdä itseliittymiä ryhmitettyihin alikyselyihin lausekkeessa FROM. Oikean kirjoittaminen on monimutkaisempaa, mutta se voi olla tarpeen joillekin monimutkaisille tiedoille tai tehokkuudelle:

Mikä tahansa näistä kyselyistä toimii, ja olen varma, että on muitakin tapoja. Jos voit käyttää UNION, se on todennäköisesti helpoin.