Discussion :

[cignus77]

Bonjour,
j'ai une base de donnée avec 3 tables :
jos_pokerplayers (id, nickname)
jos_pokerpoints (id_event, id_player, rank, tournamentpts)
jos_pokerevents (id, date)

Je fais une requete pour afficher :
id_player : nickname : somme des points

cela donne cela :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT Po.id_player, Pl.nickname, SUM( Po.tournamentpts ) AS PlayerPoints
FROM jos_pokerpoints AS Po
JOIN jos_pokerplayers AS Pl ON Po.id_player = Pl.id
JOIN jos_pokerevents AS Ev ON Po.id_event = Ev.id
WHERE 1 
GROUP BY Po.id_player
ORDER BY  `PlayerPoints` DESC

Cela fonctionne bien... cela me calcule bien la somme de tous les points pour chaque player...

Seulement, maintenant, je voudrais ne faire la somme que sur les 10 plus grands scores de chaque player

CEla fait 3h que je triture les requetes dans tous les sens, avec des requetes imbriquées etc... Je n'y arrive pas.
J'ai essayé un truc du style :

Code :

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

1
2
3
4
5
6
7
SELECT Po.id_player, Pl.nickname, SUM((SELECT tournamentpts FROM jos_pokerpoints ORDER BY tournamentpts DESC LIMIT 15)) AS PlayerPoints
FROM jos_pokerpoints AS Po
JOIN jos_pokerplayers AS Pl ON Po.id_player = Pl.id
JOIN jos_pokerevents AS Ev ON Po.id_event = Ev.id
WHERE 1 
GROUP BY Po.id_player
ORDER BY  `PlayerPoints` DESC

mais cela me dit:
"#1242 - Subquery returns more than 1 row "

J'ai essayé d'autres choses mais sans résultats...

Comment faire ?

Merci.

[ced]

Bonjour,

Voici une requête qui devrait faire ce que tu souhaites. A tester, je n'ai pas essayé :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT id_player, nickname, SUM(tournamentpts) AS PlayerPoints
FROM (
    SELECT pl.id AS id_player, pl.nickname, po.tournamentpts, COUNT(*) AS ordre
    FROM jos_pokerplayers pl
    INNER JOIN jos_pokerpoints po ON pl.id = po.id_player
    INNER JOIN jos_pokerpoints po2 ON po.id_player = po2.id_player AND po.tournamentpts <= po2.tournamentpts
    GROUP BY pl.id, pl.nickname, po.tournamentpts
) AS t
WHERE t.ordre <= 10
GROUP BY id_player, nickname

ced

[cignus77]

Bonjour,

Voici une requête qui devrait faire ce que tu souhaites. A tester, je n'ai pas essayé :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT id_player, nickname, SUM(tournamentpts) AS PlayerPoints
FROM (
    SELECT pl.id AS id_player, pl.nickname, po.tournamentpts, COUNT(*) AS ordre
    FROM jos_pokerplayers pl
    INNER JOIN jos_pokerpoints po ON pl.id = po.id_player
    INNER JOIN jos_pokerpoints po2 ON po.id_player = po2.id_player AND po.tournamentpts <= po2.tournamentpts
    GROUP BY pl.id, pl.nickname, po.tournamentpts
) AS t
WHERE t.ordre <= 10
GROUP BY id_player, nickname

ced

Merci !!!!
Effectivement cela marche.... mais que c'est compliqué... je comprends pas bien comment cela fonctionne...

Et pour compliquer la chose :
je veux maintenant afficher :
id_player, nickname, nombre de points sur les 10 meilleurs scores, nombre total de parties jouées.

La requête originale était la suivante :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT Po.id_player, Pl.nickname, SUM( IF( Po.rank =1, 1, 0 ) ) AS Wins, SUM( IF( Po.rank
BETWEEN 1 
AND 9 , 1, 0 ) ) AS FinalTables, SUM( Po.tournamentpts ) AS PlayerPoints, COUNT( Po.id_event ) AS GamesPlayed
FROM jos_pokerpoints AS Po
JOIN jos_pokerplayers AS Pl ON Po.id_player = Pl.id
JOIN jos_pokerevents AS Ev ON Po.id_event = Ev.id
WHERE 1 
GROUP BY Po.id_player
ORDER BY  `PlayerPoints` DESC

Comment modifier cela pour n'afficher que la somme des 10 plus gros scores, sans toucher au reste ?

Encore merci!

[ced]

