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
| -- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `groups_fill_with_cursor`()
BEGIN
declare country VARCHAR(45);
declare cur cursor for select name from teams;
open cur;
fetch cur into country;
start_loop:loop
-- IF EXISTS(SELECT team FROM statistics WHERE `team`=country group by match_id)
-- THEN
SELECT team,
count(statistics.Round) AS 'Pld',
(SELECT count(pts) FROM statistics WHERE pts=3 and team=country) AS 'W',
(SELECT count(pts) FROM statistics WHERE pts=1 and team=country) AS 'D',
(SELECT count(pts) FROM statistics WHERE pts=0 and team=country) AS 'L',
SUM(statistics.Goals)AS 'GF',
(SUM(matches.`Goal Scored`) - SUM(statistics.Goals)) AS 'GA',
(SUM(statistics.Goals)-(SUM(matches.`Goal Scored`) - SUM(statistics.Goals))) AS 'GD',
(SELECT sum(pts) FROM statistics WHERE team=country) AS 'Pts'
FROM statistics INNER JOIN matches
WHERE
((statistics.team=matches.`Team 1` and matches.`Team 1`= country and matches.Round=statistics.Round)
or (statistics.team=matches.`Team 2` and matches.`Team 2`= country and matches.Round=statistics.Round))
;
-- else
-- LEAVE start_loop;
-- END IF;
END LOOP;
close cur;
END |
