/ Gists

Gists

On gists

Ranks - djpw

MySql MySql tricks

ranks.sql #

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
On gists

Nejblizsi vyssi / nizsi zaznam

MySql MySql tricks

example.sql #

SELECT a.id AS nejblizsi_nizsi, b.id AS nejblizsi_vyssi 
FROM `tabulka` a JOIN `tabulka` b ON (a.id < $id AND b.id>$id) 
ORDER BY (b.id-a.id) ASC LIMIT 1
On gists

Nejvyšší články v kategoriích

MySql MySql tricks

max.sql #

select * from clanky where (kategorie, datum) in (select kategorie, max(datum) from clanky group by kategorie)





SELECT 
t1.id, 
t1.nadpis,
t2.datum,
t1.datum,
t2.kategorie
FROM clanky t1
JOIN  ( SELECT t2.kategorie, MAX(t2.datum) datum FROM clanky t2 GROUP BY t2.kategorie) t2
ON t1.kategorie = t2.kategorie
AND t1.datum = t2.datum







select * from clanky t1
where t1.id in (select max(t2.id) id from clanky t2 group by t2.kategorie)
On gists

Equal rank with same values

MySql MySql tricks

example.sql #

id   game_id   game_rank   score   team_id
-------------------------------------------
5    1         1           15      1
4    1         2           25      2
1    1         3           40      3
3    1         3           40      4
2    1         4           55      5
7    2         0           0       1
6    2         0           0       2


#1
SET @lastscore = 0;
SET @ordering = 0;

    UPDATE game_scores 
      SET
        game_rank = IF(score = @lastscore, @lastscore, (@ordering := @ordering + 1))
        , score = (@lastscore := score)
    WHERE game_id = 1
    ORDER BY score;
    
    
#2
UPDATE game_scores 
  CROSS JOIN ( SELECT @lastscore:=0, @ordering:=0) AS parameter
  SET
    game_rank = IF(score = @lastscore, @lastscore, (@ordering := @ordering + 1))
    , score = (@lastscore := score)
WHERE game_id = 1
ORDER BY score;


#3
SET @lastscore := 0;
SET @ordering := 0;

UPDATE game_scores SET 
IF(@lastscore = score, @ordering, @ordering := @ordering + 1),
game_rank = @ordering,
@lastscore := score
WHERE game_id = 1
ORDER BY score;
On gists

Multiple update with THEN / ELT

MySql MySql tricks

update.sql #

#1 
UPDATE `table` SET `uid` = CASE
    WHEN id = 1 THEN 2952
    WHEN id = 2 THEN 4925
    WHEN id = 3 THEN 1592
    ELSE `uid`
    END
WHERE id  in (1,2,3)


#2
On gists

Subcategories (limited level)

MySql MySql tricks

example.sql #

select l0.catID,
    concat(
      case when l5.catID is null then '' else concat(l5.category, '/') end
    , case when l4.catID is null then '' else concat(l4.category, '/') end
    , case when l3.catID is null then '' else concat(l3.category, '/') end
    , case when l2.catID is null then '' else concat(l2.category, '/') end
    , case when l1.catID is null then '' else concat(l1.category, '/') end
    , l0.category)
from catcat l0
left join catcat l1 on l0.parentID=l1.catID
left join catcat l2 on l1.parentID=l2.catID
left join catcat l3 on l2.parentID=l3.catID
left join catcat l4 on l3.parentID=l4.catID
left join catcat l5 on l4.parentID=l5.catID
On gists

Update/Delete with JOIN

MySql MySql tricks

example.sql #

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php




mysql> DELETE cds 
    -> FROM cds, artists 
    -> WHERE (cds.artistID = artists.artistID) 
    -> AND (cds.artistID = '3');

To DELETE from both tables:

mysql> DELETE cds, artist 
    -> FROM cds, artist
    -> WHERE (cds.artistID = artists.artistID) 
    -> AND (cds.artistID = '3');



===========



mysql> UPDATE cds 
    -> LEFT JOIN artists 
    -> USING (artistID)
    -> SET cds.artists = artists.Artist
    -> WHERE (cds.cdID = '2');


UPDATE videogallery_komentare t1 JOIN videogallery t2 ON t2.code = t1.code
SET t1.videoID = t2.ID
On gists

Find In set (multiple values are stoered like string in one column)

MySql MySql tricks

example.sql #

*******************************
Spojeni 2 tabulek, cisla zapasu jsou jako string v 1 sloupci, 
potrebujeme je vypsat ale pres IN( + subselect) to skutecne nejde, bere se to jako string
pomuze fce FIND_IN_SET a trosku netradicni pouziti
*******************************



/*stare spojeni*/

select z.* from 
_zapasy z, _souteze s 
where find_in_set(z.id,s.zapasy) and s.id=1


/* JOIN spojeni, 2 druhy */

select * from _zapasy z join _souteze s on s.id=1 where find_in_set(z.id,s.zapasy)
select * from _zapasy z join _souteze s ON find_in_set(z.id,s.zapasy) WHERE  s.id=1
select * from _zapasy z join _souteze s ON find_in_set(z.id,s.zapasy) AND s.id=1



-- 
-- Struktura tabulky `_zapasy`
-- 

CREATE TABLE `_zapasy` (
  `id` mediumint(9) NOT NULL auto_increment,
  `kdo` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- 
-- Vypisuji data pro tabulku `_zapasy`
-- 

INSERT INTO `_zapasy` (`id`, `kdo`) VALUES (1, 'brno - olomouc'),
(2, 'jablonec - plzen');



-- 
-- Struktura tabulky `_souteze`
-- 

CREATE TABLE `_souteze` (
  `id` int(11) default NULL,
  `nazev` varchar(255) default NULL,
  `zapasy` varchar(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 
-- Vypisuji data pro tabulku `_souteze`
-- 

INSERT INTO `_souteze` (`id`, `nazev`, `zapasy`) VALUES (1, 'test-liga', '1,2');
On gists

IN(multiple values)

MySql MySql tricks

in.sql #

select ID_osoby
from   co_si_pucil
where  (ID_polozky,druh) in ((11043,'kniha'),(9443,'film'))
group  by ID_osoby
having count(distinct ID_polozky, druh) = 2 -- pocet hledanych kombinaci
On gists

Soccer table on the fly.sql

•
MySql MySql tricks

table.sql #

select 
    team, 
    count(*) played, 
    count(case when goalsfor > goalsagainst then 1 end) wins, 
    count(case when goalsagainst> goalsfor then 1 end) lost, 
    count(case when goalsfor = goalsagainst then 1 end) draws, 
    sum(goalsfor) goalsfor, 
    sum(goalsagainst) goalsagainst, 
    sum(goalsfor) - sum(goalsagainst) goal_diff,
    sum(
          case when goalsfor > goalsagainst then 3 else 0 end 
        + case when goalsfor = goalsagainst then 1 else 0 end
    ) score 
from (
    select hometeam team, goalsfor, goalsagainst from scores 
  union all
    select awayteam, goalsagainst, goalsfor from scores
) a 
group by team
order by score desc, goal_diff desc;