views.sql 2.17 KB
Newer Older
M1888's avatar
M1888 committed
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
-- ---------------
-- Näkymät ja muut omat määrittelyt
-- ---------------

-- Pistepörssi:
-- Pelaajien tekemät maalit ja syötöt yhteenlaskettuna,
-- poislukien omat maalit

CREATE VIEW Pisteporssi AS
    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)
    GROUP BY p.pelaaja_id
    ORDER BY pts DESC;

-- Joukkueiden tekemät maalit (apunäkymä seuraavalle)
CREATE VIEW Joukkue_maalit AS 
    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
CREATE VIEW Voittajat AS
    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
CREATE VIEW Pisteporssi_joukkue AS
    SELECT 
        nimi, 
        COUNT(nimi) AS pts 
    FROM Voittajat 
    GROUP BY nimi 
    ORDER BY pts;


-- Jäähyminuutit:
-- Pelaajat, jotka ovat keränneet eniten jäähyminuutteja

CREATE VIEW Jaahyt_pelaajat AS
    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 
    GROUP BY pelaaja_id 
    ORDER BY minuutit DESC;

-- Jäähyminuutit joukkueittain
CREATE VIEW Jaahyt_joukkue AS
    SELECT 
        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 
    GROUP BY jo.joukkue_id
    ORDER BY minuutit DESC;