On gists

Tipliga - SQL - multiplevariables - ranks

MySql

tipliga.sql Raw #

	INSERT INTO
		_tipliga_poradi
		(
			poradi,
			id_hrace,
			liga,
			rocnik,
			pz,
			v,
			r,
			p,
			nz,
			score,
			pts
		)
		
		SELECT
		rank,
		hrac,
		liga,
		rocnik,
		pz,
		v,
		r,
		p, 
		NETIPOVANO,
		SKORE,
		PTS
		FROM
		
		(
		
			SELECT
			*,
			IF(SKORE = @lastScore AND @lastPts = PTS, @currentRank := @currentRank, @currentRank := @i) as rank,
			@i := @i + 1,
			@lastScore := SKORE,
			@lastPts := PTS
			FROM
			(
			
			
			SELECT
							hrac, 
							h.prezdivka,
							SUM(PZ) as PZ,
							SUM(V) as V,
							SUM(R) as R,
							SUM(P) as P,
							SUM(NETIPOVANO) as NETIPOVANO,
							SUM(PTS) as SKORE,
							SUM(V)*3 + SUM(R)*1 + SUM(NETIPOVANO)*-1 as PTS,
							liga, 
							rocnik
			
							FROM
							(
										SELECT
										domaci as hrac,
										COUNT(*) as PZ,
										SUM(IF (domaci_body>host_body,1,0)) as V,
										SUM(IF (domaci_body=host_body AND domaci_tipoval = 1 AND host_tipoval = 1, 1,0)) as R,
										SUM(IF (domaci_body<host_body AND domaci_tipoval = 1, 1,0)) as P,
										SUM(IF (domaci_tipoval = 0, 1, 0)) as NETIPOVANO,
										SUM(domaci_body) as PTS,
										liga,
										rocnik
										
										FROM _tipliga_rozpisy
										WHERE 
										liga=%s
										AND rocnik=%s
										AND kolo <= %i
										GROUP BY hrac
								
										UNION ALL
										
										SELECT
										host as hrac,
										COUNT(*) as PZ,
										SUM(IF (domaci_body<host_body,1,0)) as V,
										SUM(IF (domaci_body=host_body AND domaci_tipoval = 1 AND host_tipoval = 1, 1,0)) as R,
										SUM(IF (domaci_body>host_body AND host_tipoval = 1, 1,0)) as P,
										SUM(IF (host_tipoval = 0, 1, 0)) as NETIPOVANO,
										SUM(host_body) as PTS,
										liga,
										rocnik
										
										FROM _tipliga_rozpisy
										WHERE 
										liga=%s
										AND rocnik=%s
										AND kolo <= %i
										GROUP BY hrac
							) as TMP
							
							JOIN hraci h ON h.id = TMP.hrac
							GROUP BY hrac
							ORDER BY PTS DESC, SKORE DESC, NETIPOVANO ASC, V DESC, h.prezdivka ASC
							
				) tmp,
				(SELECT @currentRank := 1, @i := 1, @lastPts:= -999, @lastScore := -999) as config
			) finalTable