Effectivement, dès qu'on demande des infos du type : "les x plus grands parmi N", les requêtes deviennent plus complexes, surtout sous MySQL qui ne dispose pas des fonctions fenêtres (qui sont bien utiles pour gérer ce genre de problème).
Donc, on peut modifier la requête précédente dans ce sens (toujours à tester, je n'ai pas essayé) :

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
SELECT id_player, nickname, SUM(tournamentpts) AS PlayerPoints
FROM (
    SELECT pl.id AS id_player, pl.nickname, po.tournamentpts, COUNT(*) AS ordre
    FROM jos_pokerplayers pl
    INNER JOIN jos_pokerpoints po ON pl.id = po.id_player
    INNER JOIN jos_pokerpoints po2 ON po.id_player = po2.id_player AND po.tournamentpts <= po2.tournamentpts
    GROUP BY pl.id, pl.nickname, po.tournamentpts
) AS t
INNER JOIN (
    SELECT pl.id AS id_player
    , SUM(IF( Po.rank =1, 1, 0 )) AS Wins
    , SUM(IF( Po.rank BETWEEN 1 AND 9 , 1, 0 )) AS FinalTables
    , SUM( Po.tournamentpts ) AS PlayerPoints, COUNT( Po.id_event ) AS GamesPlayed
    FROM jos_pokerplayers pl
    INNER JOIN jos_pokerpoints po ON pl.id = po.id_player
) AS t2 ON t.id_player = t2.id_player
WHERE t.ordre <= 10
GROUP BY id_player, nickname

Par contre, ça commence à faire beaucoup de requêtes imbriquées et gare à la dégradation des performances...

ced

[cignus77]

Merci... je teste cela tout de suite...

[cignus77]

Bon, cela fonctionne pas...
Il y a des ambiguités visiblement dans les noms de champs...

"#1052 - Column 'id_player' in field list is ambiguous "

[ced]

Ah oui, dans le GROUP BY :

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
SELECT id_player, nickname, SUM(tournamentpts) AS PlayerPoints
FROM (
    SELECT pl.id AS id_player, pl.nickname, po.tournamentpts, COUNT(*) AS ordre
    FROM jos_pokerplayers pl
    INNER JOIN jos_pokerpoints po ON pl.id = po.id_player
    INNER JOIN jos_pokerpoints po2 ON po.id_player = po2.id_player AND po.tournamentpts <= po2.tournamentpts
    GROUP BY pl.id, pl.nickname, po.tournamentpts
) AS t
INNER JOIN (
    SELECT pl.id AS id_player
    , SUM(IF( Po.rank =1, 1, 0 )) AS Wins
    , SUM(IF( Po.rank BETWEEN 1 AND 9 , 1, 0 )) AS FinalTables
    , SUM( Po.tournamentpts ) AS PlayerPoints, COUNT( Po.id_event ) AS GamesPlayed
    FROM jos_pokerplayers pl
    INNER JOIN jos_pokerpoints po ON pl.id = po.id_player
) AS t2 ON t.id_player = t2.id_player
WHERE t.ordre <= 10
GROUP BY t.id_player, t.nickname

[cignus77]

J'ai modifié le code pour ajuster tous les Po avec po...

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
ROM (
    SELECT pl.id AS id_player, pl.nickname, po.tournamentpts, COUNT(*) AS ordre
    FROM jos_pokerplayers pl
    INNER JOIN jos_pokerpoints po ON pl.id = po.id_player
    INNER JOIN jos_pokerpoints po2 ON po.id_player = po2.id_player AND po.tournamentpts <= po2.tournamentpts
    GROUP BY pl.id, pl.nickname, po.tournamentpts
) AS t
INNER JOIN (
    SELECT pl.id AS id_player
    , SUM(IF( po.rank =1, 1, 0 )) AS Wins
    , SUM(IF( po.rank BETWEEN 1 AND 9 , 1, 0 )) AS FinalTables
    , SUM( po.tournamentpts ) AS PlayerPoints, COUNT( po.id_event ) AS GamesPlayed
    FROM jos_pokerplayers pl
    INNER JOIN jos_pokerpoints po ON pl.id = po.id_player
) AS t2 ON t.id_player = t2.id_player
WHERE t.ordre <= 10
GROUP BY t.id_player, t.nickname

Mais on a toujours le même problème...

Si je comprends l'idée : il faut faire une sélection sur une table que l'on crée avec des jointures... la table à créer doit avoir ces champs :
id_player | nickname | id_event | rank | tournamentpts | ten_Max_Points

la colonne Ten_Max_Point étant créée à partir d'une requête sur la table pokerpoints triée par id_players ET limitée à 10 enregistrements ?
Les cases vides par rapport à la colonne tournamentpts sont remplies par des NULL en utilisant un LEFTJOIN... non ?

[ced]

Ah, j'ai oublié de mettre dans le SELECT les autres colonnes...
Essaie comme ça :

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
SELECT t.id_player, t.nickname, SUM(t.tournamentpts) AS PlayerPoints, t2.Wins, t2.FinalTables, t2.PlayerPoints, t2.GamesPlayed
FROM (
    SELECT pl.id AS id_player, pl.nickname, po.tournamentpts, COUNT(*) AS ordre
    FROM jos_pokerplayers pl
    INNER JOIN jos_pokerpoints po ON pl.id = po.id_player
    INNER JOIN jos_pokerpoints po2 ON po.id_player = po2.id_player AND po.tournamentpts <= po2.tournamentpts
    GROUP BY pl.id, pl.nickname, po.tournamentpts
) AS t
INNER JOIN (
    SELECT pl.id AS id_player
    , SUM(IF( Po.rank =1, 1, 0 )) AS Wins
    , SUM(IF( Po.rank BETWEEN 1 AND 9 , 1, 0 )) AS FinalTables
    , SUM( Po.tournamentpts ) AS PlayerPoints
    , COUNT( Po.id_event ) AS GamesPlayed
    FROM jos_pokerplayers pl
    INNER JOIN jos_pokerpoints po ON pl.id = po.id_player
) AS t2 ON t.id_player = t2.id_player
WHERE t.ordre <= 10
GROUP BY t.id_player, t.nickname, t2.Wins, t2.FinalTables, t2.PlayerPoints, t2.GamesPlayed

Si ça ne fonctionne pas, peux-tu donner un jeu de données pour tester (un extrait des tables) ?

ced

[cignus77]

Cela ne fonctionne toujours pas...
J'essaye de mon coté de reprendre la requete progressivement (en fait je débute en SQL) pour comprendre ce qu'elle fait au fur et a mesure...

voici les tables en fichier joint...
Si tu veux t'amuser

Merci en tout cas de ton aide.

[cignus77]

pourquoi est-ce qu'un truc du style ne fonctionne pas ?

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
SELECT x.player_id AS ident, x.player_nick AS nick, COUNT(x.event) AS total_event, SUM(y.tpoints) AS total_points
FROM
(SELECT po.id_player AS player_id, pl.nickname AS player_nick, po.id_event AS event, po.rank AS player_rank, po.tournamentpts AS points
FROM jos_pokerpoints AS po
JOIN jos_pokerplayers AS pl ON po.id_player = pl.id
JOIN jos_pokerevents AS ev ON po.id_event = ev.id
GROUP BY po.id_player, event
ORDER BY player_id ASC , points DESC) AS x
LEFT JOIN (
SELECT t.player_id AS id, t.player_nick AS name, t.event AS tevent, t.player_rank AS trank, t.points AS tpoints
FROM (
SELECT po1.id_player AS player_id, pl.nickname AS player_nick, po1.id_event AS event, po1.rank AS player_rank, po1.tournamentpts AS points, COUNT(*) AS ordre
FROM jos_pokerpoints AS po1
JOIN jos_pokerplayers AS pl ON po.id_player = pl.id
JOIN jos_pokerevents AS ev ON po.id_event = ev.id
JOIN jos_pokerpoints AS po2 ON po1.id_player = po2.id_player AND po1.tournamentpts <= po2.tournamentpts
GROUP BY po1.id_player, event
ORDER BY player_id ASC , points DESC) AS t
WHERE t.ordre <=5
GROUP BY id, tevent
ORDER BY id ASC) AS y ON x.player_id = y.id
GROUP BY ident, nick
ORDER BY total_points DESC

[cignus77]

J'ai essayé cela :

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
SELECT x.player_id AS ident, x.player_nick AS nick, x.event, y.tpoints, x.points
FROM
(SELECT po.id_player AS player_id, pl.nickname AS player_nick, po.id_event AS event, po.rank AS player_rank, po.tournamentpts AS points
FROM jos_pokerpoints AS po
JOIN jos_pokerplayers AS pl ON po.id_player = pl.id
JOIN jos_pokerevents AS ev ON po.id_event = ev.id
GROUP BY po.id_player, event
ORDER BY player_id ASC , points DESC) AS x
LEFT JOIN (
SELECT t.player_id AS id, t.player_nick AS name, t.event AS tevent, t.player_rank AS trank, t.points AS tpoints
FROM (
SELECT po1.id_player AS player_id, pl.nickname AS player_nick, po1.id_event AS event, po1.rank AS player_rank, po1.tournamentpts AS points, COUNT(*) AS ordre
FROM jos_pokerpoints AS po1
JOIN jos_pokerplayers AS pl ON po1.id_player = pl.id
JOIN jos_pokerevents AS ev ON po1.id_event = ev.id
JOIN jos_pokerpoints AS po2 ON po1.id_player = po2.id_player AND po1.tournamentpts <= po2.tournamentpts
GROUP BY po1.id_player, event
ORDER BY player_id ASC , points DESC) AS t
WHERE t.ordre <=5
GROUP BY id, tevent
ORDER BY id ASC) AS y
ON x.player_id = y.id
GROUP BY ident, nick, x.event
ORDER BY ident ASC, x.points DESC

mais il me met toujours la même valeur dans la colonne t.points

ident nick event tpoints points
1 Regiseis 12 102 113
1 Regiseis 9 102 102
1 Regiseis 184 102 90
1 Regiseis 15 102 75
1 Regiseis 14 102 68
1 Regiseis 66 102 49
1 Regiseis 31 102 41
1 Regiseis 89 102 25
1 Regiseis 34 102 22
1 Regiseis 65 102 7
1 Regiseis 8 102 4
1 Regiseis 75 102 1
1 Regiseis 127 102 1
1 Regiseis 26 102 1
1 Regiseis 129 102 1
1 Regiseis 146 102 1
1 Regiseis 33 102 1
1 Regiseis 73 102 1
1 Regiseis 131 102 1
1 Regiseis 194 102 1
1 Regiseis 162 102 1
1 Regiseis 191 102 1
1 Regiseis 23 102 1
1 Regiseis 17 102 1
1 Regiseis 24 102 1
1 Regiseis 20 102 1
1 Regiseis 132 102 1
1 Regiseis 207 102 1
1 Regiseis 81 102 1
1 Regiseis 41 102 1
1 Regiseis 155 102 1
1 Regiseis 16 102 1
1 Regiseis 13 102 1
1 Regiseis 93 102 1
1 Regiseis 121 102 1
1 Regiseis 117 102 1
1 Regiseis 5 102 1
1 Regiseis 11 102 1
2 Marcomotard 23 118 236
2 Marcomotard 141 118 183
2 Marcomotard 119 118 178
2 Marcomotard 25 118 124
2 Marcomotard 17 118 118
2 Marcomotard 77 118 109
2 Marcomotard 127 118 109
2 Marcomotard 172 118 104
2 Marcomotard 138 118 104
2 Marcomotard 149 118 104
2 Marcomotard 54 118 104
2 Marcomotard 15 118 96
2 Marcomotard 103 118 90
2 Marcomotard 207 118 88
2 Marcomotard 171 118 84
2 Marcomotard 173 118 72
2 Marcomotard 185 118 53
2 Marcomotard 85 118 53
2 Marcomotard 147 118 52
2 Marcomotard 79 118 52
2 Marcomotard 59 118 52
2 Marcomotard 105 118 52
2 Marcomotard 208 118 49
2 Marcomotard 206 118 44
2 Marcomotard 116 118 42
2 Marcomotard 13 118 39
2 Marcomotard 84 118 38
2 Marcomotard 135 118 37
2 Marcomotard 75 118 23
2 Marcomotard 150 118 23
2 Marcomotard 39 118 23
2 Marcomotard 30 118 22
2 Marcomotard 129 118 20
2 Marcomotard 66 118 20
2 Marcomotard 202 118 7
2 Marcomotard 9 118 4
2 Marcomotard 87 118 1
2 Marcomotard 146 118 1
2 Marcomotard 189 118 1
2 Marcomotard 144 118 1
2 Marcomotard 194 118 1
2 Marcomotard 33 118 1
2 Marcomotard 93 118 1
2 Marcomotard 26 118 1
2 Marcomotard 88 118 1
2 Marcomotard 123 118 1
2 Marcomotard 155 118 1
2 Marcomotard 121 118 1
2 Marcomotard 175 118 1
2 Marcomotard 24 118 1
2 Marcomotard 83 118 1
2 Marcomotard 196 118 1
2 Marcomotard 76 118 1
2 Marcomotard 131 118 1

[ced]

Pour l'instant, j'ai réécris ma première proposition de requête comme ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT pl.id AS id_player, pl.nickname, po.tournamentpts
FROM jos_pokerplayers pl
INNER JOIN jos_pokerpoints po ON pl.id = po.id_player
WHERE (
  SELECT COUNT(*)
  FROM jos_pokerpoints po2
  WHERE (po.id_player = po2.id_player
  AND po.tournamentpts < po2.tournamentpts)
  OR (po.id_player = po2.id_player
  AND po.tournamentpts = po2.tournamentpts
  AND po.id_event < po2.id_event)
) < 10

Ça semble fonctionner...

Reste à ajouter les sommes...

ced

[ced]

Ça pourrait donner la requête suivante :

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
SELECT pl.id AS id_player, pl.nickname, po.tournamentpts
, t2.Wins, t2.FinalTables, t2.PlayerPoints, t2.GamesPlayed
FROM jos_pokerplayers pl
INNER JOIN jos_pokerpoints po ON pl.id = po.id_player
INNER JOIN (
  SELECT pl.id AS id_player
  , SUM(IF( Po.rank =1, 1, 0 )) AS Wins
  , SUM(IF( Po.rank BETWEEN 1 AND 9 , 1, 0 )) AS FinalTables
  , SUM( Po.tournamentpts ) AS PlayerPoints
  , COUNT( Po.id_event ) AS GamesPlayed
  FROM jos_pokerplayers pl
  INNER JOIN jos_pokerpoints po ON pl.id = po.id_player
  GROUP BY pl.id
) AS t2 ON pl.id = t2.id_player
WHERE (
  SELECT COUNT(*)
  FROM jos_pokerpoints po2
  WHERE (po.id_player = po2.id_player
  AND po.tournamentpts < po2.tournamentpts)
  OR (po.id_player = po2.id_player
  AND po.tournamentpts = po2.tournamentpts
  AND po.id_event < po2.id_event)
) < 10

Attention, ça fait beaucoup de sous-requêtes et les performances sont plus que moyennes avec le jeu d'essai que tu as donné...

ced

[cignus77]

Merci Ced pour tous ces efforts...
malheureusement, cela ne fonctionne pas... Seuls les points d'un seul tournoi apparaissent dans la colonne "tounamentpts"...

Je cherche de mon coté... mais je n'arrive toujours pas à afficher les points de tous les tournois en même temps que les points de seulement les n plus gros scores...

Peut être cela n'est il pas possible ...

[cignus77]

Avec ce code, j'arrive à afficher les 10 meilleurs scores pour chaque joueur...
Je pense que cela peut etre un début...
Reste à en faire la somme des 10 meilleurs (qui sont donnés par ce code) tout en comptant le total de tournoi joués (et pas seulement 10 meilleurs tournois)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT t.player_id AS id, t.player_nick AS name, t.event AS tevent, t.player_rank AS trank, t.points AS tpoints
FROM (
SELECT po.id_player AS player_id, pl.nickname AS player_nick, po.id_event AS event, po.rank AS player_rank, po.tournamentpts AS points, COUNT(*) AS ordre
FROM jos_pokerpoints AS po
JOIN jos_pokerplayers AS pl ON po.id_player = pl.id
JOIN jos_pokerevents AS ev ON po.id_event = ev.id
JOIN jos_pokerpoints AS po2 ON po.id_player = po2.id_player AND po.tournamentpts <= po2.tournamentpts
GROUP BY po.id_player, event
ORDER BY player_id ASC , points DESC) AS t
WHERE t.ordre <=10
GROUP BY id, tevent
ORDER BY id ASC, tpoints DESC

[cignus77]

Avec ce code, j'arrive à afficher les 10 meilleurs scores pour chaque joueur...
Je pense que cela peut etre un début...
Reste à en faire la somme des 10 meilleurs (qui sont donnés par ce code) tout en comptant le total de tournoi joués (et pas seulement 10 meilleurs tournois)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT t.player_id AS id, t.player_nick AS name, t.event AS tevent, t.player_rank AS trank, t.points AS tpoints
FROM (
SELECT po.id_player AS player_id, pl.nickname AS player_nick, po.id_event AS event, po.rank AS player_rank, po.tournamentpts AS points, COUNT(*) AS ordre
FROM jos_pokerpoints AS po
JOIN jos_pokerplayers AS pl ON po.id_player = pl.id
JOIN jos_pokerevents AS ev ON po.id_event = ev.id
JOIN jos_pokerpoints AS po2 ON po.id_player = po2.id_player AND po.tournamentpts <= po2.tournamentpts
GROUP BY po.id_player, event
ORDER BY player_id ASC , points DESC) AS t
WHERE t.ordre <=10
GROUP BY id, tevent
ORDER BY id ASC, tpoints DESC

Je ne comprends pas....
cette requête fonctionne à moitié... sur certains id_player, elle ne fait pas la requete sur tous les tournois, mais certains id_players se retrouvent avec seulement 7, 8 ou 9 tournois comptés alors qu'ils sont bien plus nombreux...

Je suis paumé là...