mysql5: Query

[Thomas E.]

Hi,

mir stellt sich gerade die ehrenwerte Aufgabe, eine Tabelle mit 2000 Einträgen und 30 Spalten je Eintrag daraufhin zu prüfen, ob sich völlig identische Zeilen darin befinden.

Für eine Spalte ist mir das klar:

  
SELECT Spalte, COUNT(*) AS zaehler  
FROM tabelle  
GROUP BY Spalte  
HAVING zaehler > 1  
ORDER BY zaehler DESC  

Aber wie prüfe ich das für alle 30 Spalten gleichzeitig?

Gruß, Thomas

[Ilja]

moin,

Aber wie prüfe ich das für alle 30 Spalten gleichzeitig?

gibt mehrere möglichkeiten, grundsätzlich aber genauso wie, wie du es mit einer spalte gemacht hast, machst du das gleiche für alle 30, sprich einfach hinter dem GROUP BY alle 30 spalten angeben und gut ist. falls die tabelle einen primary key hat, den natürlich ausnehmen.

Ilja

[Thomas E.]

gibt mehrere möglichkeiten, grundsätzlich aber genauso wie, wie du es mit einer spalte gemacht hast, machst du das gleiche für alle 30, sprich einfach hinter dem GROUP BY alle 30 spalten angeben und gut ist. falls die tabelle einen primary key hat, den natürlich ausnehmen.

Ilja

Hi Ilja,

meinst Du das so?

  
SELECT Spalte, COUNT(*) AS zaehler  
FROM tabelle  
GROUP BY Spalte, Spalte2, Spalte3  
HAVING zaehler > 1  
ORDER BY zaehler DESC  

Oder so?

  
SELECT Spalte,Spalte2, Spalte3 COUNT(*) AS zaehler  
FROM tabelle  
GROUP BY Spalte, Spalte2, Spalte3  
HAVING zaehler > 1  
ORDER BY zaehler DESC  

Oder noch anders?

Schöne Pfingstgrüsse, Thomas

[Ilja]

moin,

beides führt zum erfolg, wichtig ist, worüber du gruppierst. solltest zwar nur spalten ausgeben, über die du auch gruppiert hast oder eine aggregat-funktion verwenden. du kannst aber auch spalten bei der anzeige weglassen (nicht in der gruppierung).

Ilja

[Thomas E.]

moin,

beides führt zum erfolg, wichtig ist, worüber du gruppierst. solltest zwar nur spalten ausgeben, über die du auch gruppiert hast oder eine aggregat-funktion verwenden. du kannst aber auch spalten bei der anzeige weglassen (nicht in der gruppierung).

Ilja

Hallo Ilja,

das heißt, dass das Statement:

  
SELECT Spalte,Spalte2, Spalte3 COUNT(*) AS zaehler  
FROM tabelle  
GROUP BY Spalte, Spalte2, Spalte3  
HAVING zaehler > 1  
ORDER BY zaehler DESC

wirklich nur die Datensätze ausgibt, die in Spalte, Spalte2 und Spalte3 identisch sind?

Darf ich Dir in diesem Zusammenhang 2 weitere Fragen stellen?
Ich mach mal, ich hoffe, ich überstrapaziere Deine Hilfsbereitschaft noch nicht.

Frage 1:

Wenn das Statement von oben wirklich die Datensätze ausgibt, die in Spalte, Spalte2 und Spalte3 identisch sind, könnte ich dann obige Query so ergänzen, dass nur die Datensätze in die Ergebnissmenge einfließen, die in Spalte, Spalte2 und Spalte3 idetisch sind, aber zusätzlich in spalte 4 NICHT identisch sind?

Frage2:

Kann ich das Statement:

  
SELECT Spalte COUNT(*) AS zaehler  
FROM tabelle  
GROUP BY Spalte  
HAVING zaehler > 1  
ORDER BY zaehler DESC  

so ergänzen, dass in den Ergebnisszeilen nicht nur einfach die Mehrfacheinträge aufgeführt werden, sonden auch 2-3 Zusätzliche Spalten zu jedem Mehrfacheintrag?

