views.sql 2.15 KB
Newer Older
M1888's avatar
M1888 committed
1 2 3 4
-- ---------------
-- Näkymät ja muut omat määrittelyt
-- ---------------

M1888's avatar
M1888 committed
5 6
USE M1888;

M1888's avatar
M1888 committed
7 8 9 10
-- Pistepörssi:
-- Pelaajien tekemät maalit ja syötöt yhteenlaskettuna,
-- poislukien omat maalit

M1888's avatar
M1888 committed
11
CREATE OR REPLACE VIEW Pisteporssi AS
M1888's avatar
M1888 committed
12 13 14 15 16 17 18 19
    SELECT 
        p.pelaaja_id,
        p.enimi,
        p.snimi,
        COUNT(m.pelaaja_id) AS pts
    FROM maali AS m
    INNER JOIN pelaaja AS p
        ON (p.pelaaja_id = m.pelaaja_id OR p.pelaaja_id = m.syottaja_id)
M1888's avatar
M1888 committed
20
    GROUP BY p.pelaaja_id;
M1888's avatar
M1888 committed
21 22

-- Joukkueiden tekemät maalit (apunäkymä seuraavalle)
M1888's avatar
M1888 committed
23
CREATE OR REPLACE VIEW Joukkue_maalit AS 
M1888's avatar
M1888 committed
24 25 26 27 28 29 30 31 32 33 34 35
    SELECT 
        ot.ottelu_id,
        m.joukkue_id,
        COUNT(m.joukkue_id) AS maalit 
    FROM ottelu AS o 
    INNER JOIN ottelutapahtuma AS ot
        ON o.ottelu_id = ot.ottelu_id 
    INNER JOIN maali AS m 
        ON ot.maali_id = m.maali_id 
    GROUP BY m.joukkue_id, o.ottelu_id;

-- Näkymä, jolla saadaan jokaisen ottelun voittanut joukkue, ja sen tekemät maalit
M1888's avatar
M1888 committed
36
CREATE OR REPLACE VIEW Voittajat AS
M1888's avatar
M1888 committed
37 38 39 40 41 42 43 44 45 46 47 48
    SELECT
        jm.ottelu_id,
        j.joukkue_id,
        j.nimi,
        MAX(jm.maalit) AS maalit
    FROM Joukkue_maalit AS jm 
    INNER JOIN joukkue AS j 
        ON jm.joukkue_id = j.joukkue_id 
    GROUP BY jm.ottelu_id;

-- Joukkueiden pistepörssi:
-- 1 otteluvoitto == 1 piste
M1888's avatar
M1888 committed
49
CREATE OR REPLACE VIEW Pisteporssi_joukkue AS
M1888's avatar
M1888 committed
50 51 52 53
    SELECT 
        nimi, 
        COUNT(nimi) AS pts 
    FROM Voittajat 
M1888's avatar
M1888 committed
54
    GROUP BY nimi; 
M1888's avatar
M1888 committed
55 56 57


-- Jäähyminuutit:
M1888's avatar
M1888 committed
58
-- Jäähyt pelaajittain minuutteina
M1888's avatar
M1888 committed
59

M1888's avatar
M1888 committed
60
CREATE OR REPLACE VIEW Jaahyt_pelaajat AS
M1888's avatar
M1888 committed
61 62 63 64 65 66 67 68
    SELECT
        p.pelaaja_id,
        p.enimi,
        p.snimi,
        SEC_TO_TIME(SUM(j.kesto)) AS minuutit 
    FROM jaahy AS j 
    INNER JOIN pelaaja AS p 
        ON p.pelaaja_id = j.pelaaja_id 
M1888's avatar
M1888 committed
69
    GROUP BY pelaaja_id;
M1888's avatar
M1888 committed
70 71

-- Jäähyminuutit joukkueittain
M1888's avatar
M1888 committed
72
CREATE OR REPLACE VIEW Jaahyt_joukkue AS
M1888's avatar
M1888 committed
73
    SELECT 
M1888's avatar
M1888 committed
74
        jo.joukkue_id,
M1888's avatar
M1888 committed
75 76 77 78 79 80 81 82 83
        jo.nimi,
        SEC_TO_TIME(SUM(ja.kesto)) AS minuutit
    FROM jaahy AS ja 
    INNER JOIN pelaaja AS p 
        ON ja.pelaaja_id = p.pelaaja_id 
    INNER JOIN sopimus AS s 
        ON p.pelaaja_id = s.pelaaja_id 
    INNER JOIN joukkue AS jo 
        ON s.joukkue_id = jo.joukkue_id 
M1888's avatar
M1888 committed
84
    GROUP BY jo.joukkue_id;
M1888's avatar
M1888 committed
85