Fortlaufende Nummerierung erkennen

Yaslaw · 1. April 2014

Du hast führende 0en. Ergo ist es ein String.
Besser du gehst sogar auf nummer sicher und schreibst

SQL:

 WHERE CAST(t.id AS UNSIGNED) > 42997

mermshaus · 2. April 2014

Am Rande: Die mysql-Extension ist veraltet. Nutze mysqli oder PDO.

- http://php-de.github.io/jumpto/faq/#deprecated-mysql (Scrollt leider möglicherweise nicht automatisch zum richtigen Eintrag. Aktuell ist es der letzte Eintrag auf der Seite.)

Thomas_Jung · 2. April 2014

@Yaslaw

Ich habe jetzt einige Nummern hinzugefügt.

HTML:

Bei der Abfrage mit folgenden Code

PHP:

SELECT
CASE
WHEN MIN( prep.id ) = MAX( prep.id )
THEN MIN( prep.id )
ELSE CONCAT( MIN( prep.id ) , '-', MAX( prep.id ) )
END AS id_range
FROM (

SELECT -- Letzte Gruppe als GruppenId nehmen
@group_id AS group_id, t.id, -- Nächte Gruppe ermitteln
@group_id :=
CASE
WHEN (
CAST( t.id AS UNSIGNED ) +1
) NOT
IN (

SELECT CAST( id AS UNSIGNED )
FROM test
)
THEN @group_id +1-- Die nexte ID exisitert nicht, ergo Gruppen-ID um eins erhöhen

ELSE @group_id -- Due Gruppen-ID bleibt unverändert

END AS next_group_id
FROM (

SELECT @group_id :=1
)vars, test t
)prep
GROUP BY prep.group_id

ist das Ergebnis

HTML:

900603-900605
900605
27-900604
30
603-605

Muss ich die Nummer erst Sortieren?

Könntest du bitte nochmal über den Code schauen.

Gruß Thomas

Yaslaw · 2. April 2014

Natürlich sollten sie sortiert sein. Am gleichen Ort wo der WHERE drin steht noch ein ORDER BY reinquetschen.

SQL:

-- Also anstelle von 
test t

-- das folgende
(SELECT DISTINCT id FROM test ORDER BY id) t

Nachtrag:
Ich sehe grad, dass du auch doppelte drin hast. Mach noch ein DISTINCT auf die ID. Habs im SQL nachgetragen

Nachtrag 2:
Ich hab mal aus Spass das ganze ein wenig aufgestockt um zu zeigen wie einfach es ist, weitere Informationen anzubinden.
- Der Code ist ein Buchstabe. Wenn es kein Buchstabe ist, ausfiltern
- Codes können mehrfach vorkommen
- Goss/Kleinschreibung im Code soll ignoriert werden: a=A
- Es soll zu jedem Code-Range auch die Titel im Format [title 1], [titel 2], ... angezeigt werden

Siehe http://sqlfiddle.com/#!2/92bec/2

Die Testdaten

SQL:

CREATE TABLE test
	(`id` int, `code` varchar(1), `title` varchar(7))
;
	
INSERT INTO test
	(`id`, `code`, `title`)
VALUES
	(1, 'A', 'Node 1'),
	(2, 'B', 'Node 2'),
	(3, 'd', 'Node 3'),
	(4, 'D', 'Node 4'),
	(5, 'A', 'Node 5'),
	(6, 'H', 'Node 6'),
	(7, 'I', 'Node 7'),
	(8, 'j', 'Node 8'),
	(9, 'H', 'Node 9'),
	(10, 'A', 'Node 10'),
    (11, 9, 'Node 11')  -- Dieser Node soll ausgefiltert werden
;

Das einfache SQL

SQL:

SELECT
	grp.code_range,
	-- Titel zusammenstellen, sortiert nach der id
	GROUP_CONCAT(CONCAT('[', t1.title, ']') ORDER BY t1.id SEPARATOR ', ') AS titles
