Commandes SQL (5)

En attendant de voir comment Guyt va intégrer ces façons de trier le classement, je vais inscrire ici les commandes SQL. Z'avez qu'à copier-coller dans la fenêtre d'entrée et soumettre.

1. Pour le classement distance totale windsurf (changez le sport si vous souhaitez ou enlevez cette condition pour tous sports confondus):
SELECT M1.NickName as 'Pseudo', SUM(R1.Distance) as 'Distance Totale' FROM results R1 LEFT JOIN members M1 ON M1.MemberID=R1.MemberID WHERE Sport='Windsurf' AND (R1.RegDate >= '2007-01-01') GROUP BY M1.memberID ORDER BY SUM(R1.Distance) DESC LIMIT 1000

= '2007-01-01') GROUP BY M1.memberID ORDER BY SUM(R1.Distance) DESC LIMIT 1000" type="hidden">

Cliquez ici pour distance totale

2. Ditance totale à nouveau, plus de détails :
SELECT M1.NickName, SUM(R1.Distance) as 'Distance Totale', ROUND(AVG(r1.distance),1) as 'Distance moyenne par sortie', COUNT(r1.distance) as '# de sorties' FROM results R1 LEFT JOIN members M1 ON M1.MemberID=R1.MemberID WHERE Sport='Windsurf' AND (R1.RegDate >= '2007-01-01') GROUP BY M1.memberID ORDER BY SUM(R1.Distance) DESC LIMIT 1000

3. Variante sur le même thème: classement selon la plus grande distance moyenne par sortie.
SELECT M1.NickName as 'Pseudo', SUM(R1.Distance) as 'Distance Totale', ROUND(AVG(r1.distance),1) as 'Distance moyenne par sortie', COUNT(r1.distance) as '# de sorties' FROM results R1 LEFT JOIN members M1 ON M1.MemberID=R1.MemberID WHERE Sport='Windsurf' AND (R1.RegDate >= '2007-01-01') GROUP BY M1.memberID ORDER BY AVG(R1.Distance) DESC LIMIT 1000

4. Pour obtenir le top 30 à 2 entrées max par planchiste (comme sur le site 2006 de l'APVM-Club GPS):
SELECT M1.NickName as 'Pseudo',R1.Spot,R1.RegDate,R1.Speed,R1.Distance,R1.BoardType,R1.BoardSize,R1.SailType,R1.SailSize,R1.Comment
FROM results R1
LEFT JOIN results R2 ON R2.MemberID = R1.MemberID
AND R1.Speed < memberid =" M1.MemberID" sport="'Windsurf'">= '2007-01-01')
GROUP BY R1.speed, R1.memberID
HAVING COUNT( R2.speed) <>
ORDER BY R1.speed DESC , R1.memberID
LIMIT 30

5. Qui ne met pas sa planche à l'eau pour rien ou qui censure ses mauvaises sorties? Ou, bon ok, qui sont nos planchistes qui vont toujours vite? Vitesse moyenne max des sorties:
SELECT M1.NickName as 'Pseudo', ROUND(AVG(r1.speed),1) as 'Vitesse moyenne par sortie', MAX(r1.speed) AS 'Vitesse Max', MIN(r1.speed) AS 'Vitesse Min', COUNT(r1.distance) as '# de sorties' FROM results R1 LEFT JOIN members M1 ON M1.MemberID=R1.MemberID WHERE Sport='Windsurf' AND (R1.RegDate >= '2007-01-01') GROUP BY M1.memberID ORDER BY AVG(R1.speed) DESC LIMIT 1000

6. Liste des membres par # de sorties de windsurf, affichage des membres sans sorties enregistrées.

(SELECT M1.NickName as 'Pseudo', COUNT(R1.Distance) as '# de sorties'
FROM results R1 LEFT JOIN members M1 ON M1.MemberID=R1.MemberID
WHERE Sport='Windsurf' AND (R1.RegDate >= '2007-01-01') GROUP BY M1.MemberID)
UNION (SELECT NickName as 'Pseudo', '0' as '# de sorties' FROM members
WHERE MemberID NOT IN (SELECT MemberID FROM results))
ORDER BY '# de sorties' DESC, 'Pseudo' ASC LIMIT 1000