mastervlad Posted March 11, 2015 Report Share Posted March 11, 2015 Tabelul rezultate(id_concurent, b1, b2, b3, b4, b5, b6, b7, b8, b9, b10) in care se salveaza rezultatele unor concurenti la bowling (10 aruncari, fiecare rezultat fiind un numar intre 0 si 10). Afisati concurentii cu punctaj 0.(3 aruncari ratate) Quote Link to comment Share on other sites More sharing options...
TheWiner Posted March 11, 2015 Report Share Posted March 11, 2015 Incearca:Select id_concurent, count( * ) as Aruncari from rezultate where count( * ) < 7 Quote Link to comment Share on other sites More sharing options...
QUADMACHINE Posted March 11, 2015 Report Share Posted March 11, 2015 Nu am testat.SELECT `id_concurent` AS `ConcurentCuRatari`, `b1`, `b2`, `b3`, `b4`, `b5`, `b6`, `b7`, `b8`, `b9`, `b10` FROM `Bowling`WHERE 0 IN(`b1`, `b2`, `b3`, `b4`, `b5`, `b6`, `b7`, `b8`, `b9`, `b10`) AND HAVING COUNT(*) < 3 AND GROUP BY `id_concurent`SELECT `id_concurent`, SUM(CASE WHEN (b1 <= 0 AND COUNT(b1) < 3) OR (b2 <= 0 AND COUNT(b2) < 3) OR (b3 <= 0 AND COUNT(b3) < 3) OR (b4 <= 0 AND COUNT(b4) < 3) OR (b5 <= 0 AND COUNT(b5) < 3) OR (b6 <= 0 AND COUNT(b6) < 3) OR (b7 <= 0 AND COUNT(b7) < 3) OR (b8 <= 0 AND COUNT(b8) < 3) OR (b9 <= 0 AND COUNT(b9) < 3) OR (b10 <= 0 AND COUNT(b10) < 3) THEN 1 ELSE 0 END) AS ConcurentCuRatariFROM `Bowling` GROUP BY `id_concurent` Quote Link to comment Share on other sites More sharing options...
QUADMACHINE Posted March 12, 2015 Report Share Posted March 12, 2015 Trebuie sa caute in coloane multiple daca Vasile, George sau Ionel au punctaj 0 de 3 ori in una dintre coloanele b(3 ratari).ex:George: b1 b2 b3 b4 b5 b6 b7 b8 b9 b10 1 1 2 6 6 8 12 0 1 2True(Conditie adevarata, are trei de 0 adica 3 ratari)Vasile: b1 b2 b3 b4 b5 b6 b7 b8 b9 b10 1 0 2 0 6 8 12 0 1 2George: b1 b2 b3 b4 b5 b6 b7 b8 b9 b10 1 0 2 6 6 8 12 0 1 2 Quote Link to comment Share on other sites More sharing options...