Vielleicht mit einer Subquery?

  
select Spalte, Spalte1, Spalte2  
from tabelle  
where Spalte IN (  
SELECT COUNT(Spalte) AS zaehler  
FROM tabelle  
GROUP BY Spalte  
HAVING zaehler > 1  
ORDER BY zaehler DESC  
)  

Naja, so gehts jedenfalls anscheinend nicht.  Meine DB arbeitet, aber ich glaube, ich habe sie gerade in sowas wie ne Endlosschleife getrieben? Sie reagiert jedenfalls nicht mehr auf diese Query.

Viele Grüße, Thomas

[Ilja]

moin

wirklich nur die Datensätze ausgibt, die in Spalte, Spalte2 und Spalte3 identisch sind?

jein, es gibt nicht die datensätze aus, sondern die zusammengesetzen spaltenwerte, die es mehr als einmal gibt. sprich wenn du 2 datensaätze hast, die sich in den drei spalten gleichen, dann gibt die abfrage einen datensatz mit den entsprechend werten der spalten zurück und mit dem COUNT(*) die anzahk, wieviele datensätze alle diese gleichen werte habe. gu gruppierst quasi und dadurch wird die menge der datensätze, die die abfrage zurück gibt kleiner als die anzahl der datensätze, die tatsächlich in der der tabelle vorhanden sind. willst du, dass wirklich jeder datensatz angezeigt wird, dann benutzt du am besten eine korrelierte unterabfrage. wichtig in diesem zusammhang wäre es aber, ob deine tabelle einen primary key hat.

Wenn das Statement von oben wirklich die Datensätze ausgibt, die in Spalte, Spalte2 und Spalte3 identisch sind, könnte ich dann obige Query so ergänzen, dass nur die Datensätze in die Ergebnissmenge einfließen, die in Spalte, Spalte2 und Spalte3 idetisch sind, aber zusätzlich in spalte 4 NICHT identisch sind?

ja, das kann man sicherlich, aber auch hier gilt erst einmal zu klären, was genau du damit meinst. stell dir 3 datensätze vor, die sich in den ersten drei spalten gleichen, in der vierten gleichen sich zwei miteinander, aber nicht alle drei. welcher der drei soll nun angezeigt wwerden, musse dazu alle drei sich in der vierten spalte ebenfalls gleichen oder wenn nur einer sich gleicht, dann soll der nicht mehr genommen werden ?

so ergänzen, dass in den Ergebnisszeilen nicht nur einfach die Mehrfacheinträge aufgeführt werden, sonden auch 2-3 Zusätzliche Spalten zu jedem Mehrfacheintrag?

gefährliches thema unter mysql, das hier viel diskutiert wird. mysql ist bei gruppiereungen einen eigenen weg gegangen. den zu erklären bin ich inzwischen müde geworden ;-)

aber klar, auch dafür gibt es mittel und wege, eine unterabfrage wäre sicherlich einer davon, aber es hängt immer davon ab, was genau du haben willst.

Ilja

[Thomas E.]

willst du, dass wirklich jeder datensatz angezeigt wird, dann benutzt du am besten eine korrelierte unterabfrage. wichtig in diesem zusammhang wäre es aber, ob deine tabelle einen primary key hat.

Ja, genau das würde ich gerne erreichen.
Und ja, die Tabelle hat die von mysql vergebene Spalte ID (auto_increment) als Primary key angegeben.

Wenn das Statement von oben wirklich die Datensätze ausgibt, die in Spalte, Spalte2 und Spalte3 identisch sind, könnte ich dann obige Query so ergänzen, dass nur die Datensätze in die Ergebnissmenge einfließen, die in Spalte, Spalte2 und Spalte3 idetisch sind, aber zusätzlich in spalte 4 NICHT identisch sind?

ja, das kann man sicherlich, aber auch hier gilt erst einmal zu klären, was genau du damit meinst. stell dir 3 datensätze vor, die sich in den ersten drei spalten gleichen, in der vierten gleichen sich zwei miteinander, aber nicht alle drei. welcher der drei soll nun angezeigt wwerden, musse dazu alle drei sich in der vierten spalte ebenfalls gleichen oder wenn nur einer sich gleicht, dann soll der nicht mehr genommen werden ?

Es sollen dann alle 3 Datensätze angezeigt werden.

