Wyciągnięcie wyników z tabeli "ciekawszym" zapytaniem, pivot table, etc

Witam,
sam nie wiedziałem jak trafnie nazwać temat tego posta, jednak sam wątek nawiązuje do innego mojego wątku niedawno tutaj poruszanego.. (link: Procedura i zmienna liczba zmiennych ).

Z nieocenioną pomoca osoby @Marcin.Miga w zalinkowanym temacie, zostało zaproponowane zastąpienie procedury "zwykłym" zapytaniem SQL..

Na początku, uwzględniając podstawowe założenia* , wszystko było ok, jednak gdy próbowałem uściślić owe założenia to pojawił się problem..

Przechodząc do konkretów..

Kod zapytania wygląda tak:
(ps. kod tabel i reszty wymaganego 'koda' poniżej..)

select pivot.* from 
( 
SELECT wyniki.id_wynik, wyniki.id_strzelca, wyniki.ilosc_punktow, 
sum(wynik*(1-abs(sign(numer_strzalu-1)))) as strzal1, 
sum(wynik*(1-abs(sign(numer_strzalu-2)))) as strzal2, 
sum(wynik*(1-abs(sign(numer_strzalu-3)))) as strzal3, 
sum(wynik*(1-abs(sign(numer_strzalu-4)))) as strzal4, 
sum(wynik*(1-abs(sign(numer_strzalu-5)))) as strzal5, 
sum(wynik*(1-abs(sign(numer_strzalu-6)))) as strzal6, 
sum(wynik*(1-abs(sign(numer_strzalu-7)))) as strzal7, 
sum(wynik*(1-abs(sign(numer_strzalu-8)))) as strzal8, 
sum(wynik*(1-abs(sign(numer_strzalu-9)))) as strzal9, 
sum(wynik*(1-abs(sign(numer_strzalu-10)))) as strzal10 
FROM strzaly  
INNER JOIN wyniki  
ON wyniki.id_strzelca = strzaly.id_strzelca 
GROUP BY id_strzelca ) pivot 
INNER JOIN wyniki w 
ON w.id_wynik = pivot.id_wynik 
ORDER BY w.ilosc_punktow DESC, w.ilosc_10 DESC, w.ilosc_9 DESC, w.ilosc_8 DESC, w.ilosc_7 DESC, w.ilosc_6 DESC,  
w.ilosc_5 DESC, w.ilosc_4 DESC, w.ilosc_3 DESC, w.ilosc_2 DESC, w.ilosc_1 DESC;

Trochę informacji..
A więc zaproponowano mi, abym do wyświetlenia tabeli skorzystał z konstrucji pivot (cross-table), aby otrzymać taką "budowę" tabeli:

+--------+---------------+--------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
| id_wynik | id_strzelca | ilosc_punkow | value1 | value2 | value3 | value4 | value5 | value6 | value7 | value8 | value9 | value10 |
+----------+-------------+--------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+

Jednakże przy tej zamianie pojawia się problem dla strzelców, którzy mają kilka "serii" strzałów, ponieważ wyniki są "sumowane" w jeden wiersz, a założenia są takie:

*
-dla posortuj malejąco według kolumny ilosc_punkow
-jesli kilku strzelcow ma ten sam wynik "rozstrzygnij" kto ma być wyżej.. (sprawdz najpierw liczbę 10-tek, potem (jesli jest konieczne) sprawdź liczbę 9-tek, 8-ek, etc.. (w kodzie załatwia to ORDER BY .. DESC))
-jesli dany strzelec bierze kilka razy udział (wykonuje kilka serii), to zostaw tylko jeden, najwyższy uzyskany przez niego wynik w "wyniku końcowym"

I właśnie z tym ostatnim założeniem jest problem.. (przynajmniej w tym momencie, w którym jestem..)..

Wcześniej próbowałem to robić za pomocą procedury i wykorzystywałem do tego m.in. zapytanie:

SELECT * FROM wyniki AS t1
WHERE (SELECT count(1) FROM wyniki AS t2 WHERE t2.id_strzelca = t1.id_strzelca AND t2.ilosc_punktow > t1.ilosc_punktow AND t2.id_strzelca ) = 0

