Eine SQL-Anweisung

[10085998]

Hallo Leute,

ich habe folgendes Problem:
Es ist ein Array gegeben: (1, 4, 4568)
Es ist eine Tabelle gegeben namens "artikel" mit den spalten (id, name, nr)

Aufgabe:
Zeige alle Werte des Arrays an, die sich nicht als Wert in der Spalte nr in der Tabelle artikel befinden.

Ich habe die folgende ziemlich umständliche Lösung gefunden:
SELECT test1
FROM (
  SELECT 1 AS test1
  UNION
  SELECT 4 AS test1
  UNION
  SELECT 4568 AS test1
) AS test
LEFT JOIN artikel ON test.test1 = artikel.nr
WHERE IsNull( artikel.id )

Mit dieser SQL-Anweisung funktioniert es zwar, aber bei einem großen Array muss man umständlich jeden Wert des Arrays als Union Select zusammenführen.

Eine andere Möglichkeit wäre noch eine temporäre Tabelle mit einer Spalte zu bauen und dort die Werte des Arrays zu speichern und mit dieser Tabelle dann einen LEFT JOIN auf die Tabelle artikel machen.

Meine Frage ist nun: Kann man mein Problem simpler mit einer einzigen SQL-Anweisung lösen?
Ich dachte an sowas ähnliches wie:
SELECT test1
FROM DUAL WHERE DUAL.TEST1 in (1, 4, 4568)
) AS test
LEFT JOIN artikel ON test.test1 = artikel.nr
WHERE IsNull( artikel.id )

was natürlich nicht funktioniert.
Gibt es ein sql-Anweisung mit der ich die Werte eines Arrays in einer Spalte einer Tabelle darstellen kann?
Als Datenbank steht mysql zur Verfügung.

vielen Dank im voraus

[dedlfix]

Hi!

Zeige alle Werte des Arrays an, die sich nicht als Wert in der Spalte nr in der Tabelle artikel befinden.

... WHERE nr NOT IN (kommaseparierte_arraywerte)

Bei den Array-Werten ist darauf zu achten, dass sie letztlich kontextgerecht im SQL-String landen.

Lo!

[10085998]

Hi!

Zeige alle Werte des Arrays an, die sich nicht als Wert in der Spalte nr in der Tabelle artikel befinden.

... WHERE nr NOT IN (kommaseparierte_arraywerte)

Bei den Array-Werten ist darauf zu achten, dass sie letztlich kontextgerecht im SQL-String landen.

Lo!

Nein das funktioniert nicht!
Mit deiner Methode werden diejenigen Werte der Spalte nr der Tabelle Artikel angezeigt, die nicht als Werte im Array vorkommen.

[dedlfix]

Hi!

Mit deiner Methode werden diejenigen Werte der Spalte nr der Tabelle Artikel angezeigt, die nicht als Werte im Array vorkommen.

Stimmt, andersrum wolltest du es haben. Dann lass das NOT weg (und füge gegebenenfalls noch ein DISTINCT zum SELECT nr hinzu: SELECT DISTINCT nr FROM ...). Erstell ein Array mit den Ergebnissen und nutze Array-Funktionen deiner abfragenden Programmiersprache, um die Differenz zu ermitteln (PHP: array_diff(dein_array, abfrageergebnis_array)).

Wenn die Lösung komplett in SQL gehalten werden soll, so wäre der Lösungsweg nach meinen Überlegungen recht aufwendig.

Lo!

[10085998]

Hi!

Mit deiner Methode werden diejenigen Werte der Spalte nr der Tabelle Artikel angezeigt, die nicht als Werte im Array vorkommen.

Stimmt, andersrum wolltest du es haben. Dann lass das NOT weg (und füge gegebenenfalls noch ein DISTINCT zum SELECT nr hinzu: SELECT DISTINCT nr FROM ...). Erstell ein Array mit den Ergebnissen und nutze Array-Funktionen deiner abfragenden Programmiersprache, um die Differenz zu ermitteln (PHP: array_diff(dein_array, abfrageergebnis_array)).

