9
Hilfe bei SQL Abfrage
Habe einfach eine Denkblockade und würde mich über Hilfe bei einer Datenbankabfrage freuen.
Die Abfrage lautet wie folgt:
SELECT SUM(preis) AS gesamt FROM positionen INNER JOIN kaeufe ON positionen.pid = kaeufe.pid INNER JOIN kunden on kaeufe.kid = kunden.kid INNER JOIN bills on kunden.kid = bills.kid WHERE kaeufe.datum > (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '2') AND '2' = bills.kid AND '2' = kaeufe.kid;
Sinn soll es sein, die Gesamtsumme von Posten zu bekommen, welche seit der letzten Rechnung angefallen sind.
Tabellen:
bills (Rechnungen)
kaeufe (getätigte Käufe)
kunden (Kunden)
positionen (Artikel)
Verknüpfung:
bills.kid <-> kunden.kid <-> kaeufe.kid, kaeufe.pid <-> positionen.pid
Derzeit erhalte ich aber als Abfrage in einem Beispiel folgendes:
21€
da stehen sollten aber 10,50€
Tabelle kaeufe:
kaid kid pid datum
1 2 1 2015-05-12 13:10:46
2 2 2 2015-05-12 13:31:05
3 2 1 2015-05-12 14:56:34
4 2 3 2015-05-12 14:56:43
Tabelle bills:
bid kid gesamt datum status
1 2 0 2015-05-11 00:00:00 Account erstellt
4 2 1.5 2015-05-12 14:33:42 bezahlt
5 4 0 2015-05-12 14:56:17 Account erstellt
Tabelle positionen:
pid bezeichnung inhalt preis
1 Mixery 0,5l 0.5
2 Cola 1,5l 1
3 Kiste Bier 10l 10
Die Abfrage lautet wie folgt:
SELECT SUM(preis) AS gesamt FROM positionen INNER JOIN kaeufe ON positionen.pid = kaeufe.pid INNER JOIN kunden on kaeufe.kid = kunden.kid INNER JOIN bills on kunden.kid = bills.kid WHERE kaeufe.datum > (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '2') AND '2' = bills.kid AND '2' = kaeufe.kid;
Sinn soll es sein, die Gesamtsumme von Posten zu bekommen, welche seit der letzten Rechnung angefallen sind.
Tabellen:
bills (Rechnungen)
kaeufe (getätigte Käufe)
kunden (Kunden)
positionen (Artikel)
Verknüpfung:
bills.kid <-> kunden.kid <-> kaeufe.kid, kaeufe.pid <-> positionen.pid
Derzeit erhalte ich aber als Abfrage in einem Beispiel folgendes:
21€
da stehen sollten aber 10,50€
Tabelle kaeufe:
kaid kid pid datum
1 2 1 2015-05-12 13:10:46
2 2 2 2015-05-12 13:31:05
3 2 1 2015-05-12 14:56:34
4 2 3 2015-05-12 14:56:43
Tabelle bills:
bid kid gesamt datum status
1 2 0 2015-05-11 00:00:00 Account erstellt
4 2 1.5 2015-05-12 14:33:42 bezahlt
5 4 0 2015-05-12 14:56:17 Account erstellt
Tabelle positionen:
pid bezeichnung inhalt preis
1 Mixery 0,5l 0.5
2 Cola 1,5l 1
3 Kiste Bier 10l 10
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 272214
Url: https://administrator.de/contentid/272214
Ausgedruckt am: 05.11.2024 um 23:11 Uhr
9 Kommentare
Neuester Kommentar
Kannst du auch noch die betroffenden Datensätze aus "positionen" posten? Daraus ergibt sich ja deine Summe, ohne die kann ich nur raten. Alle Datensätze mit id 1 bis 3.
Habe diese noch eingefügt
//EDIT
Ein SELECT SUM(DISTINCT preis)... ist leider auch keine Lösung. Sobald 2 gleiche Posten "gekauft" wurden, werden diese als doppelte Datensätze gefiltert
//EDIT
Ein SELECT SUM(DISTINCT preis)... ist leider auch keine Lösung. Sobald 2 gleiche Posten "gekauft" wurden, werden diese als doppelte Datensätze gefiltert
Dein Problem liegt auf jedenfall in der bills Tabelle. Dort kann es scheinbar pro Rechnung mehrere Datensätze geben mit verschiedenen Status und Datum-/Zeitangaben. Das ist nicht zwingend falsch wenn auch vieleicht etwas unglücklich aufgebaut. Auf diese zwei unterschiedlichen Status joinst du dann in beiden Fällen alle Positionen, hast also alle doppelt.
Das kann man mit bills.[status] = 'bezahlt' lösen aber ob das sinnvoll ist kann man anhand der drei Datensätze nicht sagen.
Hier mein Testcode:
WITH kaeufe(kaid,kid,pid,datum) AS (
SELECT 1,2,1,'2015-05-12 13:10:46.000'
UNION ALL
SELECT 2,2,2,'2015-05-12 13:31:05.000'
UNION ALL
SELECT 3,2,1,'2015-05-12 14:56:34.000'
UNION ALL
SELECT 4,2,3,'2015-05-12 14:56:43.000'
), bills(bid,kid,gesamt,datum,[status]) AS (
SELECT 1,2,0,'2015-05-11 00:00:00','Account erstellt'
UNION ALL
SELECT 4,2,1.5,'2015-05-12 14:33:42','bezahlt'
UNION ALL
SELECT 5,4,0,'2015-05-12 14:56:17','Account erstellt'
), positionen(pid,bezeichnung,preis) AS (
SELECT 1,'Mixery 0,5l',0.5
UNION ALL
SELECT 2,'Cola 1,5l',1
UNION ALL
SELECT 3,'Kiste Bier 10l',10
)
SELECT sum(preis) AS gesamt
FROM positionen
INNER JOIN kaeufe
ON positionen.pid = kaeufe.pid
--INNER JOIN kunden
--ON kaeufe.kid = kunden.kid
INNER JOIN bills
ON kaeufe.kid = bills.kid
--AND bills.[status] = 'bezahlt' --die Bedingung löst das Problem zumindest für die Testdaten
--ON kunden.kid = bills.kid
WHERE kaeufe.datum > ( SELECT max(bills.datum)
FROM bills
WHERE bills.kid = '2')
AND '2' = bills.kid
--AND '2' = kaeufe.kid --unnötig, da bills.kid = kaeufe.kid
Das kann man mit bills.[status] = 'bezahlt' lösen aber ob das sinnvoll ist kann man anhand der drei Datensätze nicht sagen.
Hier mein Testcode:
WITH kaeufe(kaid,kid,pid,datum) AS (
SELECT 1,2,1,'2015-05-12 13:10:46.000'
UNION ALL
SELECT 2,2,2,'2015-05-12 13:31:05.000'
UNION ALL
SELECT 3,2,1,'2015-05-12 14:56:34.000'
UNION ALL
SELECT 4,2,3,'2015-05-12 14:56:43.000'
), bills(bid,kid,gesamt,datum,[status]) AS (
SELECT 1,2,0,'2015-05-11 00:00:00','Account erstellt'
UNION ALL
SELECT 4,2,1.5,'2015-05-12 14:33:42','bezahlt'
UNION ALL
SELECT 5,4,0,'2015-05-12 14:56:17','Account erstellt'
), positionen(pid,bezeichnung,preis) AS (
SELECT 1,'Mixery 0,5l',0.5
UNION ALL
SELECT 2,'Cola 1,5l',1
UNION ALL
SELECT 3,'Kiste Bier 10l',10
)
SELECT sum(preis) AS gesamt
FROM positionen
INNER JOIN kaeufe
ON positionen.pid = kaeufe.pid
--INNER JOIN kunden
--ON kaeufe.kid = kunden.kid
INNER JOIN bills
ON kaeufe.kid = bills.kid
--AND bills.[status] = 'bezahlt' --die Bedingung löst das Problem zumindest für die Testdaten
--ON kunden.kid = bills.kid
WHERE kaeufe.datum > ( SELECT max(bills.datum)
FROM bills
WHERE bills.kid = '2')
AND '2' = bills.kid
--AND '2' = kaeufe.kid --unnötig, da bills.kid = kaeufe.kid
Das AND bills.status = 'bezahlt' soll sicherlich nach dem WHERE oder?
SELECT SUM(preis) AS gesamt FROM positionen INNER JOIN kaeufe ON positionen.pid = kaeufe.pid INNER JOIN kunden on kaeufe.kid = kunden.kid INNER JOIN bills on kunden.kid = bills.kid WHERE kaeufe.datum > (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '2') AND '2' = bills.kid AND bills.status = 'bezahlt'
Das funktioniert leider auch nicht nicht wirklich. Im ersten Augenblick ja, aber nach dem weitere Rechnungen in bills dazu kommen, kommt da wieder etwas durch einander.
Vielleicht hilft folgender Prozess:
Nutzer legen Käufe an. Bei Bedarf können diese auf "Bezahlen" klicken. Es wird eine Rechnung angelegt mit Status "offen". Der Status "Account erstellt" wird bei Nutzerregistrierung eingetragen als Vergleichsdatum (hätte man sicherlich auch bei kunden mit einfügen können). Anschließend kann ein Administrator prüfen ob die Rechnung bezahlt wurde und diese dann auf Status "bezahlt" setzen.
Die jetzige Abfrage soll halt alle Käufe Summieren, welche seit der letzten bezahlten bzw. offenen Rechnung wieder angelaufen sind.
SELECT SUM(preis) AS gesamt FROM positionen INNER JOIN kaeufe ON positionen.pid = kaeufe.pid INNER JOIN kunden on kaeufe.kid = kunden.kid INNER JOIN bills on kunden.kid = bills.kid WHERE kaeufe.datum > (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '2') AND '2' = bills.kid AND bills.status = 'bezahlt'
Das funktioniert leider auch nicht nicht wirklich. Im ersten Augenblick ja, aber nach dem weitere Rechnungen in bills dazu kommen, kommt da wieder etwas durch einander.
Vielleicht hilft folgender Prozess:
Nutzer legen Käufe an. Bei Bedarf können diese auf "Bezahlen" klicken. Es wird eine Rechnung angelegt mit Status "offen". Der Status "Account erstellt" wird bei Nutzerregistrierung eingetragen als Vergleichsdatum (hätte man sicherlich auch bei kunden mit einfügen können). Anschließend kann ein Administrator prüfen ob die Rechnung bezahlt wurde und diese dann auf Status "bezahlt" setzen.
Die jetzige Abfrage soll halt alle Käufe Summieren, welche seit der letzten bezahlten bzw. offenen Rechnung wieder angelaufen sind.
Moin e51bomag,
du hast zu viele JOINS.
Durch die Joins über die "kid" bekommst du in deinen Beispieldaten 4 Datenzeilen im Resultset statt 2.
Hinreichend wäre:
Ich habe die überflüssigen Joins auskommentiert im Statement stehen lassen - falls dein SQL-Query-Tool das nicht ab kann, musst du alle Zeilen, die mit "-- " beginnen mal kurz entfernen.
Du kannst mal die erste Zeile ändern von "SELECT sum(bla) from Positionen" auf "SELECT * from positionen".
Dann wird reletiv deutlich, warum bei dem Join-Konstrukt mit 2xkid=2 in der kaeufe-Tabelle mal 2xkid=2 in der bills-Tabelle im Ergebnis 2x2=4 Datenzeilen zurückkommen.
Grüße
Biber
du hast zu viele JOINS.
Durch die Joins über die "kid" bekommst du in deinen Beispieldaten 4 Datenzeilen im Resultset statt 2.
Hinreichend wäre:
select sum(preis) as gesamt
FROM positionen
INNER JOIN kaeufe
ON positionen.pid = kaeufe.pid
-- INNER JOIN kunden
-- ON kaeufe.kid = kunden.kid
-- INNER JOIN bills
-- ON kunden.kid = bills.kid
WHERE kaeufe.datum > (SELECT max(bills.datum)
FROM bills
WHERE bills.kid = '2')
-- AND '2' = bills.kid
AND '2' = kaeufe.kid; Ich habe die überflüssigen Joins auskommentiert im Statement stehen lassen - falls dein SQL-Query-Tool das nicht ab kann, musst du alle Zeilen, die mit "-- " beginnen mal kurz entfernen.
Du kannst mal die erste Zeile ändern von "SELECT sum(bla) from Positionen" auf "SELECT * from positionen".
Dann wird reletiv deutlich, warum bei dem Join-Konstrukt mit 2xkid=2 in der kaeufe-Tabelle mal 2xkid=2 in der bills-Tabelle im Ergebnis 2x2=4 Datenzeilen zurückkommen.
Grüße
Biber
Werde ich auch noch testen, ansonsten hätte ich wohl jetzt eine Lösung durch den Hinweis zur "bills" Tabelle.
SELECT SUM(preis) AS gesamt FROM positionen INNER JOIN kaeufe ON positionen.pid = kaeufe.pid INNER JOIN kunden on kaeufe.kid = kunden.kid INNER JOIN bills on kunden.kid = bills.kid where kaeufe.datum > bills.datum AND kunden.kid = bills.kid AND bills.datum = (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '$kid')
Durch bills.datum = (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '$kid') schaut er sich nur die entscheidende letzte Rechnung in bills an
//EDIT
Danke an euch beide, im Endeffekt funktioniert die letzte Lösung wohl am elegantesten.
SELECT SUM(preis) AS gesamt FROM positionen INNER JOIN kaeufe ON positionen.pid = kaeufe.pid WHERE kaeufe.datum > (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '$kid') AND '$kid' = kaeufe.kid
SELECT SUM(preis) AS gesamt FROM positionen INNER JOIN kaeufe ON positionen.pid = kaeufe.pid INNER JOIN kunden on kaeufe.kid = kunden.kid INNER JOIN bills on kunden.kid = bills.kid where kaeufe.datum > bills.datum AND kunden.kid = bills.kid AND bills.datum = (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '$kid')
Durch bills.datum = (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '$kid') schaut er sich nur die entscheidende letzte Rechnung in bills an
//EDIT
Danke an euch beide, im Endeffekt funktioniert die letzte Lösung wohl am elegantesten.
SELECT SUM(preis) AS gesamt FROM positionen INNER JOIN kaeufe ON positionen.pid = kaeufe.pid WHERE kaeufe.datum > (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '$kid') AND '$kid' = kaeufe.kid
bills.[status] = '' kann auch im Join stehen, nur Sinn macht es wohl nicht.
Biber hat recht, die Joins scheinen mir auch überflüssig solange keine weiteren WHERE Bedingungen vorliegen oder Daten aus den Tabellen begraucht werden.
Biber hat recht, die Joins scheinen mir auch überflüssig solange keine weiteren WHERE Bedingungen vorliegen oder Daten aus den Tabellen begraucht werden.
Moin e51bomag,
zu deinem ersten SELECT im Kommentar von 11:50h:
Das geht total in die Grütze, wenn per Zufall bei
... meinetwegen als das "maximale bills-Datum für Kunde 2 = der 1.5.2015 14:00 ist und wie es der Zufall will noch zwei weitere Kunden genau zu diesem Datum gezahlt haben.
Denn du bekommst ALLE bills-Datensätze mit 1.5.2015 14:00h, auch die vom Kunden 47 und vom Kunden 11 ,
Grüße
Biber
zu deinem ersten SELECT im Kommentar von 11:50h:
Das geht total in die Grütze, wenn per Zufall bei
.. bills.datum = (SELECT MAX(bills.datum) FROM bills WHERE bills.kid = '$kid') Denn du bekommst ALLE bills-Datensätze mit 1.5.2015 14:00h, auch die vom Kunden 47 und vom Kunden 11 ,
Grüße
Biber
1
Bezüglich der vielen Joins, mir war irgendwie so als müsste ich durch die Tabellen joinen (also durch alle)...eindeutig zu lange raus aus der Materie ;)