które zdawało egzamin ale w ostatecznym rozrachunku procedura w zastosowaniach stała się mało "uniwersalna"...

Podsumowując: przedstawiam problem Wam (forumowiczom) z nadziejami na pomoc :)

REATE TABLE `wyniki` (
   `id_wynik` int(11) NOT NULL auto_increment,
   `id_strzelca` int(11) NOT NULL,
   `ilosc_punktow` decimal(11,3) NOT NULL,
   `ilosc_10` int(11) NOT NULL default '0',
   `ilosc_9` int(11) NOT NULL default '0',
   `ilosc_8` int(11) NOT NULL default '0',
   `ilosc_7` int(11) NOT NULL default '0',
   `ilosc_6` int(11) NOT NULL default '0',
   `ilosc_5` int(11) NOT NULL default '0',
   `ilosc_4` int(11) NOT NULL default '0',
   `ilosc_3` int(11) NOT NULL default '0',
   `ilosc_2` int(11) NOT NULL default '0',
   `ilosc_1` int(11) NOT NULL default '0',
   `ilosc_0` int(11) NOT NULL default '0',
   PRIMARY KEY  (`id_wynik`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci


CREATE TABLE `strzaly` (
   `id_strzalu` int(11) NOT NULL auto_increment,
   `id_wyniki` int(11) NOT NULL,
   `id_strzelca` int(11) NOT NULL,
   `numer_strzalu` int(11) NOT NULL default '0',
   `wynik` decimal(5,3) NOT NULL default '0.000',
   PRIMARY KEY  (`id_strzalu`),
   UNIQUE KEY `id_wyniki` (`id_wyniki`,`numer_strzalu`),
   KEY `id_strzelca` (`id_strzelca`),
   KEY `id_wyniki_2` (`id_wyniki`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci


DROP TRIGGER IF EXISTS `tr_bi_strzaly`;
DELIMITER $$
CREATE TRIGGER `tr_bi_strzaly` BEFORE INSERT ON `strzaly`
  FOR EACH ROW begin
    UPDATE wyniki SET ilosc_punktow=ilosc_punktow+NEW.wynik WHERE id_wynik=NEW.id_wyniki;
      
      if NEW.wynik >= 10 then
        UPDATE wyniki SET ilosc_10=ilosc_10+1 WHERE id_wynik=NEW.id_wyniki;
    else
        if NEW.wynik between 9 and 10 then
            UPDATE wyniki SET ilosc_9=ilosc_9+1 WHERE id_wynik=NEW.id_wyniki;
        else
            if NEW.wynik between 8 and 9 then
                UPDATE wyniki SET ilosc_8=ilosc_8+1 WHERE id_wynik=NEW.id_wyniki;
            else
                if NEW.wynik between 7 and 8 then
                    UPDATE wyniki SET ilosc_7=ilosc_7+1 WHERE id_wynik=NEW.id_wyniki;
                else
                    if NEW.wynik between 6 and 7 then
                        UPDATE wyniki SET ilosc_6=ilosc_6+1 WHERE id_wynik=NEW.id_wyniki;
                    else
                        if NEW.wynik between 5 and 6 then
                            UPDATE wyniki SET ilosc_5=ilosc_5+1 WHERE id_wynik=NEW.id_wyniki;
                        else
                            if NEW.wynik between 4 and 5 then
                                UPDATE wyniki SET ilosc_4=ilosc_4+1 WHERE id_wynik=NEW.id_wyniki;
                            else
                                if NEW.wynik between 3 and 4 then
                                    UPDATE wyniki SET ilosc_3=ilosc_3+1 WHERE id_wynik=NEW.id_wyniki;
                                else
                                    if NEW.wynik between 2 and 3 then
                                        UPDATE wyniki SET ilosc_2=ilosc_2+1 WHERE id_wynik=NEW.id_wyniki;
                                    else
                                        if NEW.wynik between 1 and 2 then
                                            UPDATE wyniki SET ilosc_1=ilosc_1+1 WHERE id_wynik=NEW.id_wyniki;
                                        else
                                            if NEW.wynik between 0 and 1 then
                                                UPDATE wyniki SET ilosc_0=ilosc_0+1 WHERE id_wynik=NEW.id_wyniki;
                                            end if;
                                        end if;
                                    end if;
                                end if;
                            end if;
                        end if;
                    end if;
                end if;
            end if;
        end if;
    end if;

end $$
DELIMITER ;

INSERT INTO wyniki(id_wynik, id_strzelca) VALUES(1,7);
INSERT INTO wyniki(id_wynik, id_strzelca) VALUES(2,8);
INSERT INTO wyniki(id_wynik, id_strzelca) VALUES(3,8);


insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)  
values(1, 7, 1, 5);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)  
values(1, 7, 2, 9);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)  
values(1, 7, 3, 9.2);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)  
values(1, 7, 4, 5);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)  
values(1, 7, 5, 10.001);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)  
values(1, 7, 6, 9.1);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)  
values(1, 7, 7, 8);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)  
values(1, 7, 8, 6);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)  
values(1, 7, 9, 7);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)  
values(1, 7, 10, 5);

insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(2, 8, 1, 6.1);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(2, 8, 2, 8);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(2, 8, 3, 5.2);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(2, 8, 4, 5.7);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(2, 8, 5, 10.1);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(2, 8, 6, 10.1);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(2, 8, 7, 10.001);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(2, 8, 8, 9);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(2, 8, 9, 7);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(2, 8, 10, 7);

insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(3, 8, 1, 5.1);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(3, 8, 2, 7);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(3, 8, 3, 5.2);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(3, 8, 4, 7.7);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(3, 8, 5, 10.7);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(3, 8, 6, 8.1);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(3, 8, 7, 6);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(3, 8, 8, 8);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(3, 8, 9, 9);
insert into strzaly(id_wyniki, id_strzelca, numer_strzalu, wynik)
values(3, 8, 10,9);

Tylko jedna małą uwaga. W podzapytaniu zaczynającym się od 'SELECT wyniki.id_wynik, wyniki.id_strzelca, wyniki.ilosc_punktow, '
poiwnieneś mieć grupowanie: GROUP BY wyniki.id_wynik, wyniki.id_strzelca, wyniki.ilosc_punktow
Powyższe zapytanie przejdzie ci tylko na źle skonfigurowanym MySQL. Popraw i sprawdź, czy się nic nie pogorszyło...

-jesli dany strzelec bierze kilka razy udział (wykonuje kilka serii), to zostaw tylko jeden, najwyższy uzyskany przez niego wynik w "wyniku końcowym"

Może użyć funkcji max() na wynik strzału?

Panowie @Marcin.Miga i @tehaor - dzięki za odpowiedzi, bo dzięki Wam chyba coś się urodziło..

Na początku wstawiłem brakujące GROUP BY wyniki.id_wynik, wyniki.id_strzelca, wyniki.ilosc_punktow ale nadal nie było pożądanego efektu.. Ostatecznie okazało się, że był błąd w zapytaniu .. powinno być ON wyniki.id_wynik = strzaly.id_wyniki a miałem #ON wyniki.id_strzelca = strzaly.id_strzelca ....

Także zapytanie wydawało się już funkcjonować poprawnie. Pozostał jednak problem z "duplikatami" w związku kilku wyników dla danego strzelca.. Idąc za radą postanowiłem "wpleść" w kod funkcje MAX() .. Poniekąd to się udało, i dla akutalnej bazy daje poprawny wynik.. muszę wprowadzić jeszcze większą liczę danych i to jeszcze sprawdzić, ale wynik na tą chwile jest pozytywny :)

Co ciekawe mam jeszcze pytanie odnośnie tego zapytania, ale to już bezpośrednio w kodzie (komentarz)..

