Football

Einige SQL's, die zur Korrektur eingesetzt werden können:
In den Beispielen ist die Liga 1 der Saison 2011 gewählt, also die aktuelle 1. Bundesliga. Für andere Ligen ist zumindest die Liganummer jeweils zu verändern.
Wenn man nur den Subselect, also REPLACE ... weglässt und nur den Teil nach SELECT verwendet oder UPDATE weglässt und nur den Select in Klammern verwendet, kann man vorher überprüfen, was eventuell korrigiert wird.

1. Nur bei Ligen mit "Tippabgabe bis zum jeweiligen Spielbeginn".
Korrektur des Abgabetermins und Status bei den Spieltagen:

Code: Alles auswählen

# Erster Spielbeginn als Abgabetermin bei Tippabgabe bis zum Spielbeginn

REPLACE INTO phpbb_footb_matchdays (season, league, matchday, delivery_date, matchday_name, matches)
SELECT m.season, m.league, m.matchday, min(m.match_datetime) as delivery_date, md.matchday_name, md.matches 
FROM phpbb_footb_matches as m
JOIN phpbb_footb_leagues AS l ON (l.season = m.season AND l.league = m.league)
JOIN phpbb_footb_matchdays AS md ON (md.season = m.season AND md.league = m.league AND md.matchday = m.matchday)
WHERE l.bet_in_time = 1 AND m.status=0 
GROUP BY m.season, m.league, m.matchday

2. Für Ligen ohne "Tippabgabe bis zum jeweiligen Spielbeginn".
Korrektur eines Abgabetermins, der nach dem ersten Spielbeginn liegt, auf gerade diesen:

Code: Alles auswählen

# Erster Spielbeginn als Abgabetermin bei festem Abgabetermin

REPLACE INTO phpbb_footb_matchdays (season, league, matchday, status, delivery_date, delivery_date_2, delivery_date_3, matchday_name, matches)
SELECT md.season
	, md.league
	, md.matchday
	, md.status
	, min_delivery_date as delivery_date
	, md.delivery_date_2
	, md.delivery_date_3
	, md.matchday_name
	, md.matches 
FROM phpbb_footb_matchdays AS md 
INNER JOIN (SELECT season, league, matchday, min(match_datetime) as min_delivery_date 
		FROM phpbb_footb_matches 
		WHERE season = 2011 AND league = 1 AND status = 0
		GROUP BY season, league, matchday) as agg
where md.season = agg.season 
AND md.league = agg.league 
AND md.matchday = agg.matchday 
AND md.delivery_date > agg.min_delivery_date 

# Zweiter Spielbeginn als Abgabetermin bei festem Abgabetermin

REPLACE INTO phpbb_footb_matchdays (season, league, matchday, status, delivery_date, delivery_date_2, delivery_date_3, matchday_name, matches)
SELECT md.season
	, md.league
	, md.matchday
	, md.status
	, md.delivery_date
	, min_delivery_date as delivery_date_2
	, md.delivery_date_3
	, md.matchday_name
	, md.matches 
FROM phpbb_footb_matchdays AS md 
INNER JOIN (SELECT season, league, matchday, min(match_datetime) as min_delivery_date 
		FROM phpbb_footb_matches 
		WHERE season = 2011 AND league = 1 AND status = -1
		GROUP BY season, league, matchday) as agg
where md.season = agg.season 
AND md.league = agg.league 
AND md.matchday = agg.matchday 
AND md.delivery_date_2 > agg.min_delivery_date 

# Zweiter Spielbeginn als Abgabetermin bei festem Abgabetermin

REPLACE INTO phpbb_footb_matchdays (season, league, matchday, status, delivery_date, delivery_date_2, delivery_date_3, matchday_name, matches)
SELECT md.season
	, md.league
	, md.matchday
	, md.status
	, md.delivery_date
	, md.delivery_date_2
	, min_delivery_date as delivery_date_3
	, md.matchday_name
	, md.matches 
FROM phpbb_footb_matchdays AS md 
INNER JOIN (SELECT season, league, matchday, min(match_datetime) as min_delivery_date 
		FROM phpbb_footb_matches 
		WHERE season = 2011 AND league = 1 AND status = -2
		GROUP BY season, league, matchday) as agg
where md.season = agg.season 
AND md.league = agg.league 
AND md.matchday = agg.matchday 
AND md.delivery_date_3 > agg.min_delivery_date

3. Für Ligen mit oder ohne "Tippabgabe bis zum jeweiligen Spielbeginn".
Korrektur des Spieltags Status:

Code: Alles auswählen

# Korrekten Status im Spieltag setzen

UPDATE phpbb_footb_matchdays AS target 
INNER JOIN  
( 
SELECT md.season
	, md.league
	, md.matchday
	, IF( md.delivery_date > now(),
		0,
		IF(ISNULL(min(e.extra_status)),
			IF(min(m.status) = 1 AND max(m.status) > 1,
				2,
				GREATEST(min(m.status), 0)),
			IF(LEAST(min(m.status), min(e.extra_status)) = 1 AND GREATEST(max(m.status), max(e.extra_status)) > 1,
				2,
				GREATEST(LEAST(min(m.status), min(e.extra_status)), 0)))) As new_status
FROM phpbb_footb_matchdays AS md 
LEFT JOIN phpbb_footb_matches AS m ON (m.season = md.season AND m.league = md.league AND m.matchday = md.matchday)
LEFT JOIN phpbb_footb_extra AS e ON (e.season = md.season AND e.league = md.league AND e.matchday_eval = md.matchday)
WHERE md.season = 2011 AND md.league = 1
GROUP BY md.season, md.league, md.matchday) AS source
ON target.season = source.season AND target.league = source.league AND target.matchday = source.matchday
SET target.status = source.new_status

Football

Nützliche SQL's

Nützliche SQL's