/
Gists
/
MySql tricks
Gists - MySql tricks
On gists
MySql
MySql tricks
Find In set (multiple values are stoered like string in one column)
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
MySql
MySql tricks
IN(multiple values)
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
MySql
MySql tricks
first/least/max row per group in SQL
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
MySql
MySql tricks
Ranking variables (2 ways)
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
MySql
MySql tricks
Ranking via variables
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
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)