SELECT pivot.* FROM  
(  
SELECT wyniki.id_wynik, wyniki.id_strzelca, wyniki.ilosc_punktow,  
SUM(wynik*(1-abs(sign(numer_strzalu-1)))) AS strzal1,  
SUM(wynik*(1-abs(sign(numer_strzalu-2)))) AS strzal2,  
SUM(wynik*(1-abs(sign(numer_strzalu-3)))) AS strzal3,  
SUM(wynik*(1-abs(sign(numer_strzalu-4)))) AS strzal4,  
SUM(wynik*(1-abs(sign(numer_strzalu-5)))) AS strzal5,  
SUM(wynik*(1-abs(sign(numer_strzalu-6)))) AS strzal6,  
SUM(wynik*(1-abs(sign(numer_strzalu-7)))) AS strzal7,  
SUM(wynik*(1-abs(sign(numer_strzalu-8)))) AS strzal8,  
SUM(wynik*(1-abs(sign(numer_strzalu-9)))) AS strzal9,  
SUM(wynik*(1-abs(sign(numer_strzalu-10)))) AS strzal10  
FROM strzaly   
INNER JOIN wyniki   
ON wyniki.id_wynik = strzaly.id_wyniki            #miejsce poprawionego błędu
GROUP BY  id_wyniki, wyniki.id_wynik, wyniki.id_strzelca, wyniki.ilosc_punktow) pivot 
INNER JOIN wyniki w  
ON w.id_wynik = pivot.id_wynik  
GROUP BY w.id_strzelca       # dodane w związku z duplikatami.. Co ciekawe już dla samego GROUP By otrzymuje poprawne wyniki
HAVING MAX(w.ilosc_punktow)    # ale chyba wymagana jest raczej również ta klauzula ?
ORDER BY w.ilosc_punktow DESC, w.ilosc_10 DESC, w.ilosc_9 DESC, w.ilosc_8 DESC, w.ilosc_7 DESC, w.ilosc_6 DESC,   
w.ilosc_5 DESC, w.ilosc_4 DESC, w.ilosc_3 DESC, w.ilosc_2 DESC, w.ilosc_1 DESC;

ps. Jak bardzo nie wydajne jest to zapytanie ? tzn. co jest najbardziej kosztowną operacją w tym zapytaniu ? (i tak w ogóle jak ta sprawa wygląda np. dla GROUP BY, ORDER BY, HAVING, kwestia podzapytania ...?)

Ja nie rozumiem tego HAVING MAX(w.id_strzelca). Co to miałoby robić? (znowu kolejna rzecz, która tylko na MySQL przejdzie.... masakra jakaś)

EDIT:
eh.. jednak nie załatwia to sprawy.. ogólnie jest wyświetlana wymagana liczba wierszy, tzn. zostawia tylko wiersze gdzie dla danego strzelca wartość ilosc_punktow jest największa.. tylko, że ten wynik "wkomponowany" jest w wiersz dla innego strzału.. (tzn. inne wyniki "partialne" są jakoby z tego wiersza, który powinien być usunięty... czyli jest jednym słowiem mega-mix..)

Brakuje mi już pomysłów co tu jeszcze można wykombinować .. ;|

Ogónie sprawę załatwia coś takiego :
(przynajmniej na ten moment..)

 
SELECT w.id_wynik, w.id_strzelca, MAX(w.ilosc_punktow), pivot.strzal1 FROM  
(  
SELECT wyniki.id_wynik, wyniki.id_strzelca, wyniki.ilosc_punktow,  
SUM(wynik*(1-abs(sign(numer_strzalu-1)))) AS strzal1,  
SUM(wynik*(1-abs(sign(numer_strzalu-2)))) AS strzal2,  
SUM(wynik*(1-abs(sign(numer_strzalu-3)))) AS strzal3,  
SUM(wynik*(1-abs(sign(numer_strzalu-4)))) AS strzal4,  
SUM(wynik*(1-abs(sign(numer_strzalu-5)))) AS strzal5,  
SUM(wynik*(1-abs(sign(numer_strzalu-6)))) AS strzal6,  
SUM(wynik*(1-abs(sign(numer_strzalu-7)))) AS strzal7,  
SUM(wynik*(1-abs(sign(numer_strzalu-8)))) AS strzal8,  
SUM(wynik*(1-abs(sign(numer_strzalu-9)))) AS strzal9,  
SUM(wynik*(1-abs(sign(numer_strzalu-10)))) AS strzal10  
FROM strzaly   
INNER JOIN wyniki   
ON wyniki.id_wynik = strzaly.id_wyniki 
GROUP BY  id_wyniki, wyniki.id_wynik, wyniki.id_strzelca, wyniki.ilosc_punktow) pivot 
INNER JOIN wyniki w  
ON w.id_wynik = pivot.id_wynik  
GROUP BY w.id_strzelca 
ORDER BY w.ilosc_punktow DESC, w.ilosc_10 DESC, w.ilosc_9 DESC, w.ilosc_8 DESC, w.ilosc_7 DESC, w.ilosc_6 DESC,   
w.ilosc_5 DESC, w.ilosc_4 DESC, w.ilosc_3 DESC, w.ilosc_2 DESC, w.ilosc_1 DESC;

