SQL mix (all my SQL, huge library)

15.5.2023 • 17.3.2024 (aktualizováno)
MySql tricks MySql - advanced
mix.sql Raw #

/*
ulozeno jako 1,3,999,765 ...
*/

/*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


/* 20 latest unique records */
SELECT 
FROM 
  (
    SELECT 
      sparse, 
      MAX(ts) AS last_update 
    FROM 
      logfile 
    GROUP BY 
      sparse
  ) q 
ORDER BY 
  last_update DESC 
LIMIT



/* rank */

set @type := '', @num := 1;

select type, variety,
   @num := if(@type = type, @num + 1, 1) as row_number,
   @type := type as dummy
from fruits
order by type, variety;

+--------+------------+------------+--------+
| type   | variety    | row_number | dummy  |
+--------+------------+------------+--------+
| apple  | fuji       |          1 | apple  | 
| apple  | gala       |          2 | apple  | 
| apple  | limbertwig |          3 | apple  | 
| cherry | bing       |          1 | cherry | 
| cherry | chelan     |          2 | cherry | 
| orange | navel      |          1 | orange | 
| orange | valencia   |          2 | orange | 
| pear   | bartlett   |          1 | pear   | 
| pear   | bradford   |          2 | pear   | 
+--------+------------+------------+--------+

/* cislovani radku */

-- ve 2 SQL
set @radek=0; 
select @radek:=@radek+1 rownum, t.* FROM tabulka t ORDER BY t.body DESC;

-- by KAJMAN v 1 sql
select @radek:=@radek+1 rownum, t.* from (SELECT @radek:=0) r, tabulka t

/* Nejnovejsi komentare a clanky k nim s limitem */

SELECT clanky.nadpis, com.id, com.datum
FROM clanky, 

  (
  SELECT id, id_cl, datum
  FROM komentare
  ORDER BY id DESC
  )com
  
WHERE clanky.id = com.id_cl
GROUP BY clanky.id
ORDER BY com.datum
LIMIT 10 ;


/* concat subselect s limitem */
select group_concat(uzivatele order by registrace desc)
from (select uzivatele, registrace from uzivatele order by registrace desc limit 0,10) tmp

/* update case ..., update elt/field https://diskuse.jakpsatweb.cz/?action=vthread&forum=28&topic=143350 */

update `dostupnostKniha` join
       (select 1 id, 10 k
        union
        select 2 id, 20 k
        union
        select 3 id, 30 k
        union
        select 4 id, 40 k) dbtmp on `dostupnostKniha`.id = dbtmp.id and `dostupnostKniha`.idDostupnosti = 2
set     `dostupnostKniha`.pocetKusu = dbtmp.k, `dostupnostKniha`.datumUpdate = '$datum'


UPDATE dostupnostKniha
SET    datumUpdate = '$datum', pocetKusu = Elt(Field(id, 1, 2), 10, 20)
WHERE  id IN ( 1, 2 ) and idDostupnosti = 2


/* Náhrada za windows funkce */
SELECT id_student, id_class, grade,
   @student:=CASE WHEN @class <> id_class THEN 0 ELSE @student+1 END AS rn,
   @class:=id_class AS clset
FROM
  (SELECT @student:= -1) s,
  (SELECT @class:= -1) c,
  (SELECT *
   FROM mytable
   ORDER BY id_class, id_student
  ) t
  
  
  /* IN spojeni 2 a vice hodnot */
  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


/* lepsi spojeni v subselectu nez normalne pres JOIN */
EXPLAIN SELECT gb . * , hraci.prezdivka
FROM hraci, (

SELECT *
FROM gb2
ORDER BY id DESC
LIMIT 3
)gb
WHERE hraci.id = gb.jmeno

/* limit v poddotaze */
SELECT * FROM (SELECT * FROM `tabulka` ORDER BY `poradi_vlozeni` DESC LIMIT 20) vybrane ORDER BY RAND() LIMIT 10

/* mazani duplicit, vic zpusobu */
delete from dupes
where id not in (select min(id) from dupes group by name)