Wenn die Lösung komplett in SQL gehalten werden soll, so wäre der Lösungsweg nach meinen Überlegungen recht aufwendig.

Lo!

Ja wie im Ausgangs-Post schon erwähnt, soll die Lösung mit einer einzigen SQL-Anweisung gelöst werden (ohne Programmiersprachen wie perl, php usw.).
Ich habe ja schon eine Lösung gefunden, die ich im Eröffunung-Post auch angegeben habe. Leider eine recht umständliche, wenn das Array sehr groß sein sollte.

Der Kern ist folgende Frage:
Gibt es eine sql-Anweisung mit der ich die Werte eines Arrays in einer Spalte einer Tabelle darstellen kann?
Sowas ähnliches wie Select * from (1, 4,4568), was aber auch funktionieren sollte.

viele Grüße

[dedlfix]

Hi!

Gibt es eine sql-Anweisung mit der ich die Werte eines Arrays in einer Spalte einer Tabelle darstellen kann?
Sowas ähnliches wie Select * from (1, 4,4568), was aber auch funktionieren sollte.

So eine Funktion ist mir nicht bekannt (jedenfalls nicht in generellem SQL). Das würde bedeuten, dass das DBMS selbständig eine temporäre Tabelle erzeugen müsste, die es dann befragen kann. Natürlich kannst du dir eine Stored Procedure erstellen, die das kann, wobei mir nicht bekannt ist, dass es die Möglichkeit gäbe, eine variable Anzahl an Parametern zu übergeben. Der Parameter müsste dann ein String sein, den du dir selbst auseinanderpflücken müsstest.

Lo!

[10085998]

Hi!

Gibt es eine sql-Anweisung mit der ich die Werte eines Arrays in einer Spalte einer Tabelle darstellen kann?
Sowas ähnliches wie Select * from (1, 4,4568), was aber auch funktionieren sollte.

So eine Funktion ist mir nicht bekannt (jedenfalls nicht in generellem SQL). Das würde bedeuten, dass das DBMS selbständig eine temporäre Tabelle erzeugen müsste, die es dann befragen kann. Natürlich kannst du dir eine Stored Procedure erstellen, die das kann, wobei mir nicht bekannt ist, dass es die Möglichkeit gäbe, eine variable Anzahl an Parametern zu übergeben. Der Parameter müsste dann ein String sein, den du dir selbst auseinanderpflücken müsstest.

Lo!

Nein, nicht unbedingt. Die DBMS kann sich eigentlich eine selbständig erstellte temporäre Tabellen sparen. So eine sql-Anweisung, ich nenne sie mal ConvertArrayToColumn, müsste sich intern z.B. einfach auflösen in z.B.:

SELECT ConvertArrayToColumn(array(1, 4, 4568)) as test.test1

wird intern in der DBMS umgeschrieben und ausgeführt als z.B.:

SELECT test1
FROM (
  SELECT 1 AS test1
  UNION
  SELECT 4 AS test1
  UNION
  SELECT 4568 AS test1
) as test

Also mir wäre ein solche sql-Anweisung schon einige Male recht hilfreich gewesen. Ich denke, dies wäre schon eine recht praktische Funktion ohne jetzt selbst ein Stored Procedure schreiben zu müssen, welches in Oracle, SQL-Server und in den neueren mysql-Versionen jeweils komplett anders aussieht. Ältere Versionen von mysql kennen keine Stored Procedures.

viele Grüße

[Ilja]

moin,

Als Datenbank steht mysql zur Verfügung.

hmm, die anweiung mit dem dual sah aber oraclisch aus, aber vielleicht ist das ja nur das testsystem für mysql. ;-)

Gibt es ein sql-Anweisung mit der ich die Werte eines Arrays in einer Spalte einer Tabelle darstellen kann?

