Discussion :

[tecxor]

Bonjour,
je bloque depuis un moment sur ce problème.
J'espère trouver l'aide de quelqu'un de confirmé sur ce forum.

Voici le problème:
Je souhaite faire un classement de score avec gestion des égalités.

Pour l'instant rien de compliqué avec ce type de code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SELECT a.name,
(SELECT COUNT(DISTINCT b.score) FROM scores AS b WHERE b.score >= a.score) AS 'position'
FROM scores AS a ORDER BY position ASC;

Le problème c'est que ma table n'est pas de type:
name - score
bob - 500
joe - 600
tom - 300

mais j'ai 3 tables:

table: utilisateurs
id_user - pseudo
1 - bob
2 - joe
3 - tom
4 - sam

table: score_principal
id_jeu - id_user - gain - bonus - perte
1 - 1 - 500 - 50 - 0
2 - 2 - 600 - 80 - 30
3 - 3 - 300 - 0 - 40
1 - 2 - 400 - 10 - 0
3 - 2 - 600 - 80 - 30
3 - 1 - 200 - 0 - 10
2 - 4 - 200 - 60 - 0

table: collections
id_user - valeur
1 - 200
2 - 100

Donc ce que je voudrais, c'est un tableau qui me retourne le classement des utilisateurs avec le score correspondant à (score_principal(gain+bonus-perte) + collection(valeur))
En fait le but, c'est d'additionner le score de chaque jeu d'un utilisateur + la valeur d'une collection si il l'a obtenu et de le classer par rapport aux autres utilisateurs.
ce qui donnerai quelque chose comme cela:

rang - name - score(gain+bonus-perte)
1 - joe - 1910
2 - bob - 840
3 - tom - 260
3 - sam - 260

Et bien sûr, le code doit permettre de diviser le tableau sur plusieurs page.
Auriez vous une solution?
J'ai tout essayé, jusqu'à y mettre des variables "@variable", mais je n'y arrive pas.
merci beaucoup!

[Seb33300]

ça devrait ressembler à :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
	(SELECT COUNT(SUM(gain + bonus - perte)) + 1 FROM scores GROUP BY name WHERE score > s.score) AS 'rang'
	s.name,
	SUM(s.gain + s.bonus - s.perte) as score
 
FROM 
	scores as s
 
GROUP BY 
	s.name
 
ORDER BY 
	s.position

[tecxor]

Bonjour Seb33300,
Merci pour la reponse rapide. Malheureusement, j'ai déjà essayé, mais ça ne marche pas. De plus dans ton code, tu as mis:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

...name WHERE score > s.score)...

mais s.score ne correspond à rien.

En fait, c'est même plus compliqué que ça, je reformule mes tables dans l’énoncé en haut de page.

[Fred_34]

Si tu lances cette requete :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT utilisateurs.pseudo, score1+ COALESCE(valeur,0) AS score FROM
(SELECT id_user,SUM(gain)+SUM(bonus)-SUM(perte) AS score1 from score_principal group by id_user) AS T
LEFT JOIN collections
ON collections.id_user=T.id_user
INNER JOIN utilisateurs	
ON utilisateurs.id_user=T.id_user
ORDER BY score DESC

Tu auras :

joe - 1910
bob - 840
tom - 260
sam - 260

Apres tu peux peut-être gérer le classement dans ton application car sinon, il faut faire un truc pas du tout optimisé dans ce style:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT rang,T.pseudo,score FROM(
SELECT utilisateurs.pseudo, score1+ COALESCE(valeur,0) AS score, (SELECT COUNT(T.scoreT)+1 FROM (    
SELECT score1+ COALESCE(valeur,0) AS scoreT FROM
(SELECT id_user,SUM(gain)+SUM(bonus)-SUM(perte) AS score1 from score_principal group by id_user) AS T
LEFT JOIN collections
ON collections.id_user=T.id_user
) T
WHERE T.scoreT>score) AS rang FROM
 
(SELECT id_user,SUM(gain)+SUM(bonus)-SUM(perte) AS score1 from score_principal group by id_user) AS T
LEFT JOIN collections
ON collections.id_user=T.id_user
INNER JOIN utilisateurs	
ON utilisateurs.id_user=T.id_user
) T
ORDER BY rang ASC

