demande aide requete sql

Version imprimable

Bonjour,

Je suis actuellement en train de développer une application Web pour une association de supporters d'un club de rugby. Je cherche à calculer le classement du championnat Top14 à partir des résultats des rencontres effectuées jusqu'à présent.
Selon les règles de la LNR, une victoire compte 4 points, un nul 2, une défaite 0 auxquels se rajoutent des points de bonus calculés selon ces règles un point de bonus offensif si l'équipe marque plus de 3 essais de plus que son adversaire et un point de bonus défensif si l'équipe perd de moins de 7 points (entre 1 et 7 compris).
Toujours selon les règles de la LNR, le classement se fait comme suit, les équipes sont d'abord "triées" par le nombre de points obtenus (règles ci-dessus), en cas d'égalité c'est le goal average (différence de points terrains marqués) particulier qui rentre en compte (soit une équipe A et une équipe B, par exemple A a gagné 15-10 contre B au match aller et B a gagné 15-13 au match alors A est devant), s'il y a encore égalité alors c'est au nombre d'essais marqués entre ces 2 équipes de la même manière que précédemment.

Ci-dessous, la table me permettant de stocker l'ensemble des résultats des différents matchs.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
-- création table match
IF NOT EXISTS(SELECT * FROM sysobjects WHERE name = '[dbo].[match]' AND xtype = 'U')
BEGIN
	CREATE TABLE [dbo].[match]
	(
		[comp_id] INT NOT NULL REFERENCES [competition]([comp_id]),
		[equi_id_domicile] INT NOT NULL REFERENCES [equipe]([equi_id]),
		[equi_id_exterieure] INT NOT NULL REFERENCES [equipe]([equi_id]),
		[sais_id] INT NOT NULL REFERENCES [saison]([sais_id]),
		[rang_id] INT NOT NULL REFERENCES [rang]([rang_id]),
		[matc_num_journee] INT NULL,
		[matc_score_domicile] INT NOT NULL,
		[matc_score_exterieure] INT NOT NULL,
		[matc_essai_domicile] INT NOT NULL,
		[matc_essai_exterieure] INT NOT NULL,
		[matc_d_deroulement] DATETIME NOT NULL,
		[matc_a_ete_joue] BIT NOT NULL
		CONSTRAINT PK_match PRIMARY KEY CLUSTERED ([comp_id],[equi_id_domicile],[equi_id_exterieure],[sais_id],[rang_id])
	)
END

Ci-dessous, la requête actuelle où j'arrive à trier selon le nombre de points,

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
DECLARE @saisId INT, @compId INT, @rangId INT, @nbPointsVictoire INT, @nbPointsNul INT, @nbPointsPerdu INT
SELECT @saisId = sais_id FROM dbo.saison WHERE sais_annee_debut = 2011 AND sais_annee_fin = 2012
SELECT @compId = comp_id FROM dbo.competition WHERE comp_nom = 'Top 14'
SELECT @rangId = rang_id FROM dbo.rang WHERE rang_nom = 'Saison régulière'
SET @nbPointsVictoire = 4
SET @nbPointsNul = 2
SET @nbPointsPerdu = 0
 