kann es per definition nicht geben und somit schon im ansatz falsch. SQL kann viel, aber es kann immer nur daten zurück geben, die auch in der datenbank existieren. man kann daten miteinander vergleichen, ausschließen, manipulieren und vieles mehr, aber eben keine daten aus dem hut zaubern, die es nicht in irgendeiner form in der datenbank gibt.

demzufolge bleiben dir nur zwei kunstgriffe, dynamisches sql, zum beispiel das was du mit dem UNION machst (ps UNION ALL verenden) oder aber du baust dir eine Funktion, was ich als die bessere lösung ansehe.

Ilja

[Ilja]

moin,

das thema hat mir keine ruhe gelassen und ich muss mich selbst korregieren. es gibt unter mysql eventuell eine lösung, wenn sie auch unschön ist. ich würde wie gesagt dafür eine eigene funktion verwenden, aber hier mal mein lösungsansatz.

man kann sich eine variable zunutze machen, die man in die abfrage mit einbaut. in ersten ergebnisdatensatz wird die variable mit dem array initialisiert, sagen wird die varable heisst v_ausgabe. ich kenne den mysql dialekt dazu nicht genau, deswegen verwende ich ein wenig speudo code.

SELECT v_ausgabe = '1,4,4568'
FROM dual
;

jetzt kommt die eigentliche tabelle mit rein und der trick ist mit substr zu arbeiten. damit kannst du die werte rausfiltern, die matchen.

SELECT v_ausgabe = '1,4,4568' Ausgabe, 1 Sortierung
FROM dual d
ORDER BY 2
UNION ALL
SELECT v_ausgabe = SUBSTR(v_ausgabe, a.nr, ''), 2
FROM artikel a
WHERE a.nr IN ('1,4,4568')
;

die sortierungsspalte brauchen wir, um sicherzustellen, dass die initialisierung zuerst ausgeführt wird. in der ausgabe musst du noch die trennzeichen und eventuelle leerzeichen behandeln, das lasse ich hier aber mal weg. ich habe leider kein mysql zur hand, kannst ja mal schauen, ob das geht.

Ilja

[dedlfix]

Hi!

[...] hier mal mein lösungsansatz.

Funktioniert aber in der Form nicht.

man kann sich eine variable zunutze machen, die man in die abfrage mit einbaut. in ersten ergebnisdatensatz wird die variable mit dem array initialisiert,

SQL kennt keine Arrays. Ich schlage vor, wir bleiben mal für das Verständnis bei möglichst korrekten Bezeichnungen (zumindest beim Versuch, sie zu verwenden). Es ist nur ein String mit kommaseparierten Zahlen, also ein skalarer Wert / ein einteiliges Datum.

ich kenne den mysql dialekt dazu nicht genau, deswegen verwende ich ein wenig speudo code.

Kein Problem, ich übersetzt das mal, soweit ich deine Intention verstehe.

SELECT v_ausgabe = '1,4,4568'
FROM dual

Die Pseudo-Tabelle dual gibt es (immer noch) nicht. In dem Fall einfach die FROM-Klausel weglassen (plus angepasste Variablenschreibweise).

SELECT @ausgabe := '1,4,4568'

Zusatzfrage: Wie unterscheidet Oracle einen Vergleich (=) von einer Zuweisung (ebenfalls =) und einen Variablennamen von einem Spaltennamen?

SELECT v_ausgabe = '1,4,4568' Ausgabe, 1 Sortierung
FROM dual d
ORDER BY 2

Da nun ein Union folgt, muss dieses SELECT-Statement mit dem ORDER BY drin geklammert werden, ansonsten darf das ORDER BY nur am Ende stehen und wirkt auf die unionisierte Ergebnismenge.

(SELECT @v_ausgabe := '1,4,4568' Ausgabe, 1 Sortierung
ORDER BY 2)

UNION ALL
SELECT v_ausgabe = SUBSTR(v_ausgabe, a.nr, ''), 2
FROM artikel a
WHERE a.nr IN ('1,4,4568')
;

Damit ergibt sich:

