Prosím o pomoc pri nasledovnom „futbalovom“ probléme:
Uvedený select vypíše výsledky pre jeden klub (napr. id=1 ). Dá sa to napísať (bez procedury), aby to vypísalo pre všetky kluby? Pripájam komplet tabuľky „kluby“ a „zapasy“.
SELECT k.nazov, a.zapas AS pocetzapasov, b.zapas AS vyhrate,c.zapas AS remizy,d.zapas AS prehry,e.zapas+f.zapas AS goly_dal,g.zapas+h.zapas AS goly_dostal, (b.zapas*3+c.zapas) as body FROM
(SELECT count(*) AS zapas FROM zapasy WHERE (id_dom=1 or id_host=1) and (goly_dom is not null) ) a,
(SELECT (count(*)) AS zapas FROM zapasy WHERE ((id_dom=1) and (goly_dom>goly_host)) or ((id_host=1) and (goly_host>goly_dom)) ) b,
(SELECT (count(*)) AS zapas FROM zapasy WHERE ((id_dom=1 or id_host=1) and (goly_dom=goly_host)) ) c,
(SELECT (count(*)) AS zapas FROM zapasy WHERE ((id_dom=1) and (goly_dom<goly_host)) or ((id_host=1) and (goly_host<goly_dom)) ) d,
(select sum(goly_dom) as zapas FROM zapasy where id_dom=1) e,
(select sum(goly_host) as zapas FROM zapasy where id_host=1) f,
(select sum(goly_dom) as zapas FROM zapasy where id_host=1) g,
(select sum(goly_host) as zapas FROM zapasy where id_dom=1) h
left join kluby k on k.id=1
nazov pocetzapasov vyhrate remizy prehry goly_dal goly_dostal body
MFK Strojár Krupina 5 3 1 1 14 7 10
CREATE TABLE IF NOT EXISTS `kluby` (
`ID` int(11) ,
`nazov` varchar(50) NOT NULL,
`kr_nazov` varchar(20) NOT NULL,
PRIMARY KEY (`ID`)
) ;
--
-- Sťahujem dáta pre tabuľku `kluby`
--
INSERT INTO `kluby` (`ID`, `nazov`, `kr_nazov`) VALUES
(1, 'MFK Strojár Krupina ', 'Krupina'),
(2, 'ŠK Badín ', 'Badín'),
(3, 'MFK Revúca ', 'Revúca'),
(4, 'FK Žiar nad Hronom ', 'Žiar n. Hr.'),
(5, 'Sitno Banská Štiavnica ', 'B. Štiavnica'),
(6, 'CSM Tisovec ', 'Tisovec'),
(7, 'MFK Lokomotíva Zvolen C ', 'Zvolen C'),
(8, 'MFK Banská Bystrica ', 'MFK B. Bystrica'),
(9, 'ŠK Tempus Rimavská Sobota ', 'Tempus RS'),
(10, 'MFK SPARTAK Hriňová ', 'Hriňová'),
(11, 'MŠK R. Sobota ', 'R. Sobota'),
(12, 'Slovan Kúpele Sliač', 'Sliač'),
(13, 'FTC Fiľakovo', 'Fiľakovo'),
(14, 'MFK Detva ', 'Detva');
--
-- Štruktúra tabuľky pre tabuľku `zapasy`
--
CREATE TABLE IF NOT EXISTS `zapasy` (
`ID` int(11) ,
`kolo` int(11) NOT NULL,
`datum` varchar(20) NOT NULL,
`ID_dom` int(11) default NULL,
`ID_host` int(11) default NULL,
`goly_dom` int(11) default NULL,
`goly_host` int(11) default NULL,
PRIMARY KEY (`ID`)
) ;
--
-- Sťahujem dáta pre tabuľku `zapasy`
--
INSERT INTO `zapasy` (`ID`, `kolo`, `datum`, `ID_dom`, `ID_host`, `goly_dom`, `goly_host`) VALUES
(1, 1, 'Ut 1.9.2009 12:00', 1, 8, 4, 2),
(2, 1, 'Ut 1.9.2009 13:30 ', 2, 9, 0, 4),
(3, 1, 'Ut 1.9.2009 12:00', 3, 10, 2, 4),
(4, 1, 'Ut 1.9.2009 12:00', 4, 11, 4, 2),
(5, 1, 'Ut 1.9.2009 12:00', 5, 12, 0, 2),
(6, 1, 'Ut 1.9.2009 12:00', 6, 13, 1, 7),
(7, 1, 'Ut 1.9.2009 12:00', 7, 14, 1, 2),
(8, 2, 'Ut 15.9.2009 12:00', 8, 14, NULL, NULL),
(9, 2, 'Ut 15.9.2009 12:00', 13, 7, NULL, NULL),
(10, 2, 'Ut 15.9.2009 12:00', 12, 6, NULL, NULL),
(11, 2, 'Ut 15.9.2009 12:00', 11, 5, NULL, NULL),
(12, 2, 'Ut 15.9.2009 12:00', 10, 4, NULL, NULL),
(13, 2, 'Ut 15.9.2009 12:00', 9, 3, NULL, NULL),
(14, 2, 'Ut 15.9.2009 12:00', 1, 2, NULL, NULL),
(15, 3, 'Ne 23.8.2009 12:00', 2, 8, 2, 4),
(16, 3, 'So 22.8.2009 12:00', 3, 1, 1, 2),
(17, 3, 'So 22.8.2009 12:00', 4, 9, 2, 4),
(18, 3, 'So 22.8.2009 12:00', 5, 10, 1, 7),
(19, 3, 'So 22.8.2009 12:00', 6, 11, 1, 6),
(20, 3, 'Ne 23.8.2009 12:00', 7, 12, 0, 2),
(21, 3, 'So 22.8.2009 12:00', 14, 13, 1, 7),
(22, 4, 'Ne 30.8.2009 12:00', 8, 13, 2, 1),
(23, 4, 'So 29.8.2009 12:00', 12, 14, 0, 0),
(24, 4, 'So 29.8.2009 12:00', 11, 7, NULL, NULL),
(25, 4, 'So 29.8.2009 12:00', 10, 6, 1, 2),
(26, 4, 'So 29.8.2009 12:00', 9, 5, 6, 0),
(27, 4, 'So 29.8.2009 12:00', 1, 4, 1, 2),
(28, 4, 'So 29.8.2009 12:00', 2, 3, 1, 1),
(29, 5, 'So 5.9.2009 12:00', 3, 8, 1, 4),
(30, 5, 'So 5.9.2009 12:00', 4, 2, 7, 0),
(31, 5, 'So 5.9.2009 12:00', 5, 1, 0, 5),
(32, 5, 'So 5.9.2009 12:00', 6, 9, NULL, NULL),
(33, 5, 'Ne 6.9.2009 12:00', 7, 10, 2, 6),
(34, 5, 'So 5.9.2009 12:00', 14, 11, 1, 4),
(35, 5, 'So 5.9.2009 12:00', 13, 12, 0, 2),
(36, 6, 'Ne 13.9.2009 12:00', 8, 12, 0, 1),
(37, 6, 'So 12.9.2009 12:00', 11, 13, 0, 1),
(38, 6, 'So 12.9.2009 12:00', 10, 14, 11, 0),
(39, 6, 'So 12.9.2009 12:00', 9, 7, 10, 0),
(40, 6, 'So 12.9.2009 12:00', 1, 6, 2, 2),
(41, 6, 'Ne 13.9.2009 13:30', 2, 5, 2, 2),
(42, 6, 'So 12.9.2009 12:00', 3, 4, 1, 7);
|