Désolé, j'ai pas trop fait d'effort sur l'indentation.

[tecxor]

bonjour Fred_34,
Ta première solution avec la gestion en php, j'ai déjà essayé, mais je veux éviter.
Par contre ta deuxième solution m'intéresse beaucoup plus et m'a l'air pas mal.
J'étais partie sur ce genre d'architecture, mais je l'ai moins bien géré que toi!

Je vais essayer ce code ce soir!
Je te retiens au courant!

Merci beaucoup !

[tecxor]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT rang,T.pseudo,score FROM(
SELECT utilisateurs.pseudo, score1+ COALESCE(valeur,0) AS score, (SELECT COUNT(T.scoreT)+1 FROM (    
SELECT score1+ COALESCE(valeur,0) AS scoreT FROM
(SELECT id_user,SUM(gain)+SUM(bonus)-SUM(perte) AS score1 from score_principal group by id_user) AS T
LEFT JOIN collections
ON collections.id_user=T.id_user
) T
WHERE T.scoreT>score) AS rang FROM
 
(SELECT id_user,SUM(gain)+SUM(bonus)-SUM(perte) AS score1 from score_principal group by id_user) AS T
LEFT JOIN collections
ON collections.id_user=T.id_user
INNER JOIN utilisateurs	
ON utilisateurs.id_user=T.id_user
) T
ORDER BY rang ASC

Désolé, j'ai pas trop fait d'effort sur l'indentation.

Super!! Ca fonctionne parfaitement!
Par contre, j'ai oublié de préciser que dans la table collection, un utilisateur peut avoir plusieurs collections, donc plusieurs valeurs.

En fait, en réalité j'ai une table supplémentaire:
donc j'ai ma table collection avec un id_cat et un id_user

table: collections
id_user - id_cat
1 - 1
2 - 1
1 - 2

Et une autre table avec les valeurs des collections

table: collections_valeur
id_cat - valeur
1 - 200
2 - 100



Je n'ai pas l'habitude de programmer de cette façon en Sql, donc comment dois-je faire pour insérer le SUM( ) des valeurs de collection par utilisateur dans le code?

[tecxor]

De plus, j'ai remarqué que le classement était du type:

rang - pseudo - score
1 - bob - 1910
2 - joe - 840
3 - tom - 260
3 - sam - 260
5 - ... - 200

Je souhaiterai plutôt qu'il soit de ce type:


rang - pseudo - score
1 - bob - 1910
2 - joe - 840
3 - tom - 260
3 - sam - 260
4 - ... 200

J'ai essayé avec "T.scoreT>=score", mais forcément, ça ne marche pas.

[tecxor]

De plus, j'ai remarqué que le classement était du type:

rang - pseudo - score
1 - bob - 1910
2 - joe - 840
3 - tom - 260
3 - sam - 260
5 - ... - 200

Je souhaiterai plutôt qu'il soit de ce type:


rang - pseudo - score
1 - bob - 1910
2 - joe - 840
3 - tom - 260
3 - sam - 260
4 - ... 200

J'ai essayé avec "T.scoreT>=score", mais forcément, ça ne marche pas.

Ca y est j'ai trouvé pour le type de classement il suffisait de mettre "COUNT( DISTINCT T.scoreT)" et "T.scoreT>=score"

ce qui donne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
SELECT rang,T.pseudo,score
FROM(
	SELECT utilisateurs.pseudo, score1 AS score, 
 
	(SELECT 
	COUNT(DISTINCT T.scoreT)
	FROM (
		SELECT score1 AS scoreT
		FROM (
			SELECT id_user,SUM(gain)+SUM(bonus)-SUM(perte) AS score1
			FROM score_principal GROUP BY id_user
		) AS T
	) T
	WHERE T.scoreT>=score
	) AS rang
 
	FROM (
		SELECT id_user,SUM(gain)+SUM(bonus)-SUM(perte) AS score1 
		FROM score_principal
		GROUP BY id_user
	) AS T
	LEFT JOIN utilisateurs	
	ON utilisateurs.id_user=T.id_user
) T
ORDER BY rang ASC