czyli MAX(w.ilosc_punktow) w pierwszym SELECT'cie.. Jednak wtedy muszę ręcznie niejako zastąpić wypisywanie kolumn zamiast * .. (z jednej strony z tego co pamiętam wypisanie kolumn przyspiesza zapytanie.. z drugiej z racji tego, że jest zmienna ilość kolumn dla tabel będę miał utrudniony proces przygotowania samego zapytania..) Chyba, że idzie jakoś inaczej przygotować to zapytanie aby ten problem zniknął ? ;>

Ok.. w sumie w akcie desperacji chwyciłem się tego co pisałem na początku :

SELECT * FROM wyniki AS t1 
WHERE (SELECT COUNT(1) FROM wyniki AS t2 WHERE t2.id_strzelca = t1.id_strzelca AND t2.ilosc_punktow > t1.ilosc_punktow AND t2.id_strzelca ) = 0

i dokładając do tego "wiedzę empiryczną" nabytą na dotychczasowych przykładach skonstruowałem zapytanie, które daje w końcu poprawny wynik.. ale jego budowa mnie przeraża :P
Może jakieś sugestie odnośnie tego zapytania ? (pewnie można coś by tu poprawić ..)

SELECT sub.* FROM 
( 
SELECT pivot.* FROM 
( 
SELECT wyniki.id_wynik, wyniki.id_strzelca, wyniki.ilosc_punktow,  
SUM(wynik*(1-abs(sign(numer_strzalu-1)))) AS strzal1,  
SUM(wynik*(1-abs(sign(numer_strzalu-2)))) AS strzal2,  
SUM(wynik*(1-abs(sign(numer_strzalu-3)))) AS strzal3,  
SUM(wynik*(1-abs(sign(numer_strzalu-4)))) AS strzal4,  
SUM(wynik*(1-abs(sign(numer_strzalu-5)))) AS strzal5,  
SUM(wynik*(1-abs(sign(numer_strzalu-6)))) AS strzal6,  
SUM(wynik*(1-abs(sign(numer_strzalu-7)))) AS strzal7,  
SUM(wynik*(1-abs(sign(numer_strzalu-8)))) AS strzal8,  
SUM(wynik*(1-abs(sign(numer_strzalu-9)))) AS strzal9,  
SUM(wynik*(1-abs(sign(numer_strzalu-10)))) AS strzal10  
FROM strzaly   
INNER JOIN wyniki   
ON wyniki.id_wynik = strzaly.id_wyniki 
GROUP BY  id_wyniki, wyniki.id_wynik, wyniki.id_strzelca, wyniki.ilosc_punktow ) pivot 
WHERE (SELECT COUNT(1) FROM wyniki AS t2 WHERE t2.id_strzelca = pivot.id_strzelca AND t2.ilosc_punktow > pivot.ilosc_punktow AND t2.id_strzelca ) = 0 ) sub 
INNER JOIN wyniki w  
ON w.id_wynik = sub.id_wynik  
ORDER BY w.ilosc_punktow DESC, w.ilosc_10 DESC, w.ilosc_9 DESC, w.ilosc_8 DESC, w.ilosc_7 DESC, w.ilosc_6 DESC,   
w.ilosc_5 DESC, w.ilosc_4 DESC, w.ilosc_3 DESC, w.ilosc_2 DESC, w.ilosc_1 DESC;