SELECT m.equi_nom, (@nbPointsVictoire * m.nb_matchs_gagnes + @nbPointsNul * m.nb_matchs_nuls + @nbPointsPerdu * m.nb_matchs_perdus + nb_points_bonus_defensif + nb_points_bonus_offensif) AS [nb_points],
(m.nb_points_bonus_defensif + m.nb_points_bonus_offensif) AS [nb_points_bonus], m.nb_matchs_joues, m.nb_matchs_gagnes, m.nb_matchs_nuls, m.nb_matchs_perdus, m.score_pour, m.score_contre, (m.score_pour - m.score_contre) AS [difference]
FROM
(
	SELECT m.equi_nom, COUNT(*) AS [nb_matchs_joues], SUM(m.match_gagne) AS [nb_matchs_gagnes],
	SUM(m.match_nul) AS [nb_matchs_nuls], SUM(m.match_perdu) AS [nb_matchs_perdus], SUM(m.score_pour) AS [score_pour],
	SUM(m.score_contre) AS [score_contre], SUM(m.point_bonus_defensif) AS [nb_points_bonus_defensif], SUM(m.point_bonus_offensif) AS [nb_points_bonus_offensif]
	FROM
	(
		-- match où l'équipe est à domicile
		SELECT e.equi_nom, m.matc_score_domicile AS [score_pour], m.matc_score_exterieure AS [score_contre],
		m.matc_d_deroulement,
		CASE WHEN m.matc_score_domicile > m.matc_score_exterieure THEN 1 ELSE 0 END AS [match_gagne],
		CASE WHEN m.matc_score_domicile = m.matc_score_exterieure THEN 1 ELSE 0 END AS [match_nul],
		CASE WHEN m.matc_score_domicile < m.matc_score_exterieure THEN 1 ELSE 0 END AS [match_perdu],
		CASE WHEN m.matc_score_exterieure - m.matc_score_domicile BETWEEN 1 AND 7 THEN 1 ELSE 0 END AS [point_bonus_defensif],
		CASE WHEN m.matc_essai_domicile - m.matc_essai_exterieure >= 3 THEN 1 ELSE 0 END AS [point_bonus_offensif]
		FROM dbo.match m
		INNER JOIN dbo.equipe e ON m.equi_id_domicile = e.equi_id
		WHERE m.comp_id = @compId
		AND m.sais_id = @saisId
		AND m.rang_id = @rangId
		AND m.matc_d_deroulement <= GETDATE()
		AND m.matc_a_ete_joue = 1
		UNION
		-- match où l'équipe est à l'extérieure
		SELECT e.equi_nom, m.matc_score_exterieure AS [score_pour], m.matc_score_domicile AS [score_contre],
		m.matc_d_deroulement,
		CASE WHEN m.matc_score_domicile < m.matc_score_exterieure THEN 1 ELSE 0 END AS [match_gagne],
		CASE WHEN m.matc_score_domicile = m.matc_score_exterieure THEN 1 ELSE 0 END AS [match_nul],
		CASE WHEN m.matc_score_domicile > m.matc_score_exterieure THEN 1 ELSE 0 END AS [match_perdu],
		CASE WHEN m.matc_score_domicile - m.matc_score_exterieure BETWEEN 1 AND 7 THEN 1 ELSE 0 END AS [point_bonus_defensif],
		CASE WHEN m.matc_essai_exterieure - m.matc_essai_domicile >= 3 THEN 1 ELSE 0 END AS [point_bonus_offensif]
		FROM dbo.match m
		INNER JOIN dbo.equipe e ON m.equi_id_exterieure = e.equi_id
		WHERE m.comp_id = @compId
		AND m.sais_id = @saisId
		AND m.rang_id = @rangId
		AND m.matc_d_deroulement <= GETDATE()
		AND m.matc_a_ete_joue = 1
	) m
	GROUP BY m.equi_nom
) m
ORDER BY nb_points DESC

Ma question est donc comment pouvoir implémenter les 2 dernières règles de classement par requête SQL, si c'est impossible alors je le ferai par l'applicatif.

Merci pour vos réponses

Rérou

09/05/2012, 23h16
rerou

Bon vu le nombre de visites à cette heure (93) et le nombre de réponses obtenues (0), je vois que le problème n'est pas si évident que ça, alors je vais aller au plus simple en faisant la manip côté software.
Je laisse la conversation ouverte car il me semble que ceci peut être un bon exercice.

Bon j'ai trouvé un truc que l'on peut considérer comme capillotracté.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
 
DECLARE @saisId INT, @compId INT, @rangId INT, @nbPointsVictoire INT, @nbPointsNul INT, @nbPointsPerdu INT
SELECT @saisId = sais_id FROM dbo.saison WHERE sais_annee_debut = 2011 AND sais_annee_fin = 2012
SELECT @compId = comp_id FROM dbo.competition WHERE comp_nom = 'Top 14'
SELECT @rangId = rang_id FROM dbo.rang WHERE rang_nom = 'Saison régulière'
SET @nbPointsVictoire = 4
SET @nbPointsNul = 2
SET @nbPointsPerdu = 0
 
