On gists

Ranks - djpw

MySql MySql tricks

ranks.sql Raw #

http://diskuse.jakpsatweb.cz/index.php?action=vthread&topic=97602&forum=28&page=-1



1/ uzivatelske promenne


SET @minule := 0;
SET @poradi := 0;
SET @radek := 1;
UPDATE 
    poradi, 
    (
        SELECT 
            tym,
            body,
            @poradi := IF(@minule = body, @poradi, @radek) AS poradi,
            @radek := @radek + 1 AS radek,
            @minule := body AS tmp
        FROM poradi
        ORDER BY body DESC 
    ) AS vypocet
SET poradi.poradi = vypocet.poradi 
WHERE poradi.tym = vypocet.tym;




2/ Kajman , nejdrive nastavit pozice klasicky 1,2,3, .. a pak vybrat ty co jsou vicekrat a znova update 

update tabulka,
       (select body1, body2, body3, min(poradi) poradi
        from   tabulka
        group  by body1, body2, body3
        having count(*) > 1) as vypocet
set    tabulka.poradi = vypocet.poradi
where  tabulka.body1 = vypocet.body1
       and tabulka.body2 = vypocet.body2
       and tabulka.body3 = vypocet.body3



3/ za behu korelovany dotaz 

SELECT SQL_NO_CACHE
   (SELECT COUNT(*) + 1 FROM poradi as t1 WHERE t1.body > t2.body) AS rank, t2.tym, t2.body
    FROM poradi as t2


4/ Moje procedura :)

CREATE  PROCEDURE `SP_poradi`()
BEGIN

DECLARE s_poradi INT(5);
DECLARE s_body INT(5);
DECLARE temp_body INT(5) DEFAULT 0;
DECLARE s_tym VARCHAR(50);
DECLARE my_rank INT(5) DEFAULT 0;
DECLARE done TINYINT(1) DEFAULT 0;

DECLARE position TINYINT DEFAULT 0;

DECLARE i INT;

DECLARE rank CURSOR FOR
SELECT tym, body, poradi FROM poradi
ORDER BY body DESC;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET i = 0;
OPEN rank;
rank_loop:  LOOP

FETCH rank INTO s_tym, s_body, s_poradi;
IF done THEN LEAVE rank_loop; END IF;

SET i=i+1;

IF (temp_body <> s_body)
  THEN SET position = i;
ELSE
  SET position = position;
END IF;


UPDATE poradi SET poradi = position WHERE tym = s_tym;

SET temp_body = s_body;  


END LOOP rank_loop;
CLOSE rank;

END