Zmotywowany odpowiedzią @abrakadaber i @furious programming postanowiłem wyłożyć wszystko co mam na temat tej procedury i jej funkcjonalności..
Input do procedury to:
+--------+---------+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
| tab_id | user_id | sum_value | value1 | value2 | value3 | value4 | value5 | value6 | value7 | value8 | value9 | value10 |
+--------+---------+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
| 5 | 12 | 73.3001 | 5.000 | 9.000 | 9.200 | 5.000 | 10.001 | 9.100 | 8.000 | 6.000 | 7.000 | 5.000 |
| 6 | 10 | 80.8002 | 10.001 | 9.000 | 8.000 | 7.000 | 4.000 | 5.000 | 10.000 | 10.801 | 8.000 | 9.000 |
| 7 | 16 | 80.8001 | 10.001 | 9.000 | 8.000 | 7.000 | 4.000 | 5.000 | 10.000 | 10.800 | 8.000 | 9.000 |
| 8 | 15 | 68.2000 | 6.000 | 6.000 | 7.000 | 5.000 | 4.000 | 9.000 | 8.000 | 6.000 | 10.000 | 7.200 |
| 9 | 17 | 87.5000 | 10.000 | 9.000 | 8.000 | 6.400 | 10.100 | 7.000 | 10.000 | 10.800 | 8.000 | 8.200 |
| 10 | 14 | 73.3001 | 5.000 | 9.000 | 9.200 | 10.000 | 10.001 | 9.100 | 7.000 | 4.000 | 5.000 | 5.000 |
| 11 | 9 | 73.3001 | 4.000 | 9.000 | 10.000 | 5.000 | 10.001 | 9.100 | 8.000 | 6.000 | 7.000 | 5.000 |
| 12 | 9 | 67.1001 | 3.000 | 7.000 | 10.000 | 5.000 | 10.001 | 6.000 | 8.000 | 6.000 | 7.000 | 5.000 |
+--------+---------+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
natomiast output:
+--------+---------+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
| tab_id | user_id | sum_value | value1 | value2 | value3 | value4 | value5 | value6 | value7 | value8 | value9 | value10 |
+--------+---------+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
| 9 | 17 | 87.5000 | 10.000 | 9.000 | 8.000 | 6.400 | 10.100 | 7.000 | 10.000 | 10.800 | 8.000 | 8.200 |
| 6 | 10 | 80.8002 | 10.001 | 9.000 | 8.000 | 7.000 | 4.000 | 5.000 | 10.000 | 10.801 | 8.000 | 9.000 |
| 7 | 16 | 80.8001 | 10.001 | 9.000 | 8.000 | 7.000 | 4.000 | 5.000 | 10.000 | 10.800 | 8.000 | 9.000 |
| 10 | 14 | 73.3001 | 5.000 | 9.000 | 9.200 | 10.000 | 10.001 | 9.100 | 7.000 | 4.000 | 5.000 | 5.000 |
| 11 | 9 | 73.3001 | 4.000 | 9.000 | 10.000 | 5.000 | 10.001 | 9.100 | 8.000 | 6.000 | 7.000 | 5.000 |
| 5 | 12 | 73.3001 | 5.000 | 9.000 | 9.200 | 5.000 | 10.001 | 9.100 | 8.000 | 6.000 | 7.000 | 5.000 |
| 8 | 15 | 68.2000 | 6.000 | 6.000 | 7.000 | 5.000 | 4.000 | 9.000 | 8.000 | 6.000 | 10.000 | 7.200 |
+--------+---------+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
Co się stało ?
Z racji tego, iż wynik ma stanowić swoisty ranking, dane zostały uszeregowane według kolumny sum_value (malejąco) oraz zostało "rozstrzygnięte", który użytkownik powinien być wyżej dla tych samych wyników w kolumnie sum_value.
Jak wygląda to rozstrzygnięcie?
Jeśli wartość sum_value jest równa dla danego użytkownika to powinien "wygrać" ten wynik, który ma większą ilość 10-tek, potem 9-tek, 8-ek, itd.. Analizując wyniki użytkowników user_id = 9 i 14 sprawdzamy ile ma 10-tek (mają po dwie); sprawdzamy liczbę 9-tek (pierwszy ma dwie a drugi trzy), wiec user_id = 14 wygrywa i jest wyżej.
I jeszcze jedno - użytkownicy mogą mieć kilka pozycji wyników (jak np. user_id = 9), to w wyniku końcowym uwzględniany jest tylko jeden jego wynik (oczywiście najwyższy..).
I właśnie tego typu operacje robię w owej procedurze, gdzie wynik końcowy jest tworzony w tabeli tymczasowej, bo nie potrafiłem tego zrobić jednym "spójnym" zapytaniem..
W procedurze używam w sumie **dwóch kursorów **- najpierw usuwam wyniki powtarzające się dla danego użytkownika a następnie drugim kursorem wyjmuje wyniki posortowane według atrybutu sum_value z uwzględnieniem wyników cząstkowych -- i z tego tworzę wynik końcowy..
edit::
Wrzucam kod tabeli dla chętnych, którzy chcą pomóc
DROP TABLE IF EXISTS `tab_nr_7`;
CREATE TABLE `tab_nr_7` (
`tab_id` int(10) NOT NULL AUTO_INCREMENT,
`user_id` int(10) NOT NULL,
`sum_value` double(7,4) DEFAULT NULL,
`value1` double(5,3) DEFAULT NULL,
`value2` double(5,3) DEFAULT NULL,
`value3` double(5,3) DEFAULT NULL,
`value4` double(5,3) DEFAULT NULL,
`value5` double(5,3) DEFAULT NULL,
`value6` double(5,3) DEFAULT NULL,
`value7` double(5,3) DEFAULT NULL,
`value8` double(5,3) DEFAULT NULL,
`value9` double(5,3) DEFAULT NULL,
`value10` double(5,3) DEFAULT NULL,
PRIMARY KEY (`tab_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `tab_nr_7`
--
INSERT INTO `tab_nr_7` VALUES (5,12,73.3001,5.000,9.000,9.200,5.000,10.001,9.100,8.000,6.000,7.000,5.000),(6,10,80.8002,10.001,9.000,8.000,7.000,4.000,5.000,10.000,10.801,8.000,9.000),(7,16,80.8001,10.001,9.000,8.000,7.000,4.000,5.000,10.000,10.800,8.000,9.000),(8,15,68.2000,6.000,6.000,7.000,5.000,4.000,9.000,8.000,6.000,10.000,7.200),(9,17,87.5000,10.000,9.000,8.000,6.400,10.100,7.000,10.000,10.800,8.000,8.200),(10,14,73.3001,5.000,9.000,9.200,10.000,10.001,9.100,7.000,4.000,5.000,5.000),(11,9,73.3001,4.000,9.000,10.000,5.200,10.001,9.100,8.000,6.000,7.000,5.000),(12,9,67.1001,3.000,7.000,10.000,5.000,10.001,6.000,8.000,6.000,7.000,5.000);
edit 2:
Dorzucam procedurę: (jakoś jest pewnie wątpliwa, ale się "kompiluje"..)
DROP PROCEDURE IF EXISTS RANKING;
DELIMITER $$
CREATE PROCEDURE ranking()
BEGIN
DECLARE t_tab_id INT(10);
DECLARE t_user_id INT(10);
DECLARE t_sum_value DOUBLE(7,4);
DECLARE t_value1 DOUBLE(5,3);
DECLARE t_value2 DOUBLE(5,3);
DECLARE t_value3 DOUBLE(5,3);
DECLARE t_value4 DOUBLE(5,3);
DECLARE t_value5 DOUBLE(5,3);
DECLARE t_value6 DOUBLE(5,3);
DECLARE t_value7 DOUBLE(5,3);
DECLARE t_value8 DOUBLE(5,3);
DECLARE t_value9 DOUBLE(5,3);
DECLARE t_value10 DOUBLE(5,3);
DECLARE globalRowCounter INTEGER DEFAULT 0;
DECLARE numRowsOfCurs1 INTEGER DEFAULT 0;
DECLARE numRowOfTotalValue INTEGER DEFAULT 0;
# kursor z wyłonieniem najlepszych (pojedynczych) wyników dla konkretnych zawodników
DECLARE cur1 CURSOR FOR SELECT tab_id,user_id,sum_value,value1,value2,value3,value4,value5
,value6,value7,value8,value9,value10
FROM tab_nr_7 AS t1
WHERE (SELECT count(1) FROM tab_nr_7 AS t2 WHERE t2.user_id = t1.user_id AND t2.sum_value > t1.sum_value AND t2.user_id ) = 0
ORDER BY sum_value DESC;
DROP TABLE IF EXISTS t_table;
CREATE TEMPORARY TABLE IF NOT EXISTS t_table
(tab_id INT(10), user_id INT(10), sum_value DOUBLE(6,4), value1 DOUBLE(5,3),
value2 DOUBLE(5,3),value3 DOUBLE(5,3),value4 DOUBLE(5,3),value5 DOUBLE(5,3),value6 DOUBLE(5,3),
value7 DOUBLE(5,3),value8 DOUBLE(5,3),value9 DOUBLE(5,3),value10 DOUBLE(5,3));
OPEN cur1;
SELECT FOUND_ROWS() INTO numRowsOfCurs1;
mainLoop: LOOP
FETCH cur1 INTO t_tab_id,t_user_id,t_sum_value, t_value1,t_value2,t_value3,
t_value4,t_value5,t_value6,t_value7,t_value8,t_value9,t_value10;
SELECT count(1) INTO numRowOfTotalValue FROM tab_nr_7 WHERE sum_value = t_sum_value;
IF numRowOfTotalValue = 1 THEN
INSERT INTO t_table (tab_id,user_id,sum_value,value1,value2,value3,value4,value5
,value6,value7,value8,value9,value10)
VALUES (t_tab_id, t_user_id,t_sum_value, t_value1,t_value2,t_value3,
t_value4,t_value5,t_value6,t_value7,t_value8,t_value9,t_value10);
ELSE
BLOCK2: BEGIN
DECLARE tt_tab_id INT(10);
DECLARE tt_user_id INT(10);
DECLARE tt_sum_value DOUBLE(7,4);
DECLARE tt_value1 DOUBLE(5,3);
DECLARE tt_value2 DOUBLE(5,3);
DECLARE tt_value3 DOUBLE(5,3);
DECLARE tt_value4 DOUBLE(5,3);
DECLARE tt_value5 DOUBLE(5,3);
DECLARE tt_value6 DOUBLE(5,3);
DECLARE tt_value7 DOUBLE(5,3);
DECLARE tt_value8 DOUBLE(5,3);
DECLARE tt_value9 DOUBLE(5,3);
DECLARE tt_value10 DOUBLE(5,3);
DECLARE tt_liczba INT;
DECLARE numRowsOfCurs2 INTEGER DEFAULT 0;
DECLARE rowCounterLoop INTEGER DEFAULT 0;
# kursor z posortowanymi wynikami dla konkretnego sum_value
DECLARE cur2 CURSOR FOR SELECT * FROM tab_nr_7 as t JOIN (
SELECT user_id, MAX(POWER(10, value1)+POWER(10, value2)+POWER(10, value3)+POWER(10, value4)+POWER(10, value5)+POWER(10, value6)+POWER(10, value7)+POWER(10, value8)+POWER(10, value9)+POWER(10, value10)) liczba FROM tab_nr_7 as t
WHERE sum_value = t_sum_value
GROUP BY user_id) x ON t.user_id=x.user_id AND POWER(10, value1)+POWER(10, value2)+POWER(10, value3)+POWER(10, value4)+POWER(10, value5)+POWER(10, value6)+POWER(10, value7)+POWER(10, value8)+POWER(10, value9)+POWER(10, value10)=x.liczba
ORDER BY liczba DESC;
OPEN cur2;
SELECT FOUND_ROWS() INTO numRowsOfCurs2;
subLoop: LOOP
FETCH cur2 INTO tt_tab_id,tt_user_id,tt_sum_value, tt_value1,tt_value2,tt_value3,
tt_value4,tt_value5,tt_value6,tt_value7,tt_value8,tt_value9,tt_value10,tt_user_id,tt_liczba;
INSERT INTO t_table (tab_id,user_id,sum_value,value1,value2,value3,value4,value5
,value6,value7,value8,value9,value10)
VALUES (tt_tab_id, tt_user_id,tt_sum_value, tt_value1,tt_value2,tt_value3,
tt_value4,tt_value5,tt_value6,tt_value7,tt_value8,tt_value9,tt_value10);
SET rowCounterLoop := rowCounterLoop+1;
IF rowCounterLoop <= numRowsOfCurs2-1 THEN
BEGIN
FETCH NEXT FROM cur1 INTO t_tab_id,t_user_id,t_sum_value, t_value1,t_value2,t_value3,t_value4,t_value5
,t_value6,t_value7,t_value8,t_value9,t_value10;
END;
END IF;
IF rowCounterLoop >= numRowsOfCurs2 THEN
BEGIN
SET globalRowCounter := globalRowCounter+numRowsOfCurs2-1;
#FETCH NEXT FROM cur1 INTO t_tab_id,t_user_id,t_sum_value, t_value1,t_value2,t_value3,
#t_value4,t_value5,t_value6,t_value7,t_value8,t_value9,t_value10;
CLOSE cur2;
LEAVE subLoop;
END;
END IF;
END LOOP subLoop;
END BLOCK2;
END IF;
SET globalRowCounter := globalRowCounter+1;
IF globalRowCounter >= numRowsOfCurs1 THEN
LEAVE mainLoop;
END IF;
END LOOP mainLoop;
CLOSE cur1;
SELECT tab_id,user_id,sum_value,value1,value2,value3,value4,value5
,value6,value7,value8,value9,value10 FROM tab_nr_7;
SELECT tab_id,user_id,sum_value,value1,value2,value3,value4,value5
,value6,value7,value8,value9,value10 FROM t_table;
END $$
DELIMITER ;
CALL ranking();