/ Gists / MySql

Gists - MySql

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

first/least/max row per group in SQL

MySql MySql tricks

baron-schwartz.sql #

+--------+------------+-------+
| type   | variety    | price |
+--------+------------+-------+
| apple  | gala       |  2.79 | 
| apple  | fuji       |  0.24 | 
| apple  | limbertwig |  2.87 | 
| orange | valencia   |  3.59 | 
| orange | navel      |  9.36 | 
| pear   | bradford   |  6.05 | 
| pear   | bartlett   |  2.14 | 
| cherry | bing       |  2.55 | 
| cherry | chelan     |  6.33 | 
+--------+------------+-------+


#1 self join
select f.type, f.variety, f.price
from (
   select type, min(price) as minprice
   from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;


#2 correlated subquery, less efficient
select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type);


+--------+----------+-------+
| type   | variety  | price |
+--------+----------+-------+
| apple  | fuji     |  0.24 | 
| orange | valencia |  3.59 | 
| pear   | bartlett |  2.14 | 
| cherry | bing     |  2.55 | 
+--------+----------+-------+


###################################
# $n from each
###################################

#1  self join?
select type, variety, price
from fruits
where (
   select count(*) from fruits as f
   where f.type = fruits.type and f.price <= fruits.price
) <= 2;



#2 union
(select * from fruits where type = 'apple' order by price limit 2)
union all
(select * from fruits where type = 'orange' order by price limit 2)
union all
(select * from fruits where type = 'pear' order by price limit 2)
union all
(select * from fruits where type = 'cherry' order by price limit 2)


#3a variables 
set @num := 0, @type := '';

select type, variety, price
from (
   select type, variety, price,
      @num := if(@type = type, @num + 1, 1) as row_number,
      @type := type as dummy
  from fruits
  order by type, price
) as x where x.row_number <= 2;


#3b variables with force without parent subquery
set @num := 0, @type := '';

select type, variety, price,
      @num := if(@type = type, @num + 1, 1) as row_number,
      @type := type as dummy
from fruits force index(type)
group by type, price, variety
having row_number <= 2;
On gists

Ranking variables (2 ways)

MySql MySql tricks

ranking.sql #

#1 way
set @radek=0; 
select @radek:=@radek+1 rownum, t.* FROM tabulka t ORDER BY t.body DESC;


#2 way
select @radek:=@radek+1 rownum, t.* from (SELECT @radek:=0) r, tabulka t



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

Ranking via variables

MySql MySql tricks

ranking.sql #

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

select type, variety, price, row_number
from (
   select type, variety, price,
       @num := if(@type = type, @num + 1, 1) as row_number,
       @type := type as dummy
   from fruits
) as x
where row_number <= 2;

+--------+----------+-------+------------+
| type   | variety  | price | row_number |
+--------+----------+-------+------------+
| apple  | gala     |  2.79 |          1 | 
| apple  | fuji     |  0.24 |          2 | 
| orange | valencia |  3.59 |          1 | 
| orange | navel    |  9.36 |          2 | 
| pear   | bradford |  6.05 |          1 | 
| pear   | bartlett |  2.14 |          2 | 
| cherry | bing     |  2.55 |          1 | 
| cherry | chelan   |  6.33 |          2 | 
+--------+----------+-------+------------+
On gists

Maximum value by each row

•
Popular ⭐ MySql MySql - advanced Nette-Tricks

structure.sql #


CREATE TABLE shop (
    article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
    
    

    
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
On gists

Prev / Next row - mysql select - variables, corelated subquery

•
MySql MySql tricks

scheme.sql #

create table example(
  id int not null primary key,
  value varchar(50)
);

insert into example
values (0,100),(2,150),(3,200),(6,250),(7,300)