[MySQL] Von/Bis Liste

[RedWraith]

Hallo !

Ich versuche im Moment Daten aus einer MySQL Lagerverwaltung auszulesen.
Wir haben bei uns nummerierte Regale, die nummerierte Fächer haben und es wird erfasst,
wieviel von welchen Artikel in welchem Fach auf welchem Regal liegt.
Es kann immer nur ein Artikel in einem Fach auf einem Regal liegen.

Ich habe folgende Tabelle:

Felder:
Regal -> Nummer des Regals auf dem der Artikel liegt
Fach -> Nummer des Fachs in dem der Artikel liegt
Artikel -> Nummer des Artikels
Anzahl -> Die Anzahl pro Fach und Position und Regal

Primary Keys sind Regal und Fach

Regal	Fach	Artikel	Anzahl
--------------------------------------
1	1	1001	10
1	2	1001	12
1	3	1001	5
1	4	1004	10
1	5	1004	14
1	6	1001	2
1	7	1001	3
2	1	1001	6
2	2	1001	1
2	3	1003	15

Und ich möchte diese Daten jetzt abfragen. Das funktioniert auch wunderbar, ich habe nur Probleme mit der Formatierung. Und zwar möchte ich gerne eine Art Von/Bis Liste.

Regal	Artikel	VonFach	BisFach	Anzahl
---------------------------------------------------------
1	1001	1	3	17
1	1004	4	5	24
1	1001	6	7	5
2	1001	1	2	7
2	1003	3	3	15

Das heißt, von Fach 1 bis Fach 3 liegt der Artikel 1001 mit 17 Stück. Von Fach 4 bis Fach 5 liegt 24 mal Artikel 1004. und von Fach 6 bis 7 liegt wieder 5 mal Artikel 1001.

Und genau dieser Von/Bis Teil ist der, an dem ich festhänge. Ich habe im Moment das hier fabriziert:

Select Regal, Artikel, min(Fach) as VonFach, max(Fach) as BisFach, sum(Anzahl) as Anzahl 
From Tabelle 
group by Regal, Artikel

Das Problem daran ist, dass ich so die Gesamtanzahl eines Artikels sowie das erste Fach und das allerletzte Fach ermittle. Das Problem dabei ist, dass nicht berücksichtig wird, wenn Fächer dazwischen anders belegt sind.

Also wenn Fach 1,2 und 4 mit 1001 belegt und Fach 3 mit 1004 belegt ist, dann bekomme ich angezeigt, dass Fach 1 bis 4 mit 1001 belegt sind, was allerdings falsch ist.

Kann mir da jemand einen Schubs geben ?

 

[Yaslaw]

Und was sollte es anzeigen?
Dass 1001 von Fach 1 bis 2 ist und in einer neuen Zeile dass 1001 in Fach 4 bis 4 ist?

 

[RedWraith]

Ja, genau.

Artikel	VonFach	BisFach
1001	1	2
1004	3	3
1001	4	4

Tut mir leid, wenn ich mich da unterständlich ausgedrückt habe.

 

[Yaslaw]

So etwas könnte gehen

Select 
	Regal, 
	Artikel, 
	min(Fach) as VonFach, 
	max(Fach) as BisFach, 
	sum(Anzahl) as Anzahl 
FROM
	(
		SELECT
			@gruppe := @gruppe + IF(@l_regal = dat1.Regal AND @l_artikel = dat1.Artikel, 0, 1) AS gruppe,
			@l_regal := dat1.Regal AS Regal,
			dat1.Fach AS Fach,
			@l_artikel := dat1.Artikel AS Artikel,
			dat1.Anzahl	
		FROM
			(SELECT @l_Regal := 0, @l_artikel := 0, @gruppe := 0) AS vars,
			(SELECT * FROM Tabelle  ORDER BY Regal, Fach) AS dat1
	) AS dat2
GROUP BY
	gruppe

 

[RedWraith]

Nur dem Verständnis wegen:

Du definiert einen Satz Variablen und initialisierst sie alle mit 0.

(SELECT @l_Regal := 0, @l_artikel := 0, @gruppe := 0) AS vars

Dann rufst du die Quelldatenbank auf, und zwar nach Regal und Fach sortiert.
Und zwar damit die Datensätze, die nachher für das Endergebnis gruppiert werden müssen, hintereinander stehen.

SELECT * FROM Tabelle  ORDER BY Regal, Fach) AS dat1

Als Nächstes rufst du alle Datensätze aus den beiden Tabellen ab. Du speicherst das zuletzt verwendete Regel und Artikel ab, damit du prüfen kannst, ob das nächste Element in dieselbe Gruppe gehört, wie das davor. Ob etwas zusammen gruppiert wird bestimmt letztendlich die @l_gruppe Variable, weil diese durch das if Statement inkrementiert wird, wenn der jetzige Datensatz nicht in dieselbe Gruppe gehört wie der vorherige. Damit das funktioniert müssen gleichgruppierte Einträge hintereinander vorkommen, was durch die Sortierung im ersten Schritt gegeben ist.

SELECT
			@gruppe := @gruppe + IF(@l_regal = dat1.Regal AND @l_artikel = dat1.Artikel, 0, 1) AS gruppe,
			@l_regal := dat1.Regal AS Regal,
			dat1.Fach AS Fach,
			@l_artikel := dat1.Artikel AS Artikel,
			dat1.Anzahl

Zum Schluss nach der variable @gruppe gruppieren und dann min(), max() und sum() abfragen.

	Regal, 
	Artikel, 
	min(Fach) AS VonFach, 
	max(Fach) AS BisFach, 
	sum(Anzahl) AS Anzahl

Wow, eine elegante Lösung. Ich wusste bis eben garnicht, dass sowas möglich ist. Danke !

 

[Yaslaw]

Gratuliere, fast korrekte Analyse meines SQLs.
Ausser 2 kleinen Fehlern (Wahrscheinlich Flüchtigkeitsfehlern beim schreiben). Ich gehe aber auf sie ein, falls jemand anderes dies Liest damit deswegen keine Fragen aufkommen.

1) das Fach speichere ich nicht, da es nicht zur Gruppierung verwendet wird
2) die Variable in meinem Bsp heisst nicht @l_guppe, sondern nur @gruppe. Der Präfix l_ steht für Last/Letzte.

Nachtrag:
Anstelle des IF() kann man auch direkt mit den Vergleichswerten arbeiten, da MySQL Vergleiche mit 0 oder 1 als Rückgabewert handelt

--alte Zeile
@gruppe := @gruppe + IF(@l_regal = dat1.Regal AND @l_artikel = dat1.Artikel, 0, 1) AS gruppe,
-- entspricht auch dem
@gruppe := @gruppe + (NOT (@l_regal = dat1.Regal AND @l_artikel = dat1.Artikel)) AS gruppe,