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 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193
| SELECT id AS id,
(select count(*)
from MANU_results
where state=1
and course_fk = (select course_fk
from MANU_results
where id = 2602
)
) AS nb_coureurs,
classement_general AS rg,
chrono_final AS tps,
dossard AS dossard,
nom AS nom,
club AS club,
categorie AS cat,
rang_dans_la_categorie AS rg_cat,
user_id AS user_id,
temps_natation AS nat,
rang_natation AS rg_nat,
temps_t1 AS t1,
rang_t1 AS rg_t1,
Addtime(temps_natation, temps_t1) AS tps_t1,
rang_issuet1 AS cl_t1,
rang_issuet1 - rang_natation AS gain_t1,
temps_velo AS velo,
rang_velo AS rg_velo,
Addtime(Addtime(temps_natation, temps_t1), temps_velo) AS tps_velo,
rang_issuevelo AS cl_velo,
rang_issuevelo - rang_issuet1 AS gain_velo,
temps_t2 AS t2,
rang_t2 AS rg_t2,
Addtime(Addtime(Addtime(temps_natation, temps_t1), temps_velo), temps_t1) AS tps_t2,
rang_issuet2 AS cl_t2,
rang_issuet2 - rang_issuevelo AS gain_t2,
temps_cap AS cap,
rang_cap AS rg_cap,
Addtime(Addtime(Addtime(Addtime(temps_natation, temps_t1), temps_velo), temps_t1), temps_cap) AS tps_cap,
rang_issuecap AS cl_cap,
rang_issuecap - rang_issuet2 AS gain_cap
FROM (SELECT a.id,
a.classement_general,
a.chrono_final,
a.dossard,
a.nom,
a.club,
a.categorie,
a.rang_dans_la_categorie,
a.user_id,
a.temps_natation,
rank_natation.rang_natation,
a.temps_t1,
rank_t1.rang_t1,
rank_issuet1.rang_issuet1,
a.temps_velo,
rank_velo.rang_velo,
rank_issuevelo.rang_issuevelo,
a.temps_t2,
rank_t2.rang_t2,
rank_issuet2.rang_issuet2,
a.temps_cap,
rank_cap.rang_cap,
rank_issuecap.rang_issuecap
FROM MANU_results AS a
LEFT JOIN (SELECT p.dossard,
@curranknat := @curranknat + 1 AS rang_natation
FROM MANU_results p,
(SELECT @curranknat := 0) AS r1
WHERE p.course_fk = (SELECT course_fk
FROM MANU_results
WHERE id = 2602
)
AND p.temps_natation != '00:00:00'
ORDER BY temps_natation, p.dossard
) AS rank_natation
ON rank_natation.dossard = a.dossard
LEFT JOIN (SELECT p1.dossard,
@currankt1 := @currankt1 + 1 AS rang_t1
FROM MANU_results p1,
(SELECT @currankt1 := 0) AS r1
WHERE p1.course_fk = (SELECT course_fk
FROM MANU_results
WHERE id = 2602
)
AND p1.temps_t1 != '00:00:00'
ORDER BY temps_t1, p1.dossard
) AS rank_t1
ON rank_t1.dossard = a.dossard
LEFT JOIN (SELECT p5.dossard,
@currankissuet1 := @currankissuet1 + 1 AS rang_issuet1
FROM MANU_results p5,
(SELECT @currankissuet1 := 0) AS r1
WHERE p5.course_fk = (SELECT course_fk
FROM MANU_results
WHERE id = 2602
)
AND p5.temps_natation != '00:00:00'
ORDER BY Addtime(temps_natation, temps_t1), p5.dossard) AS rank_issuet1
ON rank_issuet1.dossard = a.dossard
LEFT JOIN (SELECT p2.dossard,
@currankvelo := @currankvelo + 1 AS rang_velo
FROM MANU_results p2,
(SELECT @currankvelo := 0) AS r1
WHERE p2.course_fk = (SELECT course_fk
FROM MANU_results
WHERE id = 2602
)
AND p2.temps_velo != '00:00:00'
ORDER BY p2.temps_velo, p2.dossard
) AS rank_velo
ON rank_velo.dossard = a.dossard
LEFT JOIN (SELECT p6.dossard,
@currankissuevelo := @currankissuevelo + 1 AS rang_issuevelo
FROM MANU_results p6,
(SELECT @currankissuevelo := 0) AS r1
WHERE p6.course_fk = (SELECT course_fk
FROM MANU_results
WHERE id = 2602
)
AND p6.temps_velo != '00:00:00'
AND p6.temps_natation != '00:00:00'
ORDER BY Addtime(Addtime(temps_natation, temps_t1), temps_velo), p6.dossard
) AS rank_issuevelo
ON rank_issuevelo.dossard = a.dossard
LEFT JOIN (SELECT p3.dossard,
@currankt2 := @currankt2 + 1 AS rang_t2
FROM MANU_results p3,
(SELECT @currankt2 := 0) AS r1
WHERE p3.course_fk = (SELECT course_fk
FROM MANU_results
WHERE id = 2602
)
AND p3.temps_t2 != '00:00:00'
ORDER BY p3.temps_t2, p3.dossard
) AS rank_t2
ON rank_t2.dossard = a.dossard
LEFT JOIN (SELECT p7.dossard,
@currankissuet2 := @currankissuet2 + 1 AS rang_issuet2
FROM MANU_results p7,
(SELECT @currankissuet2 := 0) AS r1
WHERE p7.course_fk = (SELECT course_fk
FROM MANU_results
WHERE id = 2602
)
AND p7.temps_velo != '00:00:00'
AND p7.temps_natation != '00:00:00'
ORDER BY Addtime(Addtime(Addtime(temps_natation, temps_t1), temps_velo), temps_t2), p7.dossard
) AS rank_issuet2
ON rank_issuet2.dossard = a.dossard
LEFT JOIN (SELECT p4.dossard,
@currankcap := @currankcap + 1 AS rang_cap
FROM MANU_results p4,
(SELECT @currankcap := 0) AS r1
WHERE p4.course_fk = (SELECT course_fk
FROM MANU_results
WHERE id = 2602
)
AND p4.temps_cap != '00:00:00'
ORDER BY temps_cap, p4.dossard
) AS rank_cap
ON rank_cap.dossard = a.dossard
LEFT JOIN (SELECT p8.dossard,
@currankissuecap := @currankissuecap + 1 AS rang_issuecap
FROM MANU_results p8,
(SELECT @currankissuecap := 0) AS r1
WHERE p8.course_fk = (SELECT course_fk
FROM MANU_results
WHERE id = 2602
)
AND p8.temps_velo != '00:00:00'
AND p8.temps_natation != '00:00:00'
AND p8.temps_cap != '00:00:00'
ORDER BY Addtime(Addtime(Addtime(Addtime(temps_natation, temps_t1), temps_velo ), temps_t2), temps_cap), p8.dossard
) AS rank_issuecap
ON rank_issuecap.dossard = a.dossard
WHERE a.course_fk = (SELECT course_fk
FROM MANU_results
WHERE id = 2602
)
AND a.state = 1
) AS detail
where detail.id = 2602; |
Partager