(SELECT @v_ausgabe := '1,4,4568' Ausgabe, 1 Sortierung
ORDER BY 2)
UNION ALL
(SELECT @v_ausgabe := SUBSTR(@v_ausgabe, a.nr, ''), 2
FROM artikel a
WHERE a.nr IN ('1,4,4568'))

Sollte das in dem zweiten Statement ebenfalls eine Zuweisung sein oder doch ein Vergleich? Soll der Wert für den IN-Operator ebenfalls ein einzelner String sein? Vermutlich nicht.

jetzt kommt die eigentliche tabelle mit rein und der trick ist mit substr zu arbeiten. damit kannst du die werte rausfiltern, die matchen.

SUBSTR() arbeitet unter MySQL mit einem String als erstem Argument und einer Positionsangabe als zweitem Argument, plus einem optionalen dritten Argument für eine Längenangabe. Außer dem ersten Argument sind die anderen nummerische Argumente. Die in a.nr enthaltene Zahl ist als Positionsangabe nicht verwendbar, das Leerzeichen als Länge auch komisch. Was also meinst du mit dieser Funktion, beziehungsweise wie arbeitet sie in dem von dir verwendeten Dialekt wirklich? (Ich konnte keinen gravierenden Unterschied zu MySQL feststellen.

Das Ergebnis ist jedenfalls:

+----------+------------+
| Ausgabe  | Sortierung |
+----------+------------+
| 1,4,4568 |          1 |
+----------+------------+

Lo!

[Ilja]

yo,

SQL kennt keine Arrays.

war auch nie die rede davon, aber das array kommt von aussen. muss natürlich vorher entsprechend angepasst werden.

Die Pseudo-Tabelle dual gibt es (immer noch) nicht.

muss ich mir mal merken, bin so an das überflüssige dual gewöhnt, nennt man wohl konditionierung.

Zusatzfrage: Wie unterscheidet Oracle einen Vergleich (=) von einer Zuweisung (ebenfalls =) und einen Variablennamen von einem Spaltennamen?

in  pl/sql und in sqlplus mit dem ":=". und wenn ich deine zweite frage angeht, so wird die variable in sqlplus mit einem : aufgeführt, wenn ich mich nicht ganz täusche.

Da nun ein Union folgt, muss dieses SELECT-Statement mit dem ORDER BY drin geklammert werden, ansonsten darf das ORDER BY nur am Ende stehen und wirkt auf die unionisierte Ergebnismenge.

ja die soll auch am ende hin, die sortierung betrifft den gesamten union.

SUBSTR() arbeitet unter MySQL mit einem String als erstem Argument und einer Positionsangabe als zweitem Argument, plus einem optionalen dritten Argument für eine Längenangabe.

ich bin überarbeitet, klassisches bespiel an etwas gedacht und was anderes gesagt. die parameter stimmen schon, nur die variable is REPLACE. und in der zweiten anweisung ist es auch ein einzelener string.

SELECT @v_ausgabe := '1,4,4568' Ausgabe, 1 Sortierung
UNION ALL
SELECT @v_ausgabe := REPLACE(@v_ausgabe, a.nr, ''), 2
FROM artikel a
WHERE a.nr IN ('1,4,4568'))
ORDER BY 2

Ilja

[dedlfix]

Hi!

ich bin überarbeitet, klassisches bespiel an etwas gedacht und was anderes gesagt. die parameter stimmen schon, nur die variable is REPLACE. und in der zweiten anweisung ist es auch ein einzelener string.

SELECT @v_ausgabe := '1,4,4568' Ausgabe, 1 Sortierung
UNION ALL
SELECT @v_ausgabe := REPLACE(@v_ausgabe, a.nr, ''), 2
FROM artikel a
WHERE a.nr IN ('1,4,4568'))
ORDER BY 2

Die WHERE-Zeile muss so aussehen, also Einzelwerte und kein String. Der String '1,2,4568' wird sonst in die Zahl 1 konvertiert und nur die Zeile a.nr=1 ausgewählt.