gefährliches thema unter mysql, das hier viel diskutiert wird. mysql ist bei gruppiereungen einen eigenen weg gegangen. den zu erklären bin ich inzwischen müde geworden ;-)

Ok. Dann werde ich versuchen, diesen Unterschied woanders zu erroieren.

Grüße bis hierhin wieder

Thomas

[Ilja]

moin,

Ja, genau das würde ich gerne erreichen.

SELECT t1.*
FROM tabelle t1
WHERE EXISTS (SELECT NULL
              FROM tabelle t2
              WHERE t2.spalte1 = t1.spalte1 -- ab hier korreliert die unterabfrage
              AND t2.spalte2 = t1.spalte2
              AND t2.spalte3 = t1.spalte3
              AND t2.id <> t1.id
             )
ORDER BY t1.spalte1, t1.spalte2, t1.spalte3
;

diese abfrage gibt dir alle datensätze aus, die in den spalten 1-3 doppelte werte haben. die abfrage kann länger dauern, je nachdem wieviele datensätze die tabelle enthält und ob indexe gesetzt sind.

Es sollen dann alle 3 Datensätze angezeigt werden.

SELECT t1.*
FROM tabelle t1
WHERE EXISTS (SELECT NULL
              FROM tabelle t2
              WHERE t2.spalte1 = t1.spalte1
              AND t2.spalte2 = t1.spalte2
              AND t2.spalte3 = t1.spalte3
              AND t2.spalte4 <> t1.spalte4 -- Spalte 4 pruefung
              AND t2.id <> t1.id
             )
ORDER BY t1.spalte1, t1.spalte2, t1.spalte3
;

hier hast du nun folgendes: falls es einen datensatz gibt, der die gleichen spalten 1-3 hat und in der spalte 4 einen anderen wert, dann anzeigen.

Ok. Dann werde ich versuchen, diesen Unterschied woanders zu erroieren.

du musst es dir nicht woanders anschauen, du kannst hier das archiv danach durchsuchen, stichwörter wären mysql GROUP BY, Vinzenz oder Ilja

Ilja

[Thomas E.]

Hallo Ilja,

du musst es dir nicht woanders anschauen, du kannst hier das archiv danach durchsuchen, stichwörter wären mysql GROUP BY, Vinzenz oder Ilja

Das werde ich machen.

diese abfrage gibt dir alle datensätze aus, die in den spalten 1-3 doppelte werte haben. die abfrage kann länger dauern, je nachdem wieviele datensätze die tabelle enthält und ob indexe gesetzt sind.

Hm. Wenn ich Deine Abfrage 1:1 übernehme, liefert sie wirklich das gewünschte Ergebniss.

Nun habe ich sie aber leicht verändert und das Ergebniss erstaunt mich:

  
SELECT a1.*  
FROM tabelle1 a1  
WHERE EXISTS (SELECT NULL  
FROM tabelle1 a2  
WHERE a2.ArtikelNummer = a1.ArtikelNummer  
AND a1.ArtikelNummer <> ''  
)  
ORDER BY a1.ArtikelNummer DESC  

Gewünscht wäre hier das Ergebniss, mir alle Daten zu doppelt vergebenen Artikelnummern (außer, wenn gar keine Artikelnummer vergeben wurde) der Tabelle "tabelle1" zu liefern.

Die Ergebnissmenge ist aber weitaus umfangreicher, wie mir scheint.

Habe ich einen Denkfehler bei der Umsetzung gemacht?

Grüße, Thomas

[Ilja]

moin,

Hm. Wenn ich Deine Abfrage 1:1 übernehme, liefert sie wirklich das gewünschte Ergebniss.

das freut mich zu hören.

Nun habe ich sie aber leicht verändert und das Ergebniss erstaunt mich:
Die Ergebnissmenge ist aber weitaus umfangreicher, wie mir scheint.
Habe ich einen Denkfehler bei der Umsetzung gemacht?

