MySQL - 5.0.51a sowas wie .... WHERE name != 'DEF' bei großer Tabelle

[xrax]

Hallo zusammen,

ich habe folgende Tabelle die real > 5 Mio. Zeilen hat.

MyTab:

Nr name
1 ABC
2 ABC
3 ABC
4 ABC
2 DEF
5 DEF
3 GHI
5 GHI

Mit

SELECT * FROM MyTab ORDER BY Nummer

erhalte erwartungsgemäß

Nr name
1 ABC
2 ABC
2 DEF
3 ABC
3 GHI
4 ABC
5 DEF
5 GHI

Ich möchte aber die Einträge mit den Nummern die mit DEF ein Tupel bilden nicht erhalten. Nicht nur die nicht, welche name=DEF sind.

Also als ergebniss:

Nr name
1 ABC
3 ABC
3 GHI

2|ABC und 5|GHI nicht, wegen 2|DEF und 5|DEF

Wie sieht hierfür die Query aus?

Besten Dank
xrax

 

[CPoly]

Bevor wir mit Lösungsansätzen anfangen, muss ich nochmal Klarheit schaffen. Müsste das Ergebnis nach deiner Erklärung nicht folgendes sein?
Nr name
1 ABC
3 GHI

Denn vor 3|ABC steht ja 2|DEF

Du möchtest keine Ergebnisse, die an ein "DEF" grenzen?

Edit:
Und noch was:
Haben die Spalten auch einen Primärschlüssel den man zu Rate ziehen kann?

 

[Yaslaw]

Auf die schnelle und ungetestet

SELECT
	nr, name
FROM
	mytable
WHERE nr NOT IN (
		SELECT DISTINCT nr 
		FROM mytable 
		WHERE name = 'DEF')

Du solltest einen Index auf nr haben. Ev auch einen auf nr & name

 

[xrax]

Danke CPoly,
beim Ergebniss habe ich 4|ABC vergessen.
Die Lösung von yaslaw macht was ich brauche...

 

[xrax]

Besten Dank yaslaw,

das wars was ich brauchte.

Jetzt gehts an den Praxistest. Mal schauen wie lange die Abfragen dauern....

Über nr und name ist der PK, drumm kann ich DISTINCT weglassen

Gruß
xrax

 

[Yaslaw]

Über nr und name ist der PK, drumm kann ich DISTINCT weglassen

Solange du nur ein Name ausklammerst. Sind es aber mehrere Namen, kann die nr in der Unterabfrage wieder mehrfach vorkommen...

 

[CPoly]

Jetzt versteh ich erst, was genau du meintest
Aber yaslaw hats ja bereits gelöst.

 

[xrax]

Ich habe das nochmals als Thema

"Sätze mit gleichen Werten finden und andere Ausschliessen"

geöffnet und dort erklärt was ich brauch.


Hallo zusammen, hallo yaslaw

mittlerweile läuft die Applikation und die Tabelle hat 600.000 Einträge.

Auf die schnelle und ungetestet

.....SELECT DISTINCT nr 
		FROM mytable 
		WHERE name = 'DEF')....

Diese WHERE wird aber immer länger.

..WHERE name='DEF' OR name='AAA' OR name='ABC'.........

Die Abfrage dauert bereits jetzt > 5 Sek. und die Tabelle wird auf mehrere Millionen wachsen.

Ich hab jetzt was gebastelt, aber das Ergebnis stimmt leider nicht ganz. Dafür ist es viel schneller

SELECT tab1.nr, tab1.name
FROM mytable AS tab1
LEFT JOIN mytable AS tab2 ON tab2.nr = tab1.nr
AND tab2.name 
IN (
'DEF', 'AAA', 'AXY'
)
WHERE tab1.name = 'ABC'
AND tab2.nr IS NULL

Das bringt mir nur die Datensätze von 'ABC' und nicht alle die gemeinsame nr mit ABC haben.

Hat jemand hierzu eine Idee wie ich eine flotte Query hinbekomme?

Besten Dank
xrax

 

[Steusi]

Du könntest REGEXP nehmen, um deine bestimmten Einträge zu ermitteln.
Noch einfacher ist es, wenn du eine eine kommaseparierte Liste ein SET darstellt.
FIND_IN_SET() http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set

WHERE FIND_IN_SET()

 

[Yaslaw]

item: Die 2te Verknüpfung im ON-Teil des JOINS ist keine Verknüpfung sondern gehört in ein SQL
item: Wozu hast du im WEHRE das folgende? Was soll das bewirken? tab1.name = 'ABC'

Ich denke dass es etwa so aussehen könnte

SELECT 
    tab1.nr, 
    tab1.name
FROM 
    mytable AS tab1
    LEFT JOIN (
                    SELECT nr 
                    FROM mytable 
                    WHERE name IN('DEF', 'AAA', 'AXY')
                 ) AS tab2 
        ON tab2.nr = tab1.nr
WHERE
    tab2.nr IS NULL

@Steusi
Ich denke, FIN_IN_SET ist langsamer als IN(). Kann mich aber auch irren