WHERE a.x IN (1,4,4568)

Außerdem gibt es ein Problem bei der Stringersetzung. Die Ausgabe ist nun wie folgt

+----------+------------+
| Ausgabe  | Sortierung |
+----------+------------+
| 1,4,4568 |          1 |
| ,4,4568  |          2 |
| ,,568    |          2 |
+----------+------------+

Zu sehen ist, die 4 verschwindet auch an Stellen, wo sie nicht soll, weil nicht die Zahlen im String sondern stets der gesamte String betrachtet wird. Abhilfe schafft, wenn man die Zahlen im Ausgangsstring eindeutig einrahmt. Ich wählte ein Leerzeichen als Anfang und ein Komma als Ende. Die Ersetzung wirft die Zahl und die "Begrenzungszeichen" raus, so arbeitet es nun ohne Fehler, und am Ende bleibt auch keine Kommawüste übrig. Und wenn man dann doch noch zwei Statements drausmacht, braucht man das Hilfskonstrukt der Sortierung nicht mehr.

SET @v_ausgabe := ' 1, 4, 4568,';
SELECT @v_ausgabe := REPLACE(@v_ausgabe, CONCAT(' ', a.nr, ','), '') Ausgabe
FROM artikel a
WHERE a.x IN (1,4,4568)
ORDER BY 1 DESC
LIMIT 1

Man muss nun aber entweder zwei Statements absetzen oder ein Multiquery bemühen (oder doch die Sortierung drin lassen). Auf alle Fälle bekommt man als Ergebnis einen String (wenn man noch ORDER und LIMIT hinzufügt ist es auch nur einer). Mit dem hat man vermutlich nicht viel gewonnen, denn er eignet sich nicht besonders als Kriterium für weitere Abfragen. Man könnte weitere Datensätze mit einer Im-String-enthalten-Prüfung selektieren (ebenfalls mit dem "Begrenzungszeichen-Trick"), doch performant ist was anderes. Es muss nämlich diese Prüfung auf jeden Datensatz einzeln angewendet werden, genauso wie es bei der ersten Berechnung der Fall war. Ein Index als Beschleuniger ist nicht verwendbar.

Ich denke, es ist günstiger (und vor allem verständlicher), die Abfragen zu trennen und etwas Hilfe der abfragenden Programmiersprache zu verwenden, die in Stringverarbeitung leistungsfähiger ist als ein DBMS.

Lo!

[Ilja]

yo,

Außerdem gibt es ein Problem bei der Stringersetzung.

stimmt, da muss man eben noch ein wenig nachbessern.

Auf alle Fälle bekommt man als Ergebnis einen String (wenn man noch ORDER und LIMIT hinzufügt ist es auch nur einer).

braucht LIMIT bei mysql zwingend das ORDER BY und wann wird das Limit ausgeführt ? wenn es am ende ausgeführt wird, dann könnte man das ORDER BY auch ganz weglassen.

Ein Index als Beschleuniger ist nicht verwendbar.

ich müsste es jetzt selbst nachprüfen, ob ein index auf die nr spalte nicht doch vorteile bringt.

Ich denke, es ist günstiger (und vor allem verständlicher), die Abfragen zu trennen und etwas Hilfe der abfragenden Programmiersprache zu verwenden, die in Stringverarbeitung leistungsfähiger ist als ein DBMS.

ich denke auch, es ist nicht immer sinnvoll alles in eine abfrage zu bekommen. vielleicht war es der sportliche ergeiz dabei, der zählt. aber die idee ist interessant, die werte aus dem join zu holen uns diese dann mit in den REPLACE mit einzubauen, um werte aus dem string heraus zu bekommen.

Ilja

[Ilja]

yo,

braucht LIMIT bei mysql zwingend das ORDER BY und wann wird das Limit ausgeführt ? wenn es am ende ausgeführt wird, dann könnte man das ORDER BY auch ganz weglassen.

ist albern, man braucht das order by.

Ilja