FROM
	test t1,
	-- Gruppieren über die group_id und den Range zusammensetzen
	(
		SELECT
			group_id,
			CASE 
				WHEN MIN(prep.code) = MAX(prep.code)
				THEN MIN(prep.code)
				ELSE CONCAT(MIN(prep.code), '-', MAX(prep.code))
			END AS code_range,
			GROUP_CONCAT(prep.id_set SEPARATOR ',') AS id_set
		FROM
			-- Jedem Code eine Gruppen-ID zuweisen
			(
				SELECT
					-- Letzte Gruppe als GruppenId nehmen
					@group_id AS group_id,
					t.code,
					t.id_set,
					-- Nächte Gruppe ermitteln
					@group_id := CASE
					WHEN next_code NOT IN (SELECT UPPER(code) FROM test)
						THEN @group_id + 1  -- Die nexte ID exisitert nicht, ergo Gruppen-ID um eins erhöhen
						ELSE @group_id      -- Due Gruppen-ID bleibt unverändert
					END AS next_group_id
				FROM
					-- Die Variable initialisieren
					(SELECT    @group_id := 1) vars,
					-- Die Daten vorbereiten
					(
						SELECT
							UPPER(code) AS code,
							-- Irgend eine Logik, welche den theoretisch nächsten Code ermittelt
							CHAR(ASCII(UPPER(code))+1) AS next_code,
							-- Set mit allen betroffenen IDS erstellen
							GROUP_CONCAT(id SEPARATOR ',') AS id_set 
						FROM      test 
						WHERE     code REGEXP '^[[:alpha:]]$'
						GROUP BY  code
						-- Nach dem Gruppierungs-Code sortieren
						ORDER BY  code	
					) t
			) prep
		GROUP BY
		prep.group_id
	) grp
WHERE     FIND_IN_SET(t1.id, grp.id_set)
GROUP BY  grp.group_id
;

und das Resultat

Code:

CODE_RANGE | TITLES
-----------|----------------------------------------
A-B        | [Node 1], [Node 2], [Node 5], [Node 10]
D          | [Node 3], [Node 4]
H-J        | [Node 6], [Node 7], [Node 8], [Node 9]

Thomas_Jung · 3. April 2014

@Yaslaw

Hi,
der Code macht genau was er soll.

PHP:

SELECT 
CASE
WHEN MIN( prep.id ) = MAX( prep.id )
THEN MIN( prep.id )
ELSE CONCAT( MIN( prep.id ) , '-', MAX( prep.id ) )
END AS id_range
FROM (

SELECT -- Letzte Gruppe als GruppenId nehmen
@group_id AS group_id, t.id, -- Nächte Gruppe ermitteln
@group_id :=
CASE
WHEN (
CAST( t.id AS UNSIGNED ) +1
) NOT
IN (

SELECT CAST( id AS UNSIGNED )
FROM test
)
THEN @group_id +1-- Die nexte ID exisitert nicht, ergo Gruppen-ID um eins erhöhen

ELSE @group_id -- Due Gruppen-ID bleibt unverändert

END AS next_group_id
FROM (

SELECT @group_id :=1
)vars, (

SELECT DISTINCT id
FROM test
ORDER BY id
)t
)prep
GROUP BY prep.group_id

Meine Frage ist.

Wie oder ist es möglich das ich auch weitere Felder(Inhalte) mit anzeigen lassen kann.
Oder muß ich zwei Abfragen erstellen. (Wäre für mich das Einfachste

aber vieleicht kann man es auch in deinen Code Integrieren

)

Die 1 holt mir die id_range.
Die 2 lager, regal, hoehe, fach und meter.

Ich weiß nicht bei welcher SELECT Abfrage ich die zusätzlichen Felder hinzufügen kann.

Ziel ist es:
Suche mir alle id´s die sich im lager 1 im regal 1 in der 2 hoehe im fach 1 befinden.
In dem Fall sind es die id´s 27, 605, 900603, 900604, 900605.

PHP:

$sql = "SELECT lager, regal, hoehe, fach, SUM(meter) as total_meter  FROM `weilhammer_datenbank`  WHERE lager = '1' AND regal = '1'  AND hoehe = '2'  AND fach = '1'  ";


 
$result = mysql_query($sql) or die("<b>ERROR: </b>.\n<br />SQL: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

while($data = mysql_fetch_array($result)) {
echo $data['id_range'].'<br>';
echo $data['lager'].'<br>';
echo $data['regal'].'<br>';
echo $data['hoehe'].'<br>';
echo $data['fach'].'<br>';
echo $data['total_meter'].'<br>';
}

Ergebnis:

Folgende id´s befinden sich im lager 1 im regal 1 in der hoehe 2 im fach 1.
27, 605, 900603 - 900605 die Summe der meter = 7,5.

Code:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(10) DEFAULT NULL,
  `lager` int(3) NOT NULL,
  `regal` int(3) NOT NULL,
  `hoehe` int(3) NOT NULL,
  `fach` int(3) NOT NULL,
  `meter` double(3,1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `test`
--

INSERT INTO `test` (`id`, `lager`, `regal`, `hoehe`, `fach`, `meter`) VALUES
(900603, 1, 1, 1, 1, 5.0),
(900604, 1, 1, 1, 1, 1.0),
(900605, 1, 1, 1, 2, 2.0),
(900605, 6, 6, 6, 6, 0.0),
(900603, 3, 3, 2, 1, 1.0),
(900604, 3, 2, 2, 3, 5.0),
(27, 1, 1, 2, 1, 1.0),
(30, 1, 1, 2, 2, 2.0),
(603, 1, 2, 1, 2, 2.0),
(604, 1, 2, 1, 2, 6.0),
(605, 1, 1, 2, 1, 6.0),
(900605, 1, 1, 2, 1, 0.0),
(900603, 1, 1, 2, 1, 0.5),
(900604, 1, 1, 2, 1, 0.0),
(27, 2, 2, 2, 2, 3.0),
(30, 2, 2, 2, 1, 1.0),
(603, 1, 1, 1, 20, 5.0),
(604, 5, 5, 5, 5, 5.0),
(605, 5, 5, 5, 6, 1.0),
(101, 1, 1, 1, 10, 5.0),
(1001, 1, 1, 1, 1, 0.5),
(11, 3, 3, 3, 3, 3.0);

Gruß Thomas

Yaslaw · 3. April 2014

Ziel ist es:
Suche mir alle id´s die sich im lager 1 im regal 1 in der 2 hoehe im fach 1 befinden.
In dem Fall sind es die id´s 27, 605, 900603, 900604, 900605.

Im Subselect [t] filtern

Folgende id´s befinden sich im lager 1 im regal 1 in der hoehe 2 im fach 1.
27, 605, 900603 - 900605 die Summe der meter = 7,5.

Die Meter ebenfalls im Subselect [t] ausgeben. Dann in jedem GROUIP BY summieren.

Thomas_Jung · 3. April 2014

@Yaslaw

Hi.
ich habe mir mal was zusammengebastelt

.
Er zeigt mir aber nur die afid 1-9 an.

Kannst du sehen wo der Fehler ist.

HTML:

afid_range  lagerort                                         gesamtmeter
1-3    	   [1-2-1-1], [1-2-1-1], [1-2-1-1]   	1
5 	           [1-2-2-1]                                  	10
8-9              [1-2-2-3], [1-2-2-4] 	                2

HTML:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) DEFAULT NULL,
  `afid` int(6) DEFAULT NULL,
  `lager` varchar(6) DEFAULT NULL,
  `regal` varchar(6) NOT NULL,
  `hoehe` varchar(6) NOT NULL,
  `fach` varchar(6) NOT NULL,
  `meter` varchar(6) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `test`
--

INSERT INTO `test` (`id`, `afid`, `lager`, `regal`, `hoehe`, `fach`, `meter`) VALUES
(1, 1, '1', '2', '1', '1', '1'),
(2, 2, '1', '2', '1', '1', '0'),
(3, 3, '1', '2', '1', '1', '0'),
(4, 5, '1', '2', '2', '1', '10'),
(5, 8, '1', '2', '2', '3', '0.5'),
(6, 9, '1', '2', '2', '4', '1.5'),
(7, 10, '1', '2', '3', '4', '1'),
(8, 11, '1', '2', '3', '4', '1'),
(9, 12, '1', '2', '3', '4', '0'),
(10, 13, '1', '2', '3', '4', '1'),
(11, 20, '1', '2', '4', '1', '1');

SQL:

SELECT
	grp.afid_range,
	-- Lager zusammenstellen, sortiert nach der id



	GROUP_CONCAT(CONCAT('[', t1.lager,-t1.regal,-t1.hoehe,-t1.fach, ']') ORDER BY t1.id SEPARATOR ', ') AS lagerort,

SUM(meter) AS 'gesamt_meter'




FROM
	test t1,
	-- Gruppieren über die group_id und den Range zusammensetzen
	(
		SELECT
			group_id,
			CASE 
				WHEN MIN(prep.afid) = MAX(prep.afid)
				THEN MIN(prep.afid)
				ELSE CONCAT(MIN(prep.afid), '-', MAX(prep.afid))
			END AS afid_range,
			GROUP_CONCAT(prep.id_set SEPARATOR ',') AS id_set
		FROM
			-- Jedem Code eine Gruppen-ID zuweisen
			(
				SELECT
					-- Letzte Gruppe als GruppenId nehmen
					@group_id AS group_id,
					t.afid,
					t.id_set,
					-- Nächte Gruppe ermitteln
					@group_id := CASE
					WHEN next_afid NOT IN (SELECT UPPER(afid) FROM test)
						THEN @group_id + 1  -- Die nexte ID exisitert nicht, ergo Gruppen-ID um eins erhöhen
						ELSE @group_id      -- Due Gruppen-ID bleibt unverändert
					END AS next_group_id
				FROM
					-- Die Variable initialisieren
					(SELECT    @group_id := 1) vars,
					-- Die Daten vorbereiten
					(
						SELECT
							UPPER(afid) AS afid,
							-- Irgend eine Logik, welche den theoretisch nächsten Code ermittelt
							CHAR(ASCII(UPPER(afid))+1) AS next_afid,
							-- Set mit allen betroffenen IDS erstellen
							GROUP_CONCAT(id SEPARATOR ',') AS id_set 
						FROM      test 
						WHERE     afid REGEXP '^[[:alnum:]]$'
						


GROUP BY  afid
						-- Nach dem Gruppierungs-Code sortieren
						ORDER BY  afid	
					) t
			) prep
		GROUP BY
		prep.group_id
	) grp
WHERE     FIND_IN_SET(t1.id, grp.id_set)
GROUP BY  grp.group_id

Gruß Thomas

Bratkartoffel · 3. April 2014

Hi,

also bei so einem komplexen SQL stellt sich (für mich) die Frage, ob mans nicht doch in PHP umsetzen sollte. Ich mein, ich sehe hier in der Arbeit auch immer wieder so komplexe Mega-SQL's (mit teilweise 800 Zeilen) und bin immer wieder am Zähne ausbeißen weil der Ersteller nicht mehr in der Firma arbeitet.

Back to topic:
Ist denn sowas noch wart- / erweiterbar? Ich mein damit, in ein paar Jahren, evtl auch von einem Kollegen und nicht von dir? Da der Thread ja schon etwas länger geht und der SQL größtenteils von Yaslaw geschrieben wurde, möchte ich dir dringend dazu raten die Query entweder gut zu dokumentieren, selber zu schreiben oder einen Teil davon in PHP umzusetzen.

Grüße,
BK

Thomas_Jung · 3. April 2014

@Bratkartoffel

Hi, du hast Recht es Funktioniert ja alles soweit.
Ich werde mir die Lagerdaten mit einer 2 Abfrage in PHP holen.
Ist für mich einfacher

Gruß Thomas

Danke noch an Yaslaw

Fortlaufende Nummerierung erkennen

Yaslaw

alter Rempler

mermshaus

Erfahrenes Mitglied

Thomas_Jung

Erfahrenes Mitglied

Yaslaw

alter Rempler

Thomas_Jung

Erfahrenes Mitglied

Yaslaw

alter Rempler

Thomas_Jung

Erfahrenes Mitglied

Bratkartoffel

gebratene Kartoffel

Thomas_Jung

Erfahrenes Mitglied

Neue Beiträge