da fällt mir spontan der satz ein: "den sie wissen nicht, was sie tun...". aber spass beiseite, du hast einen denkfehler, der sich aber schnell aufklären läßt. was wir benutzen ist eine korrelierte unterabfrage und das auch noch auf ein und dieselbe tabelle, um doppelte datensätze aufzuspüren. die betonung liegt dabei auf dieselbe tabelle, sprich du hast jeden datensatz schon mal von hause aus doppelt, nämlich wenn der datensatz mit sich selber verglichen wird. deshalb ist das ungleich aus meiner abfrage wichtig, wo t1.id <> t2.id steht. das ist der primary key und sorgt dafür, das sich dieselben datensätze nicht mit sich selbst vergleichen, da dort die werte natürlich immer gleich sind. und dieser ausschluß fehlt bei dir. was du suchst ist also folgendes:

SELECT a1.*
FROM tabelle1 a1
WHERE (a1.ArtikelNummer <> '' OR IS NULL)
AND EXISTS (SELECT NULL
            FROM tabelle1 a2
            WHERE a2.ArtikelNummer = a1.ArtikelNummer
            AND a2.id <> a1.id
           )
ORDER BY a1.ArtikelNummer DESC
;

noch ein hinweis, bei mysql ist ein wert wie '' nicht NULL. und prüfungen auf NULL gehen mit dem IS oeprator. du musst halt schauen, ob du '' werte hast oder auch NULL spalten.

Ilja

[Ilja]

habe einen spaltennamen vergessen, wo ist bloss die edit-funktion. ach ja, die gibt es ja nicht....

SELECT a1.*
FROM tabelle1 a1
WHERE (a1.ArtikelNummer <> '' OR a1.ArtikelNummer IS NULL)
AND EXISTS (SELECT NULL
            FROM tabelle1 a2
            WHERE a2.ArtikelNummer = a1.ArtikelNummer
            AND a2.id <> a1.id
           )
ORDER BY a1.ArtikelNummer DESC
;

Ilja

[Thomas E.]

das freut mich zu hören.

Hallo Ilja,

bis hierhin schonmal vielen 1000 Danke. Mir ist es gelungen, mithilfe Deiner Hilfestellungen 2 tabellen miteinander zu vergleichen, die jeweils ein paar Tausend Einträge haben.

Das hat wirklich edelst funktioniert!

Auch mein 2. Vorhaben, innerhalb einer Tabelle doppelte Einträge nicht nur zu zählen, sondern auch in der Ergebnissmenge auszuweisen, hat perfekt geklappt. Danke auch für die Erklärung zu meinem kleinen Denkfehler. So weit hatte ich nicht gedacht, dass sich auch der Datensatz innerhalb derselben Tabelle mit sich selber vergleicht.

Darf ich Dich weiter fragen? ;-)

Das nächste, was ich interessant fände, wäre nämlich, mir die Artikel aus einer Tabelle anzeigen zu lassen, die in der anderen Tabelle nicht enthalten sind.

Was hältst Du von meinem Vorschlag für das Problem?

  
SELECT  ArtikelID,  
        Artikelbezeichnung  
FROM    Table1  
    LEFT JOIN Table2  
        ON Table1.Artikelbezeichnung= Table2.Artikelbezeichnung  
WHERE   Table2.ArtikelID IS NULL  

Wofür ich aber leider gar keinen Vorschlag habe, ist folgende Problemstellung: (ich versuche mal, es in Worte zu fassen)

Suche mir aus jedem Datensatz der Tabelle 1 den Wert der Spalte XYZ und trage sie in den Datensatz der Tabelle 2 in Spalte XYZ2 ein, wo Tabelle1.Spalte1 = Tabelle2.Spalte1 ist UND Tabelle1.Spalte2 = Tabelle2.Spalte2 ist UND Tabelle1.Spalte3 = Tabelle2.Spalte3 ist.

Also geht es letztlich darum, dass mir in Tabelle 2 eine bestimmte Spalte fehlt und ich sie nachtragen möchte. Leider gibt es keinen eindeutigen Wert, den ich hierzu heranziehen könnte, sodaß ich mit der Kombination verschiedener "Hilfswerte" den entsprechenden Datensatz "dingfest" machen will.

Ich hoffe, ich konnte erklären, was ich eigentlich erreichen will.

Grüße, Thomas

[Ilja]

moin,

Das hat wirklich edelst funktioniert!

dafür sind wir hier da, sich gegenseitig zu helfen. aber dank nimmt man auch immer gerne entgegen, das ist quasi unsere droge.

Darf ich Dich weiter fragen? ;-)

ab hier muss ich eine beratungspauschale in höhe eines biers zur auslieferung in hamburg nehmen....

