-
Notifications
You must be signed in to change notification settings - Fork 0
/
skyttedbSqlListTeamResult.sql
192 lines (149 loc) · 3.59 KB
/
skyttedbSqlListTeamResult.sql
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
-- DELIMITER $$
DROP PROCEDURE IF EXISTS `ListTeamResult` $$
CREATE PROCEDURE `ListTeamResult`(in pCompId bigint)
begin
declare cur_super cursor for
select r.TeamId, s.StationId, s.Hits, s.Targets
from Result r
join tbl_Pistol_Score s on (s.EntryId = r.EntryId)
order by r.EntryId, s.StationId desc;
declare cur_nor cursor for
select s.TeamId,
t.GunClassId,
s.Hits,
s.Targets,
s.Total,
s.Points
from Result s
join tbl_Pistol_Team t on (t.Id = s.TeamId)
order by t.GunClassId, s.Total desc, s.Points desc;
declare cur_cls cursor for
select s.TeamId,
t.GunClassId,
s.Hits,
s.Targets,
s.Total,
s.Points
from Result s
join tbl_Pistol_Team t on (t.Id = s.TeamId)
order by t.GunClassId, s.Hits desc, s.Targets desc, s.Points desc;
drop temporary table if exists Result;
create temporary table Result (
TeamId bigint not null,
Hits int null,
Targets int null,
Total int null,
Points int null,
SuperScore varchar(100) null,
Place int null
);
main: begin
declare no_more_rows int default 0;
declare vCompDayId bigint;
declare vStationId smallint;
declare vMaxStation int;
declare vScoreType char(1);
declare vSuperScore varchar(100);
declare vEntryId bigint;
declare vTeamId bigint;
declare vGunClassId bigint;
declare vGroupId bigint;
declare vHits int;
declare vTargets int;
declare vTotal int;
declare vPoints int;
declare vPlace int default 0;
declare vPrevClassId bigint;
declare vPrevGroupId bigint;
declare continue handler for not found set no_more_rows = 1;
select ScoreType
into vScoreType
from tbl_Pistol_Competition
where Id = pCompId;
insert into Result (
TeamId,
Hits,
Targets,
Total,
Points
)
select e.TeamId,
-- sum( ifnull(s.Hits, 0)),
case vScoreType
when 'P' then sum(decodePrecision(ifnull(s.Hits,0)))
else sum( ifnull(s.Hits,0) )
end,
sum( ifnull(s.Targets, 0)),
case vScoreType
when 'N' then sum( ifnull(s.Hits,0) + ifnull(s.Targets,0) )
when 'P' then sum(decodePrecision(ifnull(s.Hits,0)))
else sum( ifnull(s.Hits,0) )
end,
sum( ifnull(s.Points, 0) )
from tbl_Pistol_Score s
join tbl_Pistol_CompetitionDay d on (d.Id = s.CompetitionDayId and d.CompetitionId = pCompId)
join tbl_Pistol_Entry e on (e.Id = s.EntryId and e.TeamId is not null)
group by e.TeamId;
if (vScoreType = 'N')
then
open cur_nor;
else
open cur_cls;
end if;
set vPrevClassId = null;
cur_loop1: loop
if (vScoreType = 'N') then
fetch cur_nor
into vTeamId, vGunClassId,
vHits, vTargets,
vTotal, vPoints;
else
fetch cur_cls
into vTeamId, vGunClassId,
vHits, vTargets,
vTotal, vPoints;
end if;
if no_more_rows then
if (vScoreType = 'N') then
close cur_nor;
else
close cur_cls;
end if;
leave cur_loop1;
end if;
if vPrevClassId is null
then
set vPrevClassId = vGunClassId;
set vPlace = 1;
else
if (vPrevClassId != vGunClassId)
then
set vPlace = 1;
set vPrevClassId = vGunClassId;
else
set vPlace = vPlace + 1;
end if;
end if;
update Result
set Place = vPlace
where TeamId = vTeamId;
end loop cur_loop1;
select s.TeamId,
t.GunClassId,
t.Name TeamName,
b.Name ClubName,
g.Grade GunGrade,
g.Description GunGradeDesc,
s.Hits,
s.Targets,
s.Total,
s.Points,
s.Place
from Result s
join tbl_Pistol_Team t on (t.Id = s.TeamId)
join tbl_Pistol_GunClassification g on (g.Id = t.GunClassId)
left join tbl_Pistol_Club b on (b.Id = t.ClubId)
order by t.GunClassId, s.Place;
end main;
drop temporary table if exists Result;
end $$