/
Gists
/
MySql - advanced
Gists - MySql - advanced
On gists
MySql
MySql - advanced
League standings
table.sql #
DROP TABLE IF EXISTS teams, games;
CREATE TABLE teams(
id int primary key auto_increment,
tname char(32)
);
CREATE TABLE games(
id int primary key auto_increment,
date datetime,
hteam int,
ateam int,
hscore tinyint,
ascore tinyint
);
INSERT INTO teams VALUES
(1,'Wanderers'),(2,'Spurs'),
(3,'Celtics'),(4,'Saxons');
INSERT INTO games VALUES
(1,'2008-1-1 20:00:00',1,2,1,0),
(2,'2008-1-1 20:00:00',3,4,0,2),
(3,'2008-1-8 20:00:00',1,3,1,1),
(4,'2008-1-8 20:00:00',2,4,2,1);
SELECT * FROM teams;
+----+-----------+
| id | tname |
+----+-----------+
| 1 | Wanderers |
| 2 | Spurs |
| 3 | Celtics |
| 4 | Saxons |
+----+-----------+
SELECT * FROM games;
+----+---------------------+-------+-------+--------+--------+
| id | date | hteam | ateam | hscore | ascore |
+----+---------------------+-------+-------+--------+--------+
| 1 | 2008-01-01 20:00:00 | 1 | 2 | 1 | 0 |
| 2 | 2008-01-01 20:00:00 | 3 | 4 | 0 | 2 |
| 3 | 2008-01-08 20:00:00 | 1 | 3 | 1 | 1 |
| 4 | 2008-01-08 20:00:00 | 2 | 4 | 2 | 1 |
+----+---------------------+-------+-------+--------+--------+
-- Standings query:
SELECT
tname AS Team, Sum(P) AS P,
Sum(W) AS W,Sum(D) AS D,Sum(L) AS L,
SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,
SUM(Pts) AS Pts
FROM(
SELECT
hteam Team,
1 P,
IF(hscore > ascore,1,0) W,
IF(hscore = ascore,1,0) D,
IF(hscore < ascore,1,0) L,
hscore F,
ascore A,
hscore-ascore GD,
CASE
WHEN hscore > ascore THEN 3
WHEN hscore = ascore THEN 1
ELSE 0
END PTS
FROM games
UNION ALL
SELECT
ateam,
1,
IF(hscore < ascore,1,0),
IF(hscore = ascore,1,0),
IF(hscore > ascore,1,0),
ascore,
hscore,
ascore-hscore GD,
CASE
WHEN hscore < ascore THEN 3
WHEN hscore = ascore THEN 1
ELSE 0
END
FROM games
) as tot
JOIN teams t ON tot.Team=t.id
GROUP BY Team
ORDER BY SUM(Pts) DESC ;
+-----------+------+------+------+------+------+------+------+------+
| Team | P | W | D | L | F | A | GD | Pts |
+-----------+------+------+------+------+------+------+------+------+
| Wanderers | 2 | 1 | 1 | 0 | 2 | 1 | 1 | 4 |
| Spurs | 2 | 1 | 0 | 1 | 2 | 2 | 0 | 3 |
| Saxons | 2 | 1 | 0 | 1 | 3 | 2 | 1 | 3 |
| Celtics | 2 | 0 | 1 | 1 | 1 | 3 | -2 | 1 |
+-----------+------+------+------+------+------+------+------+------+
Last updated 22 May 2009
-- OR
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
Pivot table (2 zpusoby)
•
MySql
MySql - advanced
AW
pivot-table.sql #
-- https://diskuse.jakpsatweb.cz/?action=vthread&forum=28&topic=174158
-- Pivotni tabulka
-- Ja
SELECT user_id, (
SUM(IF (action_type = 'login', 1, 0))
) AS 'login', (
SUM(IF (action_type = 'publish', 1, 0))
) AS 'publish', (
SUM(IF (action_type = 'download', 1, 0))
) AS 'download', (
SUM(IF (action_type = 'upload', 1, 0))
) AS 'upload', (
SUM(IF (action_type = 'comment', 1, 0))
) AS 'comment',(
SUM(IF (action_type = 'share', 1, 0))
) AS 'share'
FROM ss_stats
GROUP BY user_id
HAVING user_id IS NOT NULL
-- Kajman
SELECT user_id,
( Sum(IF (action_type = 'login', pocet, 0)) ) AS 'login',
( Sum(IF (action_type = 'publish', pocet, 0)) ) AS 'publish',
( Sum(IF (action_type = 'download', pocet, 0)) ) AS 'download',
( Sum(IF (action_type = 'upload', pocet, 0)) ) AS 'upload',
( Sum(IF (action_type = 'comment', pocet, 0)) ) AS 'comment',
( Sum(IF (action_type = 'share', pocet, 0)) ) AS 'share'
FROM (SELECT user_id,
action_type,
Count(*) pocet
FROM ss_stats
GROUP BY action_type,
user_id) pocty
WHERE user_id IS NOT NULL
GROUP BY user_id
--- tttt; nejlepsi a nejrychlejsi
SELECT
user_id,
logins.count AS 'login',
publishes.count AS 'publish',
downloads.count AS 'download',
uploads.count AS 'upload',
"comments".count AS 'comment',
shares.count AS 'shares'
FROM
users
LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'login' GROUP BY user_id) logins USING(user_id)
LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'publish' GROUP BY user_id) publishes USING(user_id)
LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'download' GROUP BY user_id) downloads USING(user_id)
LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'upload' GROUP BY user_id) uploads USING(user_id)
LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'comment' GROUP BY user_id) "comments" USING(user_id)
LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'share' GROUP BY user_id) shares USING(user_id)
On gists
tutorial-Store-Procedures-Mysql.sql
•
MySql
MySql - advanced
tutorial-Store-Procedures-Mysql. #
------Creating Stored Procedures in MySQL------
--Make sure you have version 5 of MySQL:
SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.0.15-nt |
+-----------+
1 row in set (0.00 sec)
--First pick a database to use (a procedure, like a table, is associated with
--a single database.) For these examples, I will use a database that is populated
--with the tables from HW 2:
USE ozaidan_hw2;
--Next, change the delimiter, because we will use the semicolon WITHIN the
--procedure declarations, and therefore it cannot be the delimiter anymore:
DELIMITER //
--OK, let's get started. Creating procedures is straightforward:
CREATE PROCEDURE myFirstProc()
SELECT 'Hello World!' AS Output;
//
Query OK, 0 rows affected (0.00 sec)
--Whenever you create a procedure (successfully) you should get a 'Query OK' message.
--Calling a procedure is also straightforward:
CALL myFirstProc() //
+--------------+
| Output |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)
--By the way, procedure names are NOT case sensitive:
CALL myfirstproc() //
+--------------+
| Output |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)
--Another example:
CREATE PROCEDURE ListStudents()
SELECT *
FROM Student;
//
CALL ListStudents() //
+-------+----------+---------+------+------+-------+---------+-----------+
| StuID | LName | Fname | Age | Sex | Major | Advisor | city_code |
+-------+----------+---------+------+------+-------+---------+-----------+
| 1001 | Smith | Linda | 18 | F | 600 | 1121 | BAL |
| 1002 | Kim | Tracy | 19 | F | 600 | 7712 | HKG |
.
.
.
| 1034 | Epp | Eric | 18 | M | 50 | 5718 | BOS |
| 1035 | Schmidt | Sarah | 26 | F | 50 | 5718 | WAS |
+-------+----------+---------+------+------+-------+---------+-----------+
34 rows in set (0.00 sec)
--Say we only want student ID's and names. To update a procedure, we must
--first DROP it:
DROP PROCEDURE IF EXISTS ListStudents //
Query OK, 0 rows affected (0.00 sec)
--Again, whenever you drop a procedure, you should get a 'Query OK' message.
--From now on, we will always use "DROP PROCEDURE IF EXISTS procName" as
--a standard practice before declaring procedures:
DROP PROCEDURE IF EXISTS ListStudents //
CREATE PROCEDURE ListStudents()
SELECT StuID, LName, FName
FROM Student;
//
CALL ListStudents() //
+-------+----------+---------+
| StuID | LName | FName |
+-------+----------+---------+
| 1001 | Smith | Linda |
| 1002 | Kim | Tracy |
.
.
.
| 1034 | Epp | Eric |
| 1035 | Schmidt | Sarah |
+-------+----------+---------+
34 rows in set (0.00 sec)
--OK, let's use some parameters:
DROP PROCEDURE IF EXISTS sayHello //
CREATE PROCEDURE sayHello(IN name VARCHAR(20))
SELECT CONCAT('Hello ', name, '!') AS Greeting;
//
--The 'IN' keyword tells MySQL that is should be expecting an input value for
--the parameter......hunh? Why would a parameter NOT have an input value? You will
--see in a little bit. First, let's see if sayHello works:
CALL sayHello('Omar') //
+-------------+
| Greeting |
+-------------+
| Hello Omar! |
+-------------+
1 row in set (0.00 sec)
--Another example:
DROP PROCEDURE IF EXISTS saySomething //
CREATE PROCEDURE saySomething(IN phrase VARCHAR(20), IN name VARCHAR(20))
SELECT CONCAT(phrase, ' ', name, '!') AS Output;
//
CALL saySomething('Go','Blue Jays') //
CALL saySomething('Do','my homework') //
+---------------+
| Output |
+---------------+
| Go Blue Jays! |
+---------------+
1 row in set (0.00 sec)
+-----------------+
| Output |
+-----------------+
| Do my homework! |
+-----------------+
1 row in set (0.00 sec)
--and another one:
DROP PROCEDURE IF EXISTS FindStudent //
CREATE PROCEDURE FindStudent(IN id INT)
SELECT StuID, CONCAT(FName, ' ', LName) AS 'Student Name'
FROM Student
WHERE StuID = id;
//
CALL FindStudent(1001) //
+-------+--------------+
| StuID | Student Name |
+-------+--------------+
| 1001 | Linda Smith |
+-------+--------------+
1 row in set (0.00 sec)
--and yet another:
DROP PROCEDURE IF EXISTS calculate //
CREATE PROCEDURE calculate(IN x INT, IN y INT, OUT sum INT, OUT product INT)
SET sum = x + y;
SET product = x * y;
//
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual ...
--Well, that wasn't good. The reason is, we must use BEGIN/END if we have
--a compound statement:
DROP PROCEDURE IF EXISTS calculate //
CREATE PROCEDURE calculate(IN x INT, IN y INT, OUT sum INT, OUT product INT)
BEGIN
SET sum = x + y;
SET product = x * y;
END;
//
--Did you notice the 'OUT' keyword for sum and product? This tells MySQL that those
--two parameters are not 'input' parameters but are 'output' parameters instead.
--Now, when calling the procedure, we need to provide four parameters: two input
--values, and two MySQL *variables* where the results will be stored:
CALL calculate(4,5,@s,@p) //
Query OK, 0 rows affected (0.00 sec)
--Here, @s and @p are MySQL variables. Notice that they start with @, although
--procedure *parameters* do not start with @
SELECT @s //
SELECT @p //
+------+
| @s |
+------+
| 9 |
+------+
1 row in set (0.00 sec)
+------+
| @p |
+------+
| 20 |
+------+
1 row in set (0.00 sec)
--Note: you can also have INOUT parameters, which serve as both input and output
--parameters.
--OK, let's do some interesting stuff. First off, flow control:
DROP PROCEDURE IF EXISTS mySign //
CREATE PROCEDURE mySign(IN x INT)
BEGIN
IF x > 0 THEN
SELECT x AS Number, '+' AS Sign;
ELSEIF x < 0 THEN
SELECT x AS Number, '-' AS Sign;
ELSE
SELECT x AS Number, 'Zero' AS Sign;
END IF;
END;
//
CALL mySign(2) //
CALL mySign(-5) //
CALL mySign(0) //
+--------+------+
| Number | Sign |
+--------+------+
| 2 | + |
+--------+------+
1 row in set (0.00 sec)
+--------+------+
| Number | Sign |
+--------+------+
| -5 | - |
+--------+------+
1 row in set (0.00 sec)
+--------+------+
| Number | Sign |
+--------+------+
| 0 | Zero |
+--------+------+
1 row in set (0.00 sec)
--Before we get any further, let's introduce variables:
DROP PROCEDURE IF EXISTS mySign //
CREATE PROCEDURE mySign(IN x INT)
BEGIN
DECLARE result VARCHAR(20);
IF x > 0 THEN
SET result = '+';
ELSEIF x < 0 THEN
SET result = '-';
ELSE
SET result = 'Zero';
END IF;
SELECT x AS Number, result AS Sign;
END;
//
CALL mySign(2) //
CALL mySign(-5) //
CALL mySign(0) //
+--------+------+
| Number | Sign |
+--------+------+
| 2 | + |
+--------+------+
1 row in set (0.00 sec)
+--------+------+
| Number | Sign |
+--------+------+
| -5 | - |
+--------+------+
1 row in set (0.00 sec)
+--------+------+
| Number | Sign |
+--------+------+
| 0 | Zero |
+--------+------+
1 row in set (0.00 sec)
--Using CASE:
DROP PROCEDURE IF EXISTS digitName //
CREATE PROCEDURE digitName(IN x INT)
BEGIN
DECLARE result VARCHAR(20);
CASE x
WHEN 0 THEN SET result = 'Zero';
WHEN 1 THEN SET result = 'One';
WHEN 2 THEN SET result = 'Two';
WHEN 3 THEN SET result = 'Three';
WHEN 4 THEN SET result = 'Four';
WHEN 5 THEN SET result = 'Five';
WHEN 6 THEN SET result = 'Six';
WHEN 7 THEN SET result = 'Seven';
WHEN 8 THEN SET result = 'Eight';
WHEN 9 THEN SET result = 'Nine';
ELSE SET result = 'Not a digit';
END CASE;
SELECT x AS Digit, result AS Name;
END;
//
CALL digitName(0) //
CALL digitName(4) //
CALL digitName(100) //
+-------+------+
| Digit | Name |
+-------+------+
| 0 | Zero |
+-------+------+
1 row in set (0.00 sec)
+-------+------+
| Digit | Name |
+-------+------+
| 4 | Four |
+-------+------+
1 row in set (0.00 sec)
+-------+-------------+
| Digit | Name |
+-------+-------------+
| 100 | Not a digit |
+-------+-------------+
1 row in set (0.00 sec)
--As you'd expect, we have loops. For example, WHILE loops:
DROP PROCEDURE IF EXISTS fact //
CREATE PROCEDURE fact(IN x INT)
BEGIN
DECLARE result INT;
DECLARE i INT;
SET result = 1;
SET i = 1;
WHILE i <= x DO
SET result = result * i;
SET i = i + 1;
END WHILE;
SELECT x AS Number, result as Factorial;
END;
//
CALL fact(1) //
CALL fact(2) //
CALL fact(4) //
CALL fact(0) //
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 1 | 1 |
+--------+-----------+
1 row in set (0.00 sec)
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 2 | 2 |
+--------+-----------+
1 row in set (0.00 sec)
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 4 | 24 |
+--------+-----------+
1 row in set (0.01 sec)
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 0 | 1 |
+--------+-----------+
1 row in set (0.00 sec)
--There is also REPEAT/UNTIL loops:
DROP PROCEDURE IF EXISTS fact //
CREATE PROCEDURE fact(IN x INT)
BEGIN
DECLARE result INT DEFAULT 1; /* notice you can declare a variable*/
DECLARE i INT DEFAULT 1; /* and give it a value in one line */
REPEAT
SET result = result * i;
SET i = i + 1;
UNTIL i > x
END REPEAT;
SELECT x AS Number, result as Factorial;
END;
//
CALL fact(1) //
CALL fact(2) //
CALL fact(4) //
CALL fact(0) //
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 1 | 1 |
+--------+-----------+
1 row in set (0.00 sec)
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 2 | 2 |
+--------+-----------+
1 row in set (0.00 sec)
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 4 | 24 |
+--------+-----------+
1 row in set (0.00 sec)
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 0 | 1 |
+--------+-----------+
1 row in set (0.00 sec)
--OK, do you remember this?
/*
CREATE PROCEDURE FindStudent(IN id INT)
SELECT StuID, CONCAT(FName, ' ', LName) AS 'Student Name'
FROM Student
WHERE StuID = id;
//
*/
--What if we only want to extract the name without printing it out?
--Obviously, we need some OUT parameters. Still, how do you extract
--information into those OUT parameters?
--
--Answer: something called a CURSOR:
DROP PROCEDURE IF EXISTS FindName //
CREATE PROCEDURE FindName(IN id INT, OUT fn VARCHAR(20), OUT ln VARCHAR(20))
BEGIN
DECLARE cur CURSOR FOR
SELECT FName, LName
FROM Student
WHERE StuID = id;
OPEN cur;
FETCH cur INTO fn, ln;
CLOSE cur;
END;
//
CALL FindName(1001,@f,@l) //
Query OK, 0 rows affected (0.00 sec)
--Remember that @f and @l are MySQL variables:
SELECT @f //
SELECT @l //
+-------+
| @f |
+-------+
| Linda |
+-------+
1 row in set (0.00 sec)
+-------+
| @l |
+-------+
| Smith |
+-------+
1 row in set (0.00 sec)
--What if we give an invalid student ID?
CALL FindName(0000,@f,@l) //
ERROR 1329 (02000): No data to FETCH
--MySQL complains, as expected. It would be nice to handle this more elegantly, however.
--We need an error HANDLER. Let's modify FindName:
DROP PROCEDURE IF EXISTS FindName //
CREATE PROCEDURE FindName(IN id INT, OUT fn VARCHAR(20), OUT ln VARCHAR(20))
BEGIN
DECLARE cur CURSOR FOR
SELECT FName, LName
FROM Student
WHERE StuID = id;
DECLARE EXIT HANDLER FOR NOT FOUND
SELECT 'Sorry; this ID was not found' AS 'Error Message';
OPEN cur;
FETCH cur INTO fn, ln;
CLOSE cur;
END;
//
CALL FindName(0000,@f,@l) //
+------------------------------+
| Error Message |
+------------------------------+
| Sorry; this ID was not found |
+------------------------------+
1 row in set (0.00 sec)
--Another use for handlers: multiple FETCH calls using a CONTINUE handler.
--
--In this case, we use a CONTINUE handler that, instead of exiting the procedure
--upon encountering a NOT FOUND error, simply sets a variable done = 1.
--
--Why would we do that? And how does that help us carry out multiple FETCH calls?
--
--Take a look at this procedure, which traverses all the entries of a table to
--find the maximum and minimum age:
DROP PROCEDURE IF EXISTS MaxMinAge //
CREATE PROCEDURE MaxMinAge(OUT maxAge INT, OUT minAge INT)
BEGIN
DECLARE currAge,maxSoFar,minSoFar,done INT;
DECLARE cur CURSOR FOR
SELECT Age
FROM Student;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = 1;
SET maxSoFar = 0;
SET minSoFar = 1000;
SET done = 0;
OPEN cur;
WHILE done = 0 DO
FETCH cur INTO currAge;
IF currAge > maxSoFar THEN
SET maxSoFar = currAge;
END IF;
IF currAge < minSoFar THEN
SET minSoFar = currAge;
END IF;
END WHILE;
CLOSE cur;
SET maxAge = maxSoFar;
SET minAge = minSoFar;
END;
//
CALL MaxMinAge(@max,@min) //
Query OK, 0 rows affected (0.00 sec)
SELECT @max //
SELECT @min //
+------+
| @max |
+------+
| 27 |
+------+
1 row in set (0.00 sec)
+------+
| @min |
+------+
| 16 |
+------+
1 row in set (0.00 sec)
--In summary, stored procedures in MySQL look like this:
DROP PROCEDURE IF EXISTS procName //
CREATE PROCEDURE procName(parameter list)
BEGIN
/* variable declarations */
/* CURSOR definitions */
/* declaring handlers */
/* procedure body...whatever you want it to do */
END;
//
--In more detail:
DROP PROCEDURE IF EXISTS procName //
CREATE PROCEDURE procName(IN/OUT/INOUT parName parType, ...)
BEGIN
/* variable declarations */
DECLARE varName,... varType;
/* e.g. DECLARE myName VARCHAR(20); DECLARE x,y,z INT; */
DECLARE varName varType DEFAULT value;
/* e.g. DECLARE x INT DEFAULT 0; */
/* CURSOR definitions */
DECLARE curName CURSOR FOR
SELECT ...
/* e.g. DECLARE cur1 CURSOR FOR
SELECT FName, LName
FROM Student; */
/* declaring handlers */
DECLARE EXIT/CONTINUE HANDLER FOR errorType/errorNumber
... action ...
/* e.g. DECLARE EXIT HANDLER FOR NOT FOUND
SELECT 'Sorry; this ID was not found' AS 'Error Message'; */
/* e.g. DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = 1; */
/* procedure body...whatever you want it to do */
/* IF statement */
IF cond1 THEN
action1
ELSEIF cond2 THEN
action2
ELSEIF cond3 THEN
action3
ELSE
elseaction
END IF;
/* e.g. IF x > 0 THEN
SET result = '+';
ELSEIF x < 0 THEN
SET result = '-';
ELSE
SET result = 'Zero';
END IF; */
/* CASE statement */
CASE varName
WHEN val1 THEN action1
WHEN val2 THEN action2
ELSE elseaction
END CASE;
/* e.g. CASE position
WHEN 1 THEN SET result = 'Gold Medal';
WHEN 2 THEN SET result = 'Silver Medal';
WHEN 3 THEN SET result = 'Bronze Medal';
ELSE SET result = 'No Medal!';
END CASE; */
/* WHILE loop */
WHILE cond DO
action1
action2
...
END WHILE
/* e.g. WHILE i < 5 DO
SET result = result + i;
SET i = i + 1;
END WHILE; */
/* REPEAT/UNTIL loop */
REPEAT
action1
action2
...
UNTIL cond
END REPEAT;
/* e.g. REPEAT
SET result = result + i;
SET i = i + 1;
UNTIL i >= 5
END REPEAT; */
/* using a CURSOR */
OPEN curName;
.
.
FETCH curName INTO var1, var2, ...;
.
.
CLOSE curName;
/* e.g. Assume cur1 has id's, first names, and last names
let's find the name of the student whose StuID is x:
OPEN cur1;
SET found = 0;
WHILE found = 0 DO
FETCH cur1 INTO nextID, nextFName, nextLName;
IF nextID = x THEN
SET result = CONCAT(nextFName, ' ', nextLName);
SET found = 1;
END IF;
END WHILE;
CLOSE cur1; */
END;
//
On gists
SQL mix (all my SQL, huge library)
•
MySql tricks
MySql - advanced
mix.sql #
/*
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
On gists
MySql - advanced
Non Equi Join
non-equi.sql #
-- Zaměstnanci
+------+-------+--------------+
| ID | Jméno | Úroveň |
+------+-------+--------------+
| 1 | John | Expert |
| 2 | Mary | Intermediate |
| 3 | Peter | Beginner |
+------+-------+--------------+
-- Zakázky
+------+-------------------+
| ID | Požadovaná_úroveň |
+------+-------------------+
| 1 | Intermediate |
| 2 | Beginner |
| 3 | Expert |
+------+-------------------+
SELECT Zaměstnanci.ID, Zaměstnanci.Jméno, Zaměstnanci.Úroveň, Zakázky.Požadovaná_úroveň
FROM Zaměstnanci
JOIN Zakázky ON Zaměstnanci.Úroveň > Zakázky.Požadovaná_úroveň;
-- Výsledek,
+------+-------+--------------+-------------------+
| ID | Jméno | Úroveň | Požadovaná_úroveň |
+------+-------+--------------+-------------------+
| 1 | John | Expert | Beginner |
| 2 | Mary | Intermediate | Beginner |
| 2 | Mary | Intermediate | Expert |
+------+-------+--------------+-------------------+
On gists
MySql
MySql tricks
MySql - advanced
Advanced CTE - Tree
tree.sql #
https://akki.ca/blog/mysql-adjacency-list-model-for-hierarchical-data-using-cte/
CREATE TABLE category (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
parent_id int(10) UNSIGNED DEFAULT NULL,
is_active int(1) UNSIGNED DEFAULT 1,
PRIMARY KEY (id),
FOREIGN KEY (parent_id) REFERENCES category (id)
ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO category (name, parent_id) VALUES ("Electronics", null);
INSERT INTO category (name, parent_id) VALUES ("TV", 1);
INSERT INTO category (name, parent_id) VALUES ("Smart", 2);
INSERT INTO category (name, parent_id) VALUES ("4K Ultra HD", 2);
INSERT INTO category (name, parent_id) VALUES ("Curved", 2);
INSERT INTO category (name, parent_id) VALUES ("Camera", 1);
INSERT INTO category (name, parent_id) VALUES ("Computer", 1);
INSERT INTO category (name, parent_id) VALUES ("Desktop", 7);
INSERT INTO category (name, parent_id) VALUES ("Laptops", 7);
INSERT INTO category (name, parent_id) VALUES ("Work", 9);
INSERT INTO category (name, parent_id) VALUES ("Travel", 9);
INSERT INTO category (name, parent_id) VALUES ("All Around", 9);
INSERT INTO category (name, parent_id) VALUES ("Gaming", 9);
INSERT INTO category (name, parent_id) VALUES ("Tablet", 7);
/* Tree */
WITH RECURSIVE shoppingCategories AS
(
SELECT id, name, parent_id, 1 AS depth, name AS path
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, sc.depth + 1, CONCAT(sc.path, ' > ', c.name)
FROM shoppingCategories AS sc
JOIN category AS c ON sc.id = c.parent_id
)
SELECT * FROM shoppingCategories;
+------+-------------+-----------+-------+-----------------------------------------------+
| id | name | parent_id | depth | path |
+------+-------------+-----------+-------+-----------------------------------------------+
| 1 | Electronics | NULL | 1 | Electronics |
| 2 | TV | 1 | 2 | Electronics > TV |
| 6 | Camera | 1 | 2 | Electronics > Camera |
| 7 | Computer | 1 | 2 | Electronics > Computer |
| 3 | Smart | 2 | 3 | Electronics > TV > Smart |
| 4 | 4K Ultra HD | 2 | 3 | Electronics > TV > 4K Ultra HD |
| 5 | Curved | 2 | 3 | Electronics > TV > Curved |
| 8 | Desktop | 7 | 3 | Electronics > Computer > Desktop |
| 9 | Laptops | 7 | 3 | Electronics > Computer > Laptops |
| 14 | Tablet | 7 | 3 | Electronics > Computer > Tablet |
| 10 | Work | 9 | 4 | Electronics > Computer > Laptops > Work |
| 11 | Travel | 9 | 4 | Electronics > Computer > Laptops > Travel |
| 12 | All Around | 9 | 4 | Electronics > Computer > Laptops > All Around |
| 13 | Gaming | 9 | 4 | Electronics > Computer > Laptops > Gaming |
+------+-------------+-----------+-------+-----------------------------------------------+
/* ancestors, tree */
SET @catID = 9;
WITH RECURSIVE
ancestorCategories AS
(
SELECT id, name, parent_id, 1 AS depth
FROM category
WHERE id = @catID
UNION ALL
SELECT c.id, c.name, c.parent_id, ac.depth - 1
FROM ancestorCategories AS ac
JOIN category AS c ON ac.parent_id = c.id
),
ancestorDeptOneCategories AS
(
SELECT id, name, parent_id, 1 AS depth
FROM category
WHERE parent_id IS null
UNION ALL
SELECT c.id, c.name, c.parent_id, adoc.depth + 1
FROM ancestorDeptOneCategories AS adoc
JOIN ancestorCategories ac ON ac.id = adoc.id
JOIN category AS c ON adoc.id = c.parent_id
)
SELECT * FROM ancestorDeptOneCategories
ORDER BY depth;
+------+-------------+-----------+-------+
| id | name | parent_id | depth |
+------+-------------+-----------+-------+
| 1 | Electronics | NULL | 1 |
| 2 | TV | 1 | 2 |
| 6 | Camera | 1 | 2 |
| 7 | Computer | 1 | 2 |
| 8 | Desktop | 7 | 3 |
| 9 | Laptops | 7 | 3 |
| 14 | Tablet | 7 | 3 |
| 10 | Work | 9 | 4 |
| 11 | Travel | 9 | 4 |
| 12 | All Around | 9 | 4 |
| 13 | Gaming | 9 | 4 |
+------+-------------+-----------+-------+
On gists
With recursive - CTE
•
MySql
MySql tricks
MySql - advanced
cte.sql #
WITH RecursiveSequence AS (
SELECT 1 AS number
UNION ALL
SELECT number + 1
FROM RecursiveSequence
WHERE number < 10
)
SELECT * FROM RecursiveSequence;
+--------+
| number |
+--------+
| 1 |
| 2 |
| 3 |
| ... |
| 10 |
+--------+
/*
numbers, days interval
*/
WITH RECURSIVE cte(n, dt) AS (
SELECT 10, '2023-01-01'
UNION ALL
SELECT n + 10, dt + INTERVAL 1 day
FROM cte WHERE n < 1000
)
/*
nebo takto kdyz nechci uvadet za cte(parametry):
WITH RECURSIVE cte AS (
SELECT 10 AS n, '2023-01-01' AS dt
UNION ALL
SELECT n + 10, dt + INTERVAL 1 day
FROM cte WHERE n < 1000
)
*/
SELECT * FROM cte;
/* tree, path, level */
# create the table
CREATE TABLE orgchart(
id INT PRIMARY KEY,
name VARCHAR(20),
role VARCHAR(20),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES orgchart(id));
# insert the rows
INSERT INTO orgchart VALUES(1,'Matthew','CEO',NULL),
(2,'Caroline','CFO',1),(3,'Tom','CTO',1),
(4,'Sam','Treasurer',2),(5,'Ann','Controller',2),
(6,'Anthony','Dev Director',3),(7,'Lousie','Sys Admin',3),
(8,'Travis','Senior DBA',3),(9,'John','Developer',6),
(10,'Jennifer','Developer',6),(11,'Maria','Junior DBA',8);
# let's see the table, The CEO has no manager, so the manager_id is set to NULL
SELECT * FROM orgchat;
+----+----------+--------------+------------+
| id | name | role | manager_id |
+----+----------+--------------+------------+
| 1 | Matthew | CEO | NULL |
| 2 | Caroline | CFO | 1 |
| 3 | Tom | CTO | 1 |
| 4 | Sam | Treasurer | 2 |
| 5 | Ann | Controller | 2 |
| 6 | Anthony | Dev Director | 3 |
| 7 | Lousie | Sys Admin | 3 |
| 8 | Travis | Senior DBA | 3 |
| 9 | John | Developer | 6 |
| 10 | Jennifer | Developer | 6 |
| 11 | Maria | Junior DBA | 8 |
+----+----------+--------------+------------+
WITH RECURSIVE reporting_chain(id, name, PATH, PATH2, level) AS
(
SELECT
id, name,
CAST(name AS CHAR(100)),
CAST(name AS CHAR(100)),
1
FROM orgchart
WHERE manager_id IS NULL
UNION ALL
SELECT
oc.id,
oc.name,
CONCAT(rc.path,' -> ',oc.name),
CONCAT(oc.name,' -> ', rc.path2),
rc.level+1
FROM reporting_chain rc
JOIN orgchart oc ON rc.id=oc.manager_id
)
SELECT * FROM reporting_chain ORDER BY level;
+------+----------+---------------------------------------+-------+
| id | name | path | level |
+------+----------+---------------------------------------+-------+
| 1 | Matthew | Matthew | 1 |
| 2 | Caroline | Matthew -> Caroline | 2 |
| 3 | Tom | Matthew -> Tom | 2 |
| 4 | Sam | Matthew -> Caroline -> Sam | 3 |
| 5 | Ann | Matthew -> Caroline -> Ann | 3 |
| 6 | Anthony | Matthew -> Tom -> Anthony | 3 |
| 7 | Lousie | Matthew -> Tom -> Lousie | 3 |
| 8 | Travis | Matthew -> Tom -> Travis | 3 |
| 9 | John | Matthew -> Tom -> Anthony -> John | 4 |
| 10 | Jennifer | Matthew -> Tom -> Anthony -> Jennifer | 4 |
| 11 | Maria | Matthew -> Tom -> Travis -> Maria | 4 |
+------+----------+---------------------------------------+-------+
/* node paths */
WITH RECURSIVE
cte ( node, path )
AS
( SELECT node, cast ( 1 as char(30) )
FROM bst WHERE parent IS NULL
UNION ALL
SELECT bst.node, CONCAT ( cte.path, '-->', bst.node )
FROM cte JOIN bst ON cte.node = bst.parent
)
SELECT * FROM cte ORDER BY node;
/* next tree */
CREATE TABLE
categories (
id int,
cat_name varchar(100),
parent_category_id int DEFAULT NULL
);
INSERT INTO
categories
VALUES
(1, 'Mens', NULL),
(2, 'Tops', 1),
(3, 'Jerseys', 2),
(4, 'England', 3);
WITH RECURSIVE
category_tree AS (
SELECT
id,
cat_name,
parent_category_id,
cat_name AS full_name
FROM
categories
WHERE
parent_category_id IS NULL
UNION ALL
SELECT
c.id,
c.cat_name,
c.parent_category_id,
CONCAT (ct.full_name, ' > ', c.cat_name)
FROM
categories c
JOIN category_tree ct ON c.parent_category_id = ct.id
)
SELECT
full_name
FROM
category_tree;
On gists
MySql
MySql tricks
MySql - advanced
MySQL can't specify target table for update in FROM clause
super-workaround.sql #
/*
does not work!
*/
update Users set Valid = 0
where Id in (
select Id from Users where Id < 100
)
/*
workaround
+ one subselect more like a wrapper ;-)
*/
update Users set Valid = 0
where Id in (
select Id from (
select Id from Users where Id < 100
) as t
)
On gists
MySql
MySql tricks
MySql - advanced
Query with variable in table name
example.sql #
SET @newDomainTable := CONCAT('domains_', newDomain);
SET @SQL = CONCAT(
'CREATE TABLE ',@newDomainTable,' (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`domain` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
PREPARE sqlcmd from @SQL;
EXECUTE sqlcmd;
DEALLOCATE PREPARE sqlcmd;
END
On gists
Pivot table via prepared statements
•
MySql
MySql tricks
MySql - advanced
pivot.sql #
create table tbl_values (
id int unsigned not null primary key,
`Name` varchar(10),
`Group` varchar(10),
`Value` int
);
insert into tbl_values values
(1, 'Pete', 'A', 10),
(2, 'Pete', 'B', 20),
(3, 'John', 'A', 10);
-- 1. Create an expression that builds the columns
set @sql = (
select group_concat(distinct
concat(
"sum(case when `Group`='", `Group`, "' then `Value` end) as `", `Group`, "`"
)
)
from tbl_values
);
-- 2. Complete the SQL instruction
set @sql = concat("select Name, ", @sql, " from tbl_values group by `Name`");
-- 3. Create a prepared statement
prepare stmt from @sql;
-- 4. Execute the prepared statement
execute stmt;