Was hältst Du von meinem Vorschlag für das Problem?

dein ansatz ist gar nicht so schlecht, aber birgt auch risiken. SQL ist als es entwickelt wurde ganz bewußt an der natürlichen, englischen sprache angelehnt. das hat den vorteil, dass man fragestellung, bzw. anforderungen lesen und dann in SQL übersetzen kann. das klappt natürlich nicht 1:1, aber so weit davon weg ist es auch nicht. klar ist erfahrung dabei auch sehr wichtig. also wenden wir uns noch mal deiner anforderung zu: "mir die Artikel aus -> einer <- Tabelle anzeigen zu lassen, die in der anderen Tabelle nicht enthalten sind."

man muss sehr genau lesen, du willst die artikel aus einer tabelle angezeigt haben, die ein bestimmtes kriterium erfüllen. was du eigentlich nicht willst, sind zwei tabellen miteinander zu verbinden, auch nicht mit einem OUTER JOIN. und das führt mich zu einem grundsatz: JOINS sind "böse". natürlich sind sie das nicht immer, aber es ist ein leitsatz und man tut gut daran, sich an ihm zu orientieren. warum das so ist, erkläre ich dir an deinem beispiel.

tabellen stehen oftmals in einem 1:n verhältnis zueinander, nicht immer, aber sehr oft. dadurch wird die ergebnismenge beeinflusst, sprich die anzahl der datensätze wird bei einem JOIN größer, die aus einer abfrage zurück gegeben werden. das muss nicht immer fall sein und in deinem beispiel bekommst du sogar die richtige anzahl der datensätze, weil du auf IS NULL prüfst. insofern könnte man denken, alles ist ok. aber ich würde es als schlechten stil bezeichnen. will ich nur datensätze aus einer tabelle, dann mache ich keinen join, sondern wieder eine korrelierte unterabfrage. was ich meine wird klarer, wenn du deine anforderung ein wenig abänderst in dem sinne, dass du datensätze haben willst, die auch in der anderen tabelle vorkommen. man könnte dann versucht sein einfach auf IS NOT NULL zu prüfen, bzw. einen INNER JOIN zu machen. und schon ist das dilema gross, zumal das einem auch nicht sofort ins auge fällt. aber die anzahl der datensätze kann nun falsch sein. sprich eine unterabfrage ist einfacher besser, damit bekommt man nie probleme mit der anzahl der datensätze.

(ich versuche mal, es in Worte zu fassen)

worte sind besser als SQL abfragen, noch besser sind immer beispieldaten

Suche mir aus jedem Datensatz der Tabelle 1 den Wert der Spalte XYZ und trage sie in den Datensatz der Tabelle 2 in Spalte XYZ2 ein, wo Tabelle1.Spalte1 = Tabelle2.Spalte1 ist UND Tabelle1.Spalte2 = Tabelle2.Spalte2 ist UND Tabelle1.Spalte3 = Tabelle2.Spalte3 ist.

im grunde genommen genauso wie der SELECT, nur eben als UPDATE.

UPDATE tabelle2 t2
SET t2.XYZ2 = (SELECT XYZ
               FROM tabelle1 t1
               WHERE t1.Spalte1 = t2.Spalte1
               AND t1.Spalte2 = t2.Spalte2
               AND t1.Spalte3 = t2.Spalte3
              )
;

die abfragen kann "knallen" und zwar genau dann, wenn es mehrere werte beim abgleich der drei spalten zurück geliefert werden. ist das der fall, muss man die dateninhalte noch mal genauer durchleuchten. knallt es nicht, bist du am ziel.

Ilja

[Thomas E.]

Hallo Ilja,

sorry, dass ich erst jetzt poste, ich hätte mich gerne zeitnäher bedankt, aber es ging zeitlich einfach nicht. Und auch jetzt bin ich etwas in Eile...

ab hier muss ich eine beratungspauschale in höhe eines biers zur auslieferung in hamburg nehmen....

Na, die 600km nehme ich doch gerne in Kauf *lol* Ich bin aber demnächst auf der Durchreise. Wenn Du also Lust auf ein Bierchen gegen 5 Uhr morgens hast, lass es mich wissen *g*

