/
Gists
/
MySql
Gists - MySql
On gists
MySql
MySql tricks
Insert A Random String from List in MySQL
gistfile1.txt #
UPDATE tablename
SET columnname = ELT(0.5 + RAND() * 6, 'value 1','value 2','value 3','value 4','value 5','value 6')
On gists
MySql
MySql tricks
Rand number between (start, end)
random-number-between.sql #
-- For range (min..max( (min inclusive, max exclusive) it is:
FLOOR( RAND() * (max-min) + min )
-- For range (min..max) (min+max inclusive) it is:
FLOOR( RAND() * (max-min+1) + min )
On gists
MySql
MySql tricks
Functions - examples
Functions.sql #
Basic MySQL CREATE FUNCTION statement
A very basic CREATE FUNCTION example which will produced the famed 'Hello World' output:
DELIMITER $$
CREATE FUNCTION hello_world()
RETURNS TEXT
LANGUAGE SQL
BEGIN
RETURN 'Hello World';
END;
$$
DELIMITER ;
Execute this function as follows:
mysql> SELECT hello_world();
+---------------+
| hello_world() |
+---------------+
| Hello World |
+---------------+
1 row in set (0.00 sec)
MySQL function with a parameter
Customize your 'Hello World' output with input from a parameter:
DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(addressee TEXT)
RETURNS TEXT
LANGUAGE SQL -- This element is optional and will be omitted from subsequent examples
BEGIN
RETURN CONCAT('Hello ', addressee);
END;
$$
DELIMITER ;
Execute as follows:
mysql> SELECT hello_world('Earth');
+----------------------+
| hello_world('Earth') |
+----------------------+
| Hello Earth |
+----------------------+
1 row in set (0.00 sec)
MySQL function with a local variable
Use a local variable to perform calculations inside your function:
DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(addressee TEXT)
RETURNS TEXT
BEGIN
DECLARE strlen INT;
SET strlen = LENGTH(addressee);
RETURN CONCAT('Hello ', addressee, ' - your parameter has ', strlen, ' characters');
END;
$$
DELIMITER ;
Output:
mysql> SELECT hello_world('Earth');
+-----------------------------------------------+
| hello_world('Earth') |
+-----------------------------------------------+
| Hello Earth - your parameter has 5 characters |
+-----------------------------------------------+
1 row in set (0.00 sec)
MySQL function with a loop
DROP FUNCTION IF EXISTS looptest;
DELIMITER $$
CREATE FUNCTION looptest()
RETURNS INT READS SQL DATA
BEGIN
DECLARE v_total INT;
SET v_total = 0;
count_loop: LOOP
SET v_total = v_total + 1;
IF v_total = 10 THEN
LEAVE count_loop;
END IF;
END LOOP;
RETURN v_total;
END;
$$
DELIMITER ;
MySQL function with a loop and cursor
It is possible - if somewhat inelegant - to iterate through a CURSOR using a loop:
DROP FUNCTION IF EXISTS looptest;
DELIMITER $$
CREATE FUNCTION looptest()
RETURNS INT READS SQL DATA
BEGIN
DECLARE v_total INT;
DECLARE v_counter INT;
DECLARE done INT DEFAULT FALSE;
DECLARE csr CURSOR FOR
SELECT counter FROM items;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET v_total = 0;
OPEN csr;
read_loop: LOOP
FETCH csr INTO v_counter;
IF done THEN
LEAVE read_loop;
END IF;
SET v_total = v_total + v_counter;
END LOOP;
CLOSE csr;
RETURN v_total;
END;
$$
DELIMITER ;
On gists
MySql
MySql tricks
Dense rank / rank (joins)
dense.sql #
SELECT COUNT(id) + 1 AS rank, COUNT(DISTINCT age) + 1 AS dense_rank
FROM testdata
WHERE gender = (SELECT gender FROM testdata WHERE id = 6)
AND age < (SELECT age FROM testdata WHERE id = 6)
SELECT testdata.id, COUNT(lesser.id) + 1 AS rank, COUNT(DISTINCT lesser.age) + 1 AS dense_rank
FROM testdata
LEFT JOIN testdata AS lesser ON lesser.age < testdata.age AND lesser.gender = testdata.gender
GROUP BY testdata.id
SELECT a.first_name,
a.age,
a.gender,
count(b.age)+1 as rank
FROM person a left join person b on a.age>b.age and a.gender=b.gender
group by a.first_name,
a.age,
a.gender
On gists
MySql
MySql tricks
Ranks - many cases
ranks.sql #
DROP TABLE IF EXISTS users;
CREATE TABLE users (user_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), start_date DATE, team_id INT);
INSERT INTO users (name, start_date, team_id) VALUES ('Matt', '2017-01-01', 1);
INSERT INTO users (name, start_date, team_id) VALUES ('John', '2017-01-02', 2);
INSERT INTO users (name, start_date, team_id) VALUES ('Sara', '2017-01-02', 2);
INSERT INTO users (name, start_date, team_id) VALUES ('Tim', '2017-01-02', 3);
INSERT INTO users (name, start_date, team_id) VALUES ('Bob', '2017-01-03', 3);
INSERT INTO users (name, start_date, team_id) VALUES ('Bill', '2017-01-04', 3);
INSERT INTO users (name, start_date, team_id) VALUES ('Kathy', '2017-01-04', 3);
INSERT INTO users (name, start_date, team_id) VALUES ('Anne', '2017-01-05', 3);
+---------+-------+------------+---------+
| user_id | name | start_date | team_id |
+---------+-------+------------+---------+
| 1 | Matt | 2017-01-01 | 1 |
| 2 | John | 2017-01-02 | 2 |
| 3 | Sara | 2017-01-02 | 2 |
| 4 | Tim | 2017-01-02 | 3 |
| 5 | Bob | 2017-01-03 | 3 |
| 6 | Bill | 2017-01-04 | 3 |
| 7 | Kathy | 2017-01-04 | 3 |
| 8 | Anne | 2017-01-05 | 3 |
+---------+-------+------------+---------+
# Ranked by start date
SELECT *
FROM users
ORDER by start_date ASC
# Ranked by start date with ties broken by user id
SELECT *
FROM users
ORDER by start_date ASC, user_id ASC
# First employee by start date with ties broken by user id
SELECT *
FROM users
ORDER by start_date ASC, user_id ASC
LIMIT 1
# First employee by start date with ties
SELECT *
FROM users
WHERE start_date = (SELECT MIN(start_date) FROM users);
# Second employee by start date with ties broken by user id
SELECT *
FROM users
ORDER by start_date ASC, user_id ASC
LIMIT 1
OFFSET 1
# Second employee by start date with ties
SELECT *
FROM users
WHERE start_date = (
SELECT DISTINCT start_date
FROM users
ORDER BY start_date ASC
LIMIT 1
OFFSET 1
)
# Ranked by start date using variable
SET @rank := 0;
SELECT
*,
@rank := @rank + 1 AS rank
FROM users
ORDER BY start_date ASC
+---------+-------+------------+---------+------+
| user_id | name | start_date | team_id | rank |
+---------+-------+------------+---------+------+
| 1 | Matt | 2017-01-01 | 1 | 1 |
| 2 | John | 2017-01-02 | 2 | 2 |
| 3 | Sara | 2017-01-02 | 2 | 3 |
| 4 | Tim | 2017-01-02 | 3 | 4 |
| 5 | Bob | 2017-01-03 | 3 | 5 |
| 6 | Bill | 2017-01-04 | 3 | 6 |
| 7 | Kathy | 2017-01-04 | 3 | 7 |
| 8 | Anne | 2017-01-05 | 3 | 8 |
+---------+-------+------------+---------+------+
# Ranked by start date using a variable
# Based on this Stack Overflow comment:
SELECT
*,
@rank := @rank + 1 AS rank
FROM users, (SELECT @rank := 0) r
ORDER BY start_date ASC
+---------+-------+------------+---------+------------+------+
| user_id | name | start_date | team_id | @rank := 0 | rank |
+---------+-------+------------+---------+------------+------+
| 1 | Matt | 2017-01-01 | 1 | 0 | 1 |
| 2 | John | 2017-01-02 | 2 | 0 | 2 |
| 3 | Sara | 2017-01-02 | 2 | 0 | 3 |
| 4 | Tim | 2017-01-02 | 3 | 0 | 4 |
| 5 | Bob | 2017-01-03 | 3 | 0 | 5 |
| 6 | Bill | 2017-01-04 | 3 | 0 | 6 |
| 7 | Kathy | 2017-01-04 | 3 | 0 | 7 |
| 8 | Anne | 2017-01-05 | 3 | 0 | 8 |
+---------+-------+------------+---------+------------+------+
# First employee by start date using by setting a variable
SET @rank := 0;
SELECT *
FROM (
SELECT
*,
@rank := @rank + 1 AS rank
FROM users
ORDER BY start_date ASC
) ranked
WHERE rank = 1
# Ranked by start date with ties
# Based on this Stack Overflow comment:
SET @prev_start_date = NULL;
SET @rank := 0;
SELECT
*,
CASE
WHEN @prev_start_date = start_date THEN @rank
-- Note that the assignment here will always be true
WHEN @prev_start_date := start_date THEN @rank := @rank + 1
END AS rank
FROM users
ORDER BY start_date ASC
+---------+-------+------------+---------+------+
| user_id | name | start_date | team_id | rank |
+---------+-------+------------+---------+------+
| 1 | Matt | 2017-01-01 | 1 | 1 |
| 2 | John | 2017-01-02 | 2 | 2 |
| 3 | Sara | 2017-01-02 | 2 | 2 |
| 4 | Tim | 2017-01-02 | 3 | 2 |
| 5 | Bob | 2017-01-03 | 3 | 3 |
| 6 | Bill | 2017-01-04 | 3 | 4 |
| 7 | Kathy | 2017-01-04 | 3 | 4 |
| 8 | Anne | 2017-01-05 | 3 | 5 |
+---------+-------+------------+---------+------+
# Ranked by user id within each team
SELECT
a.*,
COUNT(*) AS rank
FROM users a
INNER JOIN users b
ON a.team_id = b.team_id AND a.user_id >= b.user_id
GROUP BY a.team_id, a.user_id
# or, based on this Stack Overflow comment:
SELECT
a.*,
(
SELECT COUNT(*)
FROM users b
WHERE a.team_id = b.team_id AND a.user_id >= b.user_id
) AS ranked
FROM users a
+---------+-------+------------+---------+--------+
| user_id | name | start_date | team_id | ranked |
+---------+-------+------------+---------+--------+
| 1 | Matt | 2017-01-01 | 1 | 1 |
| 2 | John | 2017-01-02 | 2 | 1 |
| 3 | Sara | 2017-01-02 | 2 | 2 |
| 4 | Tim | 2017-01-02 | 3 | 1 |
| 5 | Bob | 2017-01-03 | 3 | 2 |
| 6 | Bill | 2017-01-04 | 3 | 3 |
| 7 | Kathy | 2017-01-04 | 3 | 4 |
| 8 | Anne | 2017-01-05 | 3 | 5 |
+---------+-------+------------+---------+--------+
# Note that both of these techniques require that there be a column without duplicates that we can rank on within the partition. For example, we can’t use start_date due to the duplicates within team 2 (2017-01-02) and team 3 (2017-01-14):
SELECT
a.*,
COUNT(*) AS rank
FROM users a
INNER JOIN users b
ON a.team_id = b.team_id AND a.start_date >= b.start_date
GROUP BY a.team_id, a.user_id
+---------+-------+------------+---------+------+
| user_id | name | start_date | team_id | rank |
+---------+-------+------------+---------+------+
| 1 | Matt | 2017-01-01 | 1 | 1 |
| 2 | John | 2017-01-02 | 2 | 2 |
| 3 | Sara | 2017-01-02 | 2 | 2 |
| 4 | Tim | 2017-01-02 | 3 | 1 |
| 5 | Bob | 2017-01-03 | 3 | 2 |
| 6 | Bill | 2017-01-04 | 3 | 4 |
| 7 | Kathy | 2017-01-04 | 3 | 4 |
| 8 | Anne | 2017-01-05 | 3 | 5 |
+---------+-------+------------+---------+------+
# Return the last person to join within each team based on user id
# Based on this Stack Overflow comment:
SELECT a.*
FROM users a
LEFT JOIN users b
ON a.team_id = b.team_id AND a.user_id < b.user_id
WHERE b.team_id IS NULL
or
SELECT a.*
FROM users a
WHERE user_id IN (
SELECT MAX(user_id)
FROM users
GROUP BY team_id
)
+---------+------+------------+---------+
| user_id | name | start_date | team_id |
+---------+------+------------+---------+
| 1 | Matt | 2017-01-01 | 1 |
| 3 | Sara | 2017-01-02 | 2 |
| 8 | Anne | 2017-01-05 | 3 |
+---------+------+------------+---------+
# Return the last people to join within each team based on start date
SELECT a.*
FROM users a
LEFT JOIN users b
ON a.team_id = b.team_id AND a.start_date < b.start_date
WHERE b.team_id IS NULL
# or, based on this groupwise max post:
SELECT a.*
FROM users a
INNER JOIN (
SELECT team_id, MAX(start_date) AS max_start_date
FROM users b
GROUP BY team_id
) max_start_dates
ON a.team_id = max_start_dates.team_id AND a.start_date = max_start_dates.max_start_date
+---------+------+------------+---------+
| user_id | name | start_date | team_id |
+---------+------+------------+---------+
| 1 | Matt | 2017-01-01 | 1 |
| 2 | John | 2017-01-02 | 2 |
| 3 | Sara | 2017-01-02 | 2 |
| 8 | Anne | 2017-01-05 | 3 |
+---------+------+------------+---------+
# Ranked with gaps
# Based on this Stack Overflow comment:
SELECT
user_id,
name,
start_date,
team_id,
rank
FROM (
SELECT
*,
IF(start_date = @_last_start_date, @cur_rank := @cur_rank, @cur_rank := @_sequence) AS rank,
@_sequence := @_sequence + 1,
@_last_start_date := start_date
FROM users, (SELECT @cur_rank := 1, @_sequence := 1, @_last_start_date := NULL) r
ORDER BY start_date
) ranked
# Notice that after the three tied for second earliest start date, the next one jumps to 5 (not 3):
+---------+-------+------------+---------+------+
| user_id | name | start_date | team_id | rank |
+---------+-------+------------+---------+------+
| 1 | Matt | 2017-01-01 | 1 | 1 |
| 2 | John | 2017-01-02 | 2 | 2 |
| 3 | Sara | 2017-01-02 | 2 | 2 |
| 4 | Tim | 2017-01-02 | 3 | 2 |
| 5 | Bob | 2017-01-03 | 3 | 5 |
| 6 | Bill | 2017-01-04 | 3 | 6 |
| 7 | Kathy | 2017-01-04 | 3 | 6 |
| 8 | Anne | 2017-01-05 | 3 | 8 |
+---------+-------+------------+---------+------+
On gists
MySql
MySql tricks
Ranks - djpw
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
MySql
MySql tricks
Nejblizsi vyssi / nizsi zaznam
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
MySql
MySql tricks
Nejvyšší články v kategoriích
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
MySql
MySql tricks
Equal rank with same values
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
MySql
MySql tricks
Multiple update with THEN / ELT
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