Par contre je suis toujours bloqué avec ma jointure et la somme des valeur de la table: collections et la table : collection_valeur.

[Seb33300]

Pour la requete que j'ai donné, tu peux remplacer la ligne par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

(SELECT COUNT(SUM(gain + bonus - perte)) + 1 FROM scores GROUP BY name WHERE (SUM(gain + bonus - perte)) > SUM(s.gain + s.bonus - s.perte)) AS 'rang'

[Fred_34]

C'est tellement compliqué que je commence à douter que ce soit la bonne façon de faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
SELECT rang,pseudo,score
FROM(
SELECT T.pseudo, T.score,(SELECT COUNT(DISTINCT T.scoreT)+1 FROM (    
SELECT  score1+ COALESCE(SUM(valeur),0) AS scoreT FROM
(SELECT id_user,SUM(gain)+SUM(bonus)-SUM(perte) AS score1 FROM score_principal GROUP BY id_user) AS T
LEFT JOIN collections
ON collections.id_user=T.id_user
LEFT JOIN collections_valeur	
ON collections.id_cat=collections_valeur.id_cat
GROUP BY T.id_user
) T
WHERE T.scoreT>score) AS rang FROM (    
SELECT utilisateurs.pseudo, score1+ COALESCE(SUM(valeur),0) AS score FROM
 
(SELECT id_user,SUM(gain)+SUM(bonus)-SUM(perte) AS score1 FROM score_principal GROUP BY id_user) AS T
LEFT JOIN collections
ON collections.id_user=T.id_user
LEFT JOIN collections_valeur	
ON collections.id_cat=collections_valeur.id_cat
INNER JOIN utilisateurs	
ON utilisateurs.id_user=T.id_user
GROUP BY T.id_user
) T
) T
ORDER BY rang ASC

[tecxor]

Pour la requete que j'ai donné, tu peux remplacer la ligne par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

(SELECT COUNT(SUM(gain + bonus - perte)) + 1 FROM scores GROUP BY name WHERE (SUM(gain + bonus - perte)) > SUM(s.gain + s.bonus - s.perte)) AS 'rang'

Merci, mais on ne peut pas mettre de SUM() dans un WHERE. Deja essayé

[tecxor]

C'est tellement compliqué que je commence à douter que ce soit la bonne façon de faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
SELECT rang,pseudo,score
FROM(
SELECT T.pseudo, T.score,(SELECT COUNT(DISTINCT T.scoreT)+1 FROM (    
SELECT  score1+ COALESCE(SUM(valeur),0) AS scoreT FROM
(SELECT id_user,SUM(gain)+SUM(bonus)-SUM(perte) AS score1 FROM score_principal GROUP BY id_user) AS T
LEFT JOIN collections
ON collections.id_user=T.id_user
LEFT JOIN collections_valeur	
ON collections.id_cat=collections_valeur.id_cat
GROUP BY T.id_user
) T
WHERE T.scoreT>score) AS rang FROM (    
SELECT utilisateurs.pseudo, score1+ COALESCE(SUM(valeur),0) AS score FROM
 
(SELECT id_user,SUM(gain)+SUM(bonus)-SUM(perte) AS score1 FROM score_principal GROUP BY id_user) AS T
LEFT JOIN collections
ON collections.id_user=T.id_user
LEFT JOIN collections_valeur	
ON collections.id_cat=collections_valeur.id_cat
INNER JOIN utilisateurs	
ON utilisateurs.id_user=T.id_user
GROUP BY T.id_user
) T
) T
ORDER BY rang ASC

Houlala, oui, ça devient une vrai usine à gaz, mais je ne vois pas comment faire autrement en sql.
J'essaie ton conde tout de suite!
En tout cas merci beaucoup!!

[tecxor]

Merci beaucoup!!
Ca marche parfaitement!!
T'es vraiment bon !!
Je ne sais pas comment te remercier!
Encore merci!


PS: je ne sais pas comment mettre que ce sujet est résolu