grundsatz: JOINS sind "böse". natürlich sind sie das nicht immer, aber es ist ein leitsatz und man tut gut daran, sich an ihm zu orientieren.

aber ich würde es als schlechten stil bezeichnen.

Ich glaube, ich habe hier schonmal gelesen, dass Du und Vinzenz sich hier in der Vorgehensweise unterscheiden, ich konnte mir seinerzeit nur auf diesen Hinweis keinen Reim machen. Klappt nun schon besser, wenngleich ich gestehen muß, dass Deine Erklärung nicht leicht zu verstehen ist.
Ich habe fast den Eindruck, dass hierfür mehr Datenbankerfahrung nötig ist, um den entsprechenden Instinkt für Fehleranfälligkeiten zu entwickeln.

(ich versuche mal, es in Worte zu fassen)

worte sind besser als SQL abfragen, noch besser sind immer beispieldaten

Ist wahr? Dann werde ich künftig gerne Abfragen (die ich suche) in Worte fassen, denn das fällt mir mitunter leichter, als eine erste Abfrage als Beweis von Eigenbemühungen zu verfassen ;-)

Suche mir aus jedem Datensatz der Tabelle 1 den Wert der Spalte XYZ und trage sie in den Datensatz der Tabelle 2 in Spalte XYZ2 ein, wo Tabelle1.Spalte1 = Tabelle2.Spalte1 ist UND Tabelle1.Spalte2 = Tabelle2.Spalte2 ist UND Tabelle1.Spalte3 = Tabelle2.Spalte3 ist.

im grunde genommen genauso wie der SELECT, nur eben als UPDATE.

UPDATE tabelle2 t2
SET t2.XYZ2 = (SELECT XYZ
               FROM tabelle1 t1
               WHERE t1.Spalte1 = t2.Spalte1
               AND t1.Spalte2 = t2.Spalte2
               AND t1.Spalte3 = t2.Spalte3
              )
;

die abfragen kann "knallen" und zwar genau dann, wenn es mehrere werte beim abgleich der drei spalten zurück geliefert werden. ist das der fall, muss man die dateninhalte noch mal genauer durchleuchten. knallt es nicht, bist du am ziel.

Ja, da gebe ich Dir recht. Probelm scheint aber zu sein, dass ich gar nicht mitkriege, ob die Abfrage knallt. Oder bist Du da anderer meinung?

Viele Grüße und vorab schonmal ein virtuelles kühles Blondes nach HH

Thomas

[Ilja]

moin,

Na, die 600km nehme ich doch gerne in Kauf *lol* Ich bin aber demnächst auf der Durchreise. Wenn Du also Lust auf ein Bierchen gegen 5 Uhr morgens

hast, lass es mich wissen *g*

die frage ist, ob ein bier es wert ist, so früh aufzustehen, die antwort kann eigentlich nur ja sein.....

Ich glaube, ich habe hier schonmal gelesen, dass Du und Vinzenz sich hier in der Vorgehensweise unterscheiden

viele wege führen nach rom. es ist auch immer eine frage der persönlichen vorgehensweise. was joins betrifft, so hat es sich aus meinen augen einfach bewährt, sie kritisch anzusehen. einfach um immer die richtige anzahl an datensätze zurück bekommen, ganz unabhängig davon, was ich an spalten ausgeben will.

Ich habe fast den Eindruck, dass hierfür mehr Datenbankerfahrung nötig ist, um den entsprechenden Instinkt für Fehleranfälligkeiten zu entwickeln.

klar, erfahrung ist im jeden bereich notwendig. aber man muss sich auch nicht alles selbst erarbeiten, sondern kann es sich zu gute machen, das andere schon bestimmte wege gegangen sind.

Ist wahr? Dann werde ich künftig gerne Abfragen (die ich suche) in Worte fassen, denn das fällt mir mitunter leichter, als eine erste Abfrage als Beweis von Eigenbemühungen zu verfassen ;-)

nie vergessen beispieldaten mitzuliefern...

Ja, da gebe ich Dir recht. Probelm scheint aber zu sein, dass ich gar

nicht mitkriege, ob die Abfrage knallt. Oder bist Du da anderer meinung?

doch, das wirst du, den dann wird das update mit einer fehlmerldung abgebrochen.

Ilja