-- Nezapoměňte si data zazálohovat. Pak jsou obvyklé dva způsoby.
-- 1) vytvořit si dočasný unikátní index, díky ingore se provede i na duplicitními sloupečky a další opakování vyhodí

ALTER IGNORE TABLE tabulka ADD UNIQUE INDEX docasny123 (sloupecek);
ALTER TABLE tabulka DROP INDEX docasny123;

-- 2) použít delete using s propojením tabulky se samo sebou - potřebujete ale nějaký jedinečný sloupeček, 
-- tím si můžete i určit, zda chcete např. ponechat nejstarší nebo nejmladší hodnotu (nejvyšší id jako v příkladu)

DELETE FROM t1 USING tabulka t1, tabulka t2 WHERE t1.sloupecek=t2.sloupecek AND t1.id<t2.id


/* update join */
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


/** tree without with recursive */
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+


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


/* WEBFAQ, poradi, statistiky */
SET @poradi:=0,@poradib:=0;
SELECT @poradi:=@poradi+1, @poradi as poradi, IF(id=201, @poradib:=@poradi, 0), id, name FROM table ORDER BY `name` DESC;
SELECT @poradib;
-- kajman ;)  
select count(*)+1 poradi from tabulka t where t.rok=2008 and t.golu>(select j.golu where j.rok=2008 and j.hrac=‚Jágr‘)
select count(*)+1 from (select sum(gol) golu group by hrac having golu>$goly_jagra)


/* CASE or ELT */
UPDATE `table` SET uid = ELT(id, 2952, 4925, 1592) WHERE id IN (1,2,3)
-- or
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)


/* Nejvyssi clanky v kateg. */
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)


/* rank, fotbalova tabulka */
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


/* poradi nad a pod konkretnim poradim */
SELECT a . * 
FROM tabulky a, tabulky b
WHERE b.tym =11  /* nahodny tym, ktery ma pozici 6 */
AND (
a.poradi +1 = b.poradi
OR a.poradi = b.poradi
OR a.poradi = b.poradi +1
)
ORDER BY poradi



/* rank with FIND_IN_SET */
Id  Name    Score
1   Ida     100
2   Boo     58
3   Lala    88
4   Bash    102
5   Assem   99

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

Getting a single person score:

SELECT id, name, score, FIND_IN_SET( score, (    
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'
Gives this result:

id name score rank
5 Assem 99 3


/* GW ladder, rank similar values */
# za vcerejsek
SELECT 
  * 
FROM 
  vysledky 
WHERE 
  date_format(datum, '%Y-%m-%d') = (
    CURDATE() - INTERVAL 1 DAY
  ) # za dnesek
SELECT 
  * 
FROM 
  vysledky 
WHERE 
  date_format(datum, '%Y-%m-%d') = CURDATE() # starsi nez 2 dny od dneska
SELECT 
  * 
FROM 
  vysledky 
WHERE 
  date_format(datum, '%Y-%m-%d') < (
    CURDATE() - INTERVAL 1 DAY
  ) 
order by 
  id_zapasu DESC 
limit 
  10 # vytah 
SELECT 
  count(*) + 1 
FROM 
  `tabulka_tymu_ladder` l 
  LEFT JOIN `tabulka_tymu_ladder` a ON (
    a.id_hrace = 1 
    AND a.id_souteze = 7
  ) 
WHERE 
  l.id_souteze = 7 
  AND (
    l.koeficient > a.koeficient 
    OR (
      l.koeficient = a.koeficient 
      AND (
        l.pts > a.pts 
        OR (
          l.pts = a.pts 
          AND (
            (l.gf - l.ga) > (a.gf - a.ga) 
            OR (
              (l.gf - l.ga) = (a.gf - a.ga) 
              AND (
                l.pz > a.pz 
                OR (
                  l.pz = a.pz 
                  AND l.gf > a.gf
                )
              )
            )
          )
        )
      )
    )
  ) 
SELECT 
  count(*) + 1 
FROM 
  `tabulka_tymu_ladder` l, 
  `tabulka_tymu_ladder` a 
WHERE 
  a.id_hrace = 1 
  AND a.id_souteze = 8 
  AND l.id_souteze = 8 
  AND (
    l.koeficient > a.koeficient 
    OR (
      l.koeficient = a.koeficient 
      AND (
        l.pts > a.pts 
        OR (
          l.pts = a.pts 
          AND (
            (l.gf - l.ga) > (a.gf - a.ga) 
            OR (
              (l.gf - l.ga) = (a.gf - a.ga) 
              AND (
                l.pz > a.pz 
                OR (
                  l.pz = a.pz 
                  AND l.gf > a.gf
                )
              )
            )
          )
        )
      )
    )
  )


/* prioritni order by */
SELECT * FROM staty order by (nazev='Česká republika') DESC,
(nazev='Slovenská republika') DESC, nazev 

/* vnoreny dotaz v insert select */
INSERT INTO tab1 (id,desription) VALUES((SELECT id FROM tab2 WHERE name='neco'),'blablalba')

/*
https://stackoverflow.com/questions/12992793/efficient-ways-to-calculate-ranks-for-over-350-000-users?noredirect=1&lq=1
*/ 
UPDATE `Members`
SET `RankNum` = (@newRank := @newRank + 1)
ORDER BY `Rank` DESC, `Id` ASC, @newRank := 0


/*
http://explainextended.com/2009/04/07/keeping-rows/
mazani s limitem
*/
DELETE  d.*
FROM    t_deleter d
LEFT JOIN
        (
        SELECT  id
        FROM    t_deleter
        LIMIT   10
        ) q
ON      d.id = q.id
WHERE   q.id IS NULL


/* ANY, ALLL */
Zkuste si udělat tabulku tabulka se sloupcem sloupec a v ní hodnoty 1 a 2.

select * from tabulka where sloupec >= any (select sloupec from tabulka)
vrátí oba řádky ale
select * from tabulka where sloupec >= all (select sloupec from tabulka)
vrátí jen řádek s dvojkou, protože 1 není větší než všechny hodnoty z poddotazu


/* Nalezení položek mající všechny hledané vlastnosti */
select id_polozky
from   vazebni_tabulka
where  id_vlastnosti in (1, 5, 7)
group  by id_polozky
having count(distinct id_vlastnosti) = 3 -- počet jedinečných hodnot ze závorky


-- update when then
UPDATE categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)


-- Multiple updates
-- with insert or duplicate key
INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);

-- or with union
UPDATE my_table m
JOIN (
    SELECT 1 as id, 10 as _col1, 20 as _col2
    UNION ALL
    SELECT 2, 5, 10
    UNION ALL
    SELECT 3, 15, 30
) vals ON m.id = vals.id
SET col1 = _col1, col2 = __col2;

-- next solutions
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)

UPDATE table SET uid = ELT(id, 2952, 4925, 1592) WHERE id IN (1,2,3)


/* left join with correlated and dynamic cond */
-- https://explainextended.com/2010/10/20/correlated-left-join-in-a-subquery/
SELECT  g.id, g.name, GROUP_CONCAT(p.name SEPARATOR ', ') AS players
FROM    game g
LEFT JOIN
player_team pt
ON      g.type = 'team'
AND pt.team = g.player1
JOIN    player p
ON      p.id = CASE g.type WHEN 'player' THEN g.player1 WHEN 'team' THEN pt.player END
GROUP BY
g.id
LIMIT 50


/* El dragon stats */
SELECT tmp.prezdivka, tmp.prijmeni, tmp.pocet_vysledky, tmp.pocet_clanky, tmp.pocet_forum, (
tmp.pocet_forum + tmp.pocet_vysledky + tmp.pocet_clanky
) AS CELKEM
FROM (

SELECT a.prezdivka, a.prijmeni, (

SELECT COUNT( * ) 
FROM forum_prispevky
WHERE registrovany_uzivatel = a.ID
) AS pocet_forum, (

SELECT COUNT( * ) 
FROM komentare_clanky
WHERE registrovany_uzivatel = a.ID
) AS pocet_clanky, (

SELECT COUNT( * ) 
FROM komentare_vysledky
WHERE registrovany_uzivatel = a.ID
) AS pocet_vysledky
FROM uzivatele AS a
) AS tmp

ORDER BY CELKEM DESC