Ez a cikk bemutatja, hogyan lehet megkeresni a duplikált sorokat az adatbázis-táblában. Ez egy nagyon gyakori kezdő kérdés. Az alapvető technika egyszerű. Bemutatok néhány variációt is, például a “duplikátumok két oszlopban” megkeresését (nemrégiben feltett kérdés a #mysql IRC csatornán).
Hogyan lehet megkeresni az ismétlődő sorokat
Az első lépés annak meghatározása, hogy pontosan mi teszi a sort egy másik sor duplikátumává. Legtöbbször ez egyszerű: ugyanazon értékkel rendelkeznek egyes oszlopokban. Ezt a cikk működő definíciójának tekintem, de lehet, hogy módosítania kell az alábbi lekérdezéseket, ha a “duplikátum” fogalma bonyolultabb.
Ebben a cikkben a következő mintaadatokat fogom használni:
Az első két sor megegyezik érték a day oszlopban, tehát ha ezeket duplikátumoknak tekintem, itt talál egy lekérdezést. A lekérdezés egy GROUP BY záradékot használ arra, hogy az összes sor azonos day értékkel egy “csoportba” kerüljön, majd megszámolja a csoport:
A duplikált sorok száma nagyobb, mint egy. Ha csak duplikált sorokat szeretne látni, akkor használnia kell egy HAVING záradék (nem egy WHERE záradék), így:
Ez az alapvető technika: csoportosítson a duplikátumokat tartalmazó oszlop szerint, és csak azokat a csoportokat jelenítse meg, amelyeknek egynél több sora van.
Miért nem használhatja a WHERE záradékot?
A A WHERE záradék kiszűri a sorokat, mielőtt csoportosulna. A HAVING záradék csoportosítás után szűri őket. Ezért nem használhat WHERE záradék a fenti lekérdezésben.
Ismétlődő sorok törlése
Kapcsolódó kérdés, hogy miként lehet törölni a ‘duplikált’ sorokat megtalálja őket.Egy közös t Ha a hibás adatok megtisztításakor megkérdezzük, az összes másolat kivételével töröljük az összes másolatot, így megfelelő indexeket és elsődleges kulcsokat tehetünk az asztalra, és megakadályozzuk, hogy az ismétlődések ismét bekerüljenek a táblába.
Ismét az első meg kell győződnie arról, hogy a definíciója világos. Pontosan melyik sort szeretné megtartani? Az első? Valamelyik oszlop legnagyobb értéke? Ennél a cikknél feltételezem, hogy meg akarja tartani az „első” sort – azt, amelyik a id oszlop legkisebb értékével rendelkezik. Ez azt jelenti, hogy minden más sort törölni szeretne.
Valószínűleg a legegyszerűbb módja ennek egy ideiglenes tábla. Különösen a MySQL-ben vannak bizonyos korlátozások a táblából történő kiválasztáshoz és annak frissítéséhez ugyanabban a lekérdezésben. Megkerülheti ezeket, amint azt elmagyarázom a Hogyan válasszon egy frissítési célból a MySQL-ben című cikkemben, de csak elkerülöm ezeket a bonyodalmakat, és ideiglenes táblázatot használok.
A feladat pontos meghatározása: minden olyan sor törlése, amely rendelkezik duplikátummal, kivéve azt a sort, amelynek minimális értéke id az adott csoporthoz. Tehát nemcsak azokat a sorokat kell megtalálnia, ahol egynél több van a csoportban, hanem meg kell találnia azt a sort is, amelyet meg akar tartani. Ezt a MIN() függvénnyel teheti meg. Íme néhány lekérdezés az ideiglenes tábla létrehozásához és a DELETE elvégzéséhez szükséges adatok megkereséséhez:
Most, hogy rendelkezik ezekkel az adatokkal, folytathatja a törlést a „rossz” sorok. Ennek sokféle módja van, és némelyik jobb, mint a többi (lásd cikkemet az SQL sok minden problémájáról), de ismét elkerülöm a finomabb pontokat, és csak megmutatok egy szabványos szintaxist, amelyben működnie kell bármely RDBMS, amely támogatja az alkérdezéseket:
Ha az RDBMS nem támogatja az alkérdezéseket, vagy ha ez hatékonyabb, akkor érdemes több táblázatot törölni. Ennek szintaxisa rendszerenként változó, ezért meg kell vizsgálnia a rendszer dokumentációját. Lehetséges, hogy mindezt egy tranzakció során is meg kell tennie, hogy elkerülje a többi felhasználó adatainak megváltoztatását munka közben, ha ez aggodalomra ad okot.
Hogyan találhatunk meg duplikátumokat több oszlopban
Valaki nemrég tett egy ehhez hasonló kérdést a #mysql IRC csatornán:
Van egy táblázatom oszlopokkal
bésc, amelyek összekapcsolnak két másik táblázatotbésc, és szeretném az összes olyan sor megkeresése, amelynek duplikációja van abvagy ac.
Nehéz volt megérteni, hogy ez pontosan mit jelent, de némi beszélgetés után megértettem: az illető egyedi indexeket akart felvenni a b és a c külön.
Nagyon egyszerű az egyik vagy a másik oszlopban duplikált értékeket tartalmazó sorokat találni, amint azt fentebb bemutattam: csak csoportosítson az adott oszlop alapján mn és számolja meg a csoport méretét. És könnyű megtalálni a teljes sorokat, amelyek pontosan másolatai a többi sornak: csak annyi oszlop szerint csoportosítson, amennyire szüksége van.De nehezebb azonosítani azokat a sorokat, amelyek vagy duplikált b vagy duplikált c értékkel rendelkeznek. Vegyük a következő mintatáblát, amelyet nagyjából az adott személy írt le:
Most már könnyen láthatja, hogy ebben a táblázatban vannak „duplikált” sorok, de valójában nincs két sor ugyanaz a duplával {b, c}. Ezért ezt egy kicsit nehezebb megoldani.
Nem működő lekérdezések
Ha két oszlop szerint csoportosít, akkor a csoportosítás módjától függően különféle eredményeket kap. és számolj. Itt tapadt az IRC felhasználó. Néha a lekérdezések másolatokat találtak, de másokat nem. Íme néhány dolog, amit ez a személy kipróbált:
Ez a lekérdezés a táblázat minden sorát visszaadja COUNT(*) of 1, ami téves viselkedésnek tűnik, de valójában nem az. Miért? Mivel a > 1 benne van a COUNT(). Elég könnyű kihagyni, de ez a lekérdezés valójában megegyezik a
Miért? Mivel az (b > 1) logikai kifejezés. Egyáltalán nem ezt akarja. Szeretne
Ez természetesen nulla sort ad vissza, mert nincsenek ismétlődő {b, c} sorok. A személy kipróbálta a HAVING záradékok, valamint az OR és AND sok más kombinációját, az egyik oszlop szerint csoportosítva, a másikat megszámolva stb.:
Semmi sem találta meg az összes másolatot. Szerintem az okozta a legnagyobb frusztráltságot, hogy részben működött, és arra késztette az embert, hogy majdnem megfelelő lekérdezés legyen … talán csak egy másik változat érné el …
Valójában lehetetlen megtenni az ilyen típusú lekérdezéseket egyszerű GROUP BY lekérdezés. Miért ez? Ez azért van, mert amikor egy oszlop szerint csoportosít, akkor a másik oszlop értékeit több csoportra osztja szét. Ezt vizuálisan láthatja az oszlopok szerinti sorrendben, ezt teszi a csoportosítás. Először a b oszlop szerint rendezze, és nézze meg, hogyan vannak csoportosítva:
| 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 |
Amikor b oszlop szerint rendel (csoportosít), az c különböző csoportokba vannak osztva, ezért nem számolhatja őket COUNT(DISTINCT c) vel, ahogy az illető megpróbálta. Az olyan összesített függvények, mint a COUNT(), csak egy csoporton belül működnek, és nem férnek hozzá más csoportokba sorolt sorokhoz. Hasonlóképpen, amikor c alapján rendel, az b oszlopban található duplikált értékek különböző csoportokba kerülnek. Ezt a lekérdezést nem lehet a kívánt módon megtenni.
Néhány helyes megoldás
Valószínűleg a legegyszerűbb megoldás az, ha az oszlopok duplikátjait külön és UNION őket együtt, így:
A kimenet what_col oszlopa jelzi, hogy a duplikált érték melyik oszlopban található. Egy másik megközelítés a alkérdezések:
Ez valószínűleg sokkal kevésbé hatékony, mint a UNION megközelítés, és minden duplikált sort megjelenít, nem csak a duplikált értékeket. Egy másik megközelítés az, hogy öncsatlakozásokat kell végrehajtani a csoportosított lekérdezésekkel szemben a FROM záradékban. Ennek bonyolultabb a helyes írása, de szükség lehet néhány összetett adat vagy hatékonyság érdekében:
A lekérdezések bármelyike megfelel, és biztos vagyok benne, hogy vannak más módszerek is. Ha használhatja a UNION alkalmazást, akkor valószínűleg ez a legegyszerűbb.