SELECT m.equi_id, m.equi_nom, (@nbPointsVictoire * m.nb_matchs_gagnes + @nbPointsNul * m.nb_matchs_nuls + @nbPointsPerdu * m.nb_matchs_perdus + nb_points_bonus_defensif + nb_points_bonus_offensif) AS [nb_points],
(m.nb_points_bonus_defensif + m.nb_points_bonus_offensif) AS [nb_points_bonus], m.nb_matchs_joues, m.nb_matchs_gagnes, m.nb_matchs_nuls, m.nb_matchs_perdus, m.score_pour, m.score_contre, (m.score_pour - m.score_contre) AS [difference]
INTO #tmp
FROM
(
	SELECT m.equi_id, m.equi_nom, COUNT(*) AS [nb_matchs_joues], SUM(m.match_gagne) AS [nb_matchs_gagnes],
	SUM(m.match_nul) AS [nb_matchs_nuls], SUM(m.match_perdu) AS [nb_matchs_perdus], SUM(m.score_pour) AS [score_pour],
	SUM(m.score_contre) AS [score_contre], SUM(m.point_bonus_defensif) AS [nb_points_bonus_defensif], SUM(m.point_bonus_offensif) AS [nb_points_bonus_offensif]
	FROM
	(
		-- match où l'équipe est à domicile
		SELECT e.equi_id, e.equi_nom, m.matc_score_domicile AS [score_pour], m.matc_score_exterieure AS [score_contre],
		m.matc_d_deroulement,
		CASE WHEN m.matc_score_domicile > m.matc_score_exterieure THEN 1 ELSE 0 END AS [match_gagne],
		CASE WHEN m.matc_score_domicile = m.matc_score_exterieure THEN 1 ELSE 0 END AS [match_nul],
		CASE WHEN m.matc_score_domicile < m.matc_score_exterieure THEN 1 ELSE 0 END AS [match_perdu],
		CASE WHEN m.matc_score_exterieure - m.matc_score_domicile BETWEEN 1 AND 7 THEN 1 ELSE 0 END AS [point_bonus_defensif],
		CASE WHEN m.matc_essai_domicile - m.matc_essai_exterieure >= 3 THEN 1 ELSE 0 END AS [point_bonus_offensif]
		FROM dbo.match m
		INNER JOIN dbo.equipe e ON m.equi_id_domicile = e.equi_id
		WHERE m.comp_id = @compId
		AND m.sais_id = @saisId
		AND m.rang_id = @rangId
		AND m.matc_d_deroulement <= GETDATE()
		AND m.matc_a_ete_joue = 1
		UNION
		-- match où l'équipe est à l'extérieure
		SELECT e.equi_id, e.equi_nom, m.matc_score_exterieure AS [score_pour], m.matc_score_domicile AS [score_contre],
		m.matc_d_deroulement,
		CASE WHEN m.matc_score_domicile < m.matc_score_exterieure THEN 1 ELSE 0 END AS [match_gagne],
		CASE WHEN m.matc_score_domicile = m.matc_score_exterieure THEN 1 ELSE 0 END AS [match_nul],
		CASE WHEN m.matc_score_domicile > m.matc_score_exterieure THEN 1 ELSE 0 END AS [match_perdu],
		CASE WHEN m.matc_score_domicile - m.matc_score_exterieure BETWEEN 1 AND 7 THEN 1 ELSE 0 END AS [point_bonus_defensif],
		CASE WHEN m.matc_essai_exterieure - m.matc_essai_domicile >= 3 THEN 1 ELSE 0 END AS [point_bonus_offensif]
		FROM dbo.match m
		INNER JOIN dbo.equipe e ON m.equi_id_exterieure = e.equi_id
		WHERE m.comp_id = @compId
		AND m.sais_id = @saisId
		AND m.rang_id = @rangId
		AND m.matc_d_deroulement <= GETDATE()
		AND m.matc_a_ete_joue = 1
	) m
	GROUP BY m.equi_nom, m.equi_id
) m
ORDER BY nb_points DESC
 
SELECT m.equi_id,
CASE
	WHEN m.score_equipe1 > m.score_equipe2 THEN 1
	WHEN m.score_equipe2 > m.score_equipe1 THEN 0
	ELSE CASE
		WHEN m.essai_equipe1 > m.essai_equipe2 THEN 1
		WHEN m.essai_equipe2 > m.essai_equipe1 THEN 0
		ELSE 0
	END
END AS [ordre]
INTO #tmp2
FROM
(
	SELECT t1.*, m1.matc_score_domicile + m2.matc_score_exterieure AS [score_equipe1],
	m1.matc_score_exterieure + m2.matc_score_domicile AS [score_equipe2],
	m1.matc_essai_domicile + m2.matc_essai_exterieure AS [essai_equipe1],
	m1.matc_essai_exterieure + m2.matc_essai_domicile AS [essai_equipe2]
	FROM #tmp t1
	INNER JOIN #tmp t2 ON t1.nb_points = t2.nb_points AND t1.equi_id <> t2.equi_id
	INNER JOIN dbo.match m1 ON t1.equi_id = m1.equi_id_domicile AND t2.equi_id = m1.equi_id_exterieure
	INNER JOIN dbo.match m2 ON t2.equi_id = m2.equi_id_domicile AND t1.equi_id = m2.equi_id_exterieure
) m
 
SELECT DISTINCT t1.*, t2.ordre
FROM #tmp t1
LEFT JOIN #tmp2 t2 ON t1.equi_id = t2.equi_id
ORDER BY t1.nb_points DESC, t2.ordre DESC, t1.difference DESC
 
DROP TABLE #tmp
DROP TABLE #tmp2

Je laisse encore le sujet ouvert 1 semaine si des personnes veulent commenter et si personne n'intervient alors je le fermerai