SQL Abfrage über 2 Datumsspalten

Changkowski · 26.06.2011, 19:27

Hallo ich bin neu hier

Ich habe eine SQL-Tabelle mit folgender Struktur.

CREATE TABLE IF NOT EXISTS `assignments` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`requestID` mediumint(

unsigned NOT NULL,
`employeeID` mediumint(

unsigned NOT NULL,
`clientID` mediumint(

unsigned NOT NULL,
`statusID` tinyint(3) unsigned NOT NULL,
`location` varchar(64) COLLATE latin1_german1_ci NOT NULL,
`date` date NOT NULL,
`timeFrom` time NOT NULL,
`timeTo` time NOT NULL,
`remarks` text COLLATE latin1_german1_ci NOT NULL,
`description` text COLLATE latin1_german1_ci NOT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`edited` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`editorID` mediumint(

unsigned NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=2999 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

Die Tabelle kann für die Spalte date mehrere Einträge am gleichen Tag enthalten. Die Spalte date enthält keine Stunden, Minuten und Sekunden angaben, so dass ein Eintrag unique ist wenn er an einem bestimmten Tag ist.
Ich benötige dazu eine Abfrage, die alle Einträge in denen date vor einem bestimmten Datum war (bspw. date < '2011-06-30') enthält und in der immer nur für jeden Tag ein Eintrag kommt und zwar der bei dem created zuletzt geschrieben wurde, falls es mehrere Einträge gibt bei denen date am gleichen Tag ist.
Das ganze soll dann noch nach date sortiert sein.

Bisher sieht das ganze so aus, aber es funktioniert leider noch nicht so wie ich mir das vorgestellt habe, da MAX(created) nicht ganz das liefert was ich mir vorgestellt habe...

Code:

select *, MAX(created) from assignments 
where date < '2011-06-30' 
and employeeID = 52 
group by date
order by date desc Limit 3

Ich wäre froh wenn mir jemand weiter helfen könnte...

Grüße,
Changkowski

DirkHo · 26.06.2011, 21:30

Zitat:

Zitat von Changkowski

Die Tabelle kann für die Spalte date mehrere Einträge am gleichen Tag enthalten.

Zitat:

Zitat von Changkowski

Die Spalte date enthält keine Stunden, Minuten und Sekunden angaben, so dass ein Eintrag unique ist wenn er an einem bestimmten Tag ist.

Zitat:

Zitat von Changkowski

und in der immer nur für jeden Tag ein Eintrag kommt und zwar der bei dem created zuletzt geschrieben wurde, falls es mehrere Einträge gibt bei denen date am gleichen Tag ist.

Ist die Spalte jetzt unique oder nicht?

Meines Wissens wird beim Gruppieren immer zufällig ein Wert genommen um die nicht gruppierten Spalten zu füllen.

Changkowski · 27.06.2011, 00:41

Die Spalte date ist vom Typ Date und hat keine Stunden, Minuten, Sekunden,... Angaben. Nur Jahr, Monat und Tag.
Es kann also mehrere Einträge geben die am gleichen Tag sind und somit also nicht unique. Ich hab mich da wohl falsch ausgedrückt... Damit es eindeutig wird soll, wenn date am gleichen Tag ist, immer der jüngste Eintrag von created genommen werden.

tr0y · 27.06.2011, 02:04

hmm ungetestet, aber..

a) sub-selection

Code:

SELECT
   id, requestID, employeeID, clientID, statusID,
   location, date, timeFrom, timeTo, remarks,
   description, created, edited, editorID    
FROM assignments 
WHERE date < CAST('2011-06-30' AS DATE)
   AND employeeID = 52 
   AND created = (
      SELECT MAX(created) FROM assignments
         WHERE date < CAST('2011-06-30' AS DATE)
            AND employeeID = 52
   )
GROUP BY date

b) sub-selection als GROUP BY | HAVING

Code:

SELECT
   id, requestID, employeeID, clientID, statusID,
   location, date, timeFrom, timeTo, remarks,
   description, created, edited, editorID
FROM assignments
WHERE date < CAST( '2011-06-30' AS DATE )
   AND employeeID = 52
GROUP BY date
HAVING created = (
   SELECT MAX(created) FROM assignments
      WHERE date < CAST('2011-06-30' AS DATE)
         AND employeeID = 52
)

Changkowski · 27.06.2011, 09:53

Hallo trOy Danke für die Antwort.

Code:

SELECT
   id, requestID, employeeID, clientID, statusID,
   location, date, timeFrom, timeTo, remarks,
   description, created, edited, editorID    
FROM assignments 
WHERE date < CAST('2011-06-30' AS DATE)
   AND employeeID = 52 
   AND created = (
      SELECT MAX(created) FROM assignments
         WHERE date < CAST('2011-06-30' AS DATE)
            AND employeeID = 52
   )
GROUP BY date

sieht gut aus, aber es liefert immer nur einen Eintrag und zwar den mit dem jüngsten Eintrag in created. Ich brauche aber für jedes Datum date den Eintrag mit dem jüngsten created und diese sollen absteigend sortiert sein (im Beispiel wäre das 1. 2011-06-29 mit dem jüngsten created, 2. 2011-06-28 mit dem jüngsten created, 3. 2011-06-27 mit dem jüngsten created,...). Es geht also nicht um den Eintrag in dem created am jüngsten ist, sondern immer der Eintrag für den created an einem bestimmten Tag in date am jüngsten ist.

ID ..... date .... created
3000 2011-06-29 2011-06-26 19:23:15
3001 2011-06-29 2011-06-25 19:48:08
3002 2011-06-29 2011-06-26 10:48:08
3003 2011-06-28 2011-06-20 19:48:08
3004 2011-06-27 2011-06-20 13:48:08
3005 2011-06-28 2011-06-24 19:48:08

soll liefern:
3000, ...
3005, ...
3004, ...

Es müssten also mehrere Zeilen als Ergebnis kommen, wenn es in date unterschiedliche Daten gibt was eigentlich immer der Fall ist.
Ich hoffe du oder jemand anders kann mir nochmal weiterhelfen damit. Die Gesamtergebnismenge soll auch auf 3 beschränkt sein aber das müsste ja mit limit 3 einfach zu machen sein. Ich bedanke mich schonmal.
Beste Grüße,
Changkowski

tr0y · 27.06.2011, 15:02

hmm stimmt, ich selektiere im sub-query murks, entschuldige.

Code:

SELECT
   id, requestID, employeeID, clientID, statusID,
   location, date, timeFrom, timeTo, remarks,
   description, created, edited, editorID    
FROM assignments AS outer
WHERE outer.date < CAST('2011-06-30' AS DATE)
   AND outer.employeeID = 52 
   AND outer.created = (
      SELECT MAX(created) FROM assignments AS inner
         WHERE inner.date = outer.date
            AND inner.employeeID = outer.employeeID
   )
GROUP BY outer.date

Da ich jetzt nicht unbedingt eine Tabelle anlegen wollte um das query zu testen, wäre es schön wenn du Berichtest ob das funktioniert. Kurz erklärt was das Query tut:

Code:

SELEKTIERE {felder...} 
   VON TABELLE assignments ALS ALIAS outer WO WERTZUSTAND:
      employeeID IST GLEICH 52
      created IST GLEICH (
         UNTER-SELEKTIERUNG MAXIMUM VON FELD(created) 
            VON TABELLE assignments ALS ALIAS inner WO WERTZUSTAND:
         ALIAS inner FELD date IST GLEICH ALIAS outer FELD date
         ALIAS inner FELD employeeID IST GLEICH ALIAS outer FELD employeeID
      )
   GRUPPIERE WERTE IN ALIAS outer FELD date

Limits nach dem GROUP BY limitieren die gesamtausgabe auf 3 rows ( in deinem Fall - LIMIT 3 ), ja.

Vorab schonmal eine Warnung, du begegnest in MySQL 2erlei "Aliase", die für Felder und die für Datenbanken, erstere können nicht in der WHERE-Clause verwendet werden, da erst die WHERE-Clause und dann das eigentliche SELECT ausgeführt wird, zweitere allerdings schon, da vor dem WHERE die Datenbank "entschieden" wird.

Bei Subselects gibts bei größeren Tabellen und je nach Komplexität des oder der ( multiplen ) Subqueries performance verluste. Bei größeren Sachen sollte man JOINs verwenden, statt Sub-SELECTs.

Changkowski · 29.06.2011, 15:10

Hi tr0y

Sorry für die verspätete Antwort. Ich bekomme leider eine Fehlermeldung wenn ich das ausführe und kann gerade den Fehler nicht finden.

Code:

SQL Error (1604): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer WHERE outer.date < CAST('2011-06-30' AS DATE) AND outer.employeeID = ' at line 5

Ich hatte aber auch eine alternative Lösung gefunden.

Code:

select id, date, created
from assignments
where (date, created) in (select date, max(created) from assignments group by date);

Trotzdem würde mich interessieren was bei der anderen Lösung nicht funktioniert. Ich hab zumindest noch keinen Fehler finden können, aber vielleicht liegts auch an der Hitze.

Danke nochmal für die Antwort.

ChrisB · 29.06.2011, 15:34

Zitat:

Zitat von Changkowski

Ich bekomme leider eine Fehlermeldung wenn ich das ausführe und kann gerade den Fehler nicht finden.

http://dev.mysql.com/doc/refman/5.1/...ved-words.html

tr0y · 29.06.2011, 16:53

Code:

SELECT
   id, requestID, employeeID, clientID, statusID,
   location, date, timeFrom, timeTo, remarks,
   description, created, edited, editorID    
FROM assignments AS universe
WHERE universe.date < CAST('2011-06-30' AS DATE)
   AND universe.employeeID = 52 
   AND universe.created = (
      SELECT MAX(created) FROM assignments AS earth
         WHERE earth.date = universe.date
            AND earth.employeeID = universe.employeeID
   )
GROUP BY universe.date

ajo, reserved words, kommt davon wenn man nebenher noch 20 Dinge machen soll

Changkowski · 30.06.2011, 12:12

Hallo nochmal und Danke für die Hilfe.
Beide Abfragen funktionieren jetzt, aber jetzt habe ich das Problem das beide Abfragen auch sehr langsam sind. Ich habe bislang noch nicht besonders viel mit Subqueries gearbeitet, aber ich hätte nicht gedacht, dass Wartezeiten von 10 Minuten dadurch entstehen.
Gibt es eine Möglichkeit das ganze zu beschleunigen?

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
Sql Befehl über zwei verschiedene Datenbanken	darkman	Datenbanken	8	10.05.2011 10:29
[Erledigt] SQL Abfrage escapen	wooha	PHP Einsteiger	3	18.04.2011 10:13
SQL Abfrage über PHP	cURE	Datenbanken	3	09.02.2011 12:55
[Erledigt] Grundsatzfrage über Tabellenstruktur und SQL Statement	Bravestar2010	Datenbanken	2	04.02.2011 11:49
Array aus SQL Abfrage zusammenbauen um es rekursiv abzuarbeiten	litterauspirna	PHP Einsteiger	7	01.02.2011 18:44
mySQL abfrage über mehrere Tabellen	crustyroyal	PHP Tipps 2010	3	23.04.2010 14:20
Abfrage über 2 Tabellen	ragtek	PHP Tipps 2010	4	14.04.2010 19:43
[Erledigt] Select Abfrage über 2 Tabellen bringt keine Ausgabe	clamber	Datenbanken	4	03.03.2010 15:25
[Erledigt] Abfrage mit date_add über PHP-Script liefert Fehler	stende	Datenbanken	7	17.08.2009 13:44
Scriptsuche [Erledigt] SQL- Abfrage über 2 tabellen	ejim	Scriptbörse	1	17.07.2009 21:10
Wiemache ich eine Abfrage über 3 Tabellen???	djscaleo	Datenbanken	8	05.01.2009 10:46
Abfrage über mehrer Tabellen mit Berechnung	richyboy	Datenbanken	1	04.11.2008 12:00
Abfrage aller Tabellen über Schleife		PHP Tipps 2005-2	7	22.10.2005 12:16
[Erledigt] mysql abfrage über 4 Tabelle - bis 3 geht, bei der 4. habert		Datenbanken	2	08.09.2005 11:59
Abfrage über 3 Tabellen		PHP Tipps 2004	3	31.07.2004 22:34

26.06.2011, 19:27
Changkowski Neuer Benutzer Registriert seit: 26.06.2011 Beiträge: 6 PHP-Kenntnisse: Anfänger	SQL Abfrage über 2 Datumsspalten Hallo ich bin neu hier Ich habe eine SQL-Tabelle mit folgender Struktur. CREATE TABLE IF NOT EXISTS `assignments` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `requestID` mediumint( unsigned NOT NULL, `employeeID` mediumint( unsigned NOT NULL, `clientID` mediumint( unsigned NOT NULL, `statusID` tinyint(3) unsigned NOT NULL, `location` varchar(64) COLLATE latin1_german1_ci NOT NULL, `date` date NOT NULL, `timeFrom` time NOT NULL, `timeTo` time NOT NULL, `remarks` text COLLATE latin1_german1_ci NOT NULL, `description` text COLLATE latin1_german1_ci NOT NULL, `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `edited` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `editorID` mediumint( unsigned NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=2999 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci; Die Tabelle kann für die Spalte date mehrere Einträge am gleichen Tag enthalten. Die Spalte date enthält keine Stunden, Minuten und Sekunden angaben, so dass ein Eintrag unique ist wenn er an einem bestimmten Tag ist. Ich benötige dazu eine Abfrage, die alle Einträge in denen date vor einem bestimmten Datum war (bspw. date < '2011-06-30') enthält und in der immer nur für jeden Tag ein Eintrag kommt und zwar der bei dem created zuletzt geschrieben wurde, falls es mehrere Einträge gibt bei denen date am gleichen Tag ist. Das ganze soll dann noch nach date sortiert sein. Bisher sieht das ganze so aus, aber es funktioniert leider noch nicht so wie ich mir das vorgestellt habe, da MAX(created) nicht ganz das liefert was ich mir vorgestellt habe... Code: select , MAX(created) from assignments where date < '2011-06-30' and employeeID = 52 group by date order by date desc Limit 3 Ich wäre froh wenn mir jemand weiter helfen könnte... Grüße, Changkowski Geändert von Changkowski (26.06.2011 um 19:42 Uhr).*


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

27.06.2011, 00:41
Changkowski Neuer Benutzer Registriert seit: 26.06.2011 Beiträge: 6 PHP-Kenntnisse: Anfänger	Die Spalte date ist vom Typ Date und hat keine Stunden, Minuten, Sekunden,... Angaben. Nur Jahr, Monat und Tag. Es kann also mehrere Einträge geben die am gleichen Tag sind und somit also nicht unique. Ich hab mich da wohl falsch ausgedrückt... Damit es eindeutig wird soll, wenn date am gleichen Tag ist, immer der jüngste Eintrag von created genommen werden.

27.06.2011, 09:53
Changkowski Neuer Benutzer Registriert seit: 26.06.2011 Beiträge: 6 PHP-Kenntnisse: Anfänger	Hallo trOy Danke für die Antwort. Code: SELECT id, requestID, employeeID, clientID, statusID, location, date, timeFrom, timeTo, remarks, description, created, edited, editorID FROM assignments WHERE date < CAST('2011-06-30' AS DATE) AND employeeID = 52 AND created = ( SELECT MAX(created) FROM assignments WHERE date < CAST('2011-06-30' AS DATE) AND employeeID = 52 ) GROUP BY date sieht gut aus, aber es liefert immer nur einen Eintrag und zwar den mit dem jüngsten Eintrag in created. Ich brauche aber für jedes Datum date den Eintrag mit dem jüngsten created und diese sollen absteigend sortiert sein (im Beispiel wäre das 1. 2011-06-29 mit dem jüngsten created, 2. 2011-06-28 mit dem jüngsten created, 3. 2011-06-27 mit dem jüngsten created,...). Es geht also nicht um den Eintrag in dem created am jüngsten ist, sondern immer der Eintrag für den created an einem bestimmten Tag in date am jüngsten ist. ID ..... date .... created 3000 2011-06-29 2011-06-26 19:23:15 3001 2011-06-29 2011-06-25 19:48:08 3002 2011-06-29 2011-06-26 10:48:08 3003 2011-06-28 2011-06-20 19:48:08 3004 2011-06-27 2011-06-20 13:48:08 3005 2011-06-28 2011-06-24 19:48:08 soll liefern: 3000, ... 3005, ... 3004, ... Es müssten also mehrere Zeilen als Ergebnis kommen, wenn es in date unterschiedliche Daten gibt was eigentlich immer der Fall ist. Ich hoffe du oder jemand anders kann mir nochmal weiterhelfen damit. Die Gesamtergebnismenge soll auch auf 3 beschränkt sein aber das müsste ja mit limit 3 einfach zu machen sein. Ich bedanke mich schonmal. Beste Grüße, Changkowski

27.06.2011, 15:02
tr0y Erfahrener Benutzer Registriert seit: 26.07.2010 Beiträge: 4.856 PHP-Kenntnisse: Fortgeschritten	hmm stimmt, ich selektiere im sub-query murks, entschuldige. Code: SELECT id, requestID, employeeID, clientID, statusID, location, date, timeFrom, timeTo, remarks, description, created, edited, editorID FROM assignments AS outer WHERE outer.date < CAST('2011-06-30' AS DATE) AND outer.employeeID = 52 AND outer.created = ( SELECT MAX(created) FROM assignments AS inner WHERE inner.date = outer.date AND inner.employeeID = outer.employeeID ) GROUP BY outer.date Da ich jetzt nicht unbedingt eine Tabelle anlegen wollte um das query zu testen, wäre es schön wenn du Berichtest ob das funktioniert. Kurz erklärt was das Query tut: Code: SELEKTIERE {felder...} VON TABELLE assignments ALS ALIAS outer WO WERTZUSTAND: employeeID IST GLEICH 52 created IST GLEICH ( UNTER-SELEKTIERUNG MAXIMUM VON FELD(created) VON TABELLE assignments ALS ALIAS inner WO WERTZUSTAND: ALIAS inner FELD date IST GLEICH ALIAS outer FELD date ALIAS inner FELD employeeID IST GLEICH ALIAS outer FELD employeeID ) GRUPPIERE WERTE IN ALIAS outer FELD date Limits nach dem GROUP BY limitieren die gesamtausgabe auf 3 rows ( in deinem Fall - LIMIT 3 ), ja. Vorab schonmal eine Warnung, du begegnest in MySQL 2erlei "Aliase", die für Felder und die für Datenbanken, erstere können nicht in der WHERE-Clause verwendet werden, da erst die WHERE-Clause und dann das eigentliche SELECT ausgeführt wird, zweitere allerdings schon, da vor dem WHERE die Datenbank "entschieden" wird. Bei Subselects gibts bei größeren Tabellen und je nach Komplexität des oder der ( multiplen ) Subqueries performance verluste. Bei größeren Sachen sollte man JOINs verwenden, statt Sub-SELECTs. __________________ Lasse mir ohne Anwendung von Gewalt Dinge schenken, Amazon weiß darüber bald mehr.

29.06.2011, 15:10
Changkowski Neuer Benutzer Registriert seit: 26.06.2011 Beiträge: 6 PHP-Kenntnisse: Anfänger	Hi tr0y Sorry für die verspätete Antwort. Ich bekomme leider eine Fehlermeldung wenn ich das ausführe und kann gerade den Fehler nicht finden. Code: SQL Error (1604): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer WHERE outer.date < CAST('2011-06-30' AS DATE) AND outer.employeeID = ' at line 5 Ich hatte aber auch eine alternative Lösung gefunden. Code: select id, date, created from assignments where (date, created) in (select date, max(created) from assignments group by date); Trotzdem würde mich interessieren was bei der anderen Lösung nicht funktioniert. Ich hab zumindest noch keinen Fehler finden können, aber vielleicht liegts auch an der Hitze. Danke nochmal für die Antwort.

30.06.2011, 12:12
Changkowski Neuer Benutzer Registriert seit: 26.06.2011 Beiträge: 6 PHP-Kenntnisse: Anfänger	Hallo nochmal und Danke für die Hilfe. Beide Abfragen funktionieren jetzt, aber jetzt habe ich das Problem das beide Abfragen auch sehr langsam sind. Ich habe bislang noch nicht besonders viel mit Subqueries gearbeitet, aber ich hätte nicht gedacht, dass Wartezeiten von 10 Minuten dadurch entstehen. Gibt es eine Möglichkeit das ganze zu beschleunigen?