Gists - MySql - advanced

On gists

Prepared statements and another replace tricks

10.11.2025

MySql MySql tricks MySql - advanced

example.sql #

/*
 1/ standard way
*/
INSERT IGNORE INTO  product_transport_type (product_id, transport_type_id)
SELECT productId, transportId 
FROM (
    SELECT 
        p.id AS productId,
        t.transportId
    FROM product p
    CROSS JOIN (
        SELECT 6 AS transportId UNION 
        SELECT 7 UNION 
        SELECT 8 UNION 
        SELECT 9 UNION 
        SELECT 10 UNION 
        SELECT 13 UNION 
        SELECT 14 UNION 
        SELECT 15 UNION 
        SELECT 16 UNION 
        SELECT 17 UNION 
        SELECT 18 UNION 
        SELECT 20 UNION 
        SELECT 21 UNION 
        SELECT 23 UNION 
        SELECT 25 UNION 
        SELECT 26 UNION 
        SELECT 27 UNION 
        SELECT 28 UNION 
        SELECT 29
    ) t
    WHERE p.`code` IN (
        7230991, 9705946, 9710628, 9734100, 9734201, 9734500, 9740966, 9740967, 9740980, 
        9744605, 9744606, 9744631, 9744633, 9760507, 9760556, 9762549, 9762550, 9762551, 
        9762569, 9762570, 9762578, 9762639, 9762640, 9762641, 9762653, 9762689, 9762704, 
        9762707, 9763609, 9763617, 9763618, 9779064, 9779065, 9787016, 9787017
    )
) AS ProductsTransportsCombination;




/* 2 find in set - nepodporuje index - asi pomale / pomalejsi */
SET @transport_ids = '6,7,8,9,10,13,14,15,16,17,18,20,21,23,25,26,27,28,29';
SET @product_codes = '7230991,9705946,9710628,9734100,9734201,9734500,9740966,9740967,9740980,9744605,9744606,9744631,9744633,9760507,9760556,9762549,9762550,9762551,9762569,9762570,9762578,9762639,9762640,9762641,9762653,9762689,9762704,9762707,9763609,9763617,9763618,9779064,9779065,9787016,9787017';

INSERT IGNORE INTO product_transport_type (product_id, transport_type_id) 
SELECT p.id AS productId, t.transportId  
FROM product p 
CROSS JOIN ( 
    SELECT 6 AS transportId UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION 
    SELECT 10 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION 
    SELECT 17 UNION SELECT 18 UNION SELECT 20 UNION SELECT 21 UNION SELECT 23 UNION 
    SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 
) t 
WHERE FIND_IN_SET(p.code, @product_codes)
  AND FIND_IN_SET(t.transportId, @transport_ids);
  
  
  
/* 3 prepared stm = rychlejsi kvuli pouzivani indexu */
SET @product_codes = '7230991,9705946,9710628,9734100,9734201,9734500,9740966,9740967,9740980,9744605,9744606,9744631,9744633,9760507,9760556,9762549,9762550,9762551,9762569,9762570,9762578,9762639,9762640,9762641,9762653,9762689,9762704,9762707,9763609,9763617,9763618,9779064,9779065,9787016,9787017';
SET @transport_ids = '6,7,8,9,10,13,14,15,16,17,18,20,21,23,25,26,27,28,29';

SET @sql = CONCAT('
INSERT IGNORE INTO product_transport_type (product_id, transport_type_id) 
SELECT p.id AS productId, t.transportId  
FROM product p 
CROSS JOIN ( 
    SELECT 6 AS transportId UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION 
    SELECT 10 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION 
    SELECT 17 UNION SELECT 18 UNION SELECT 20 UNION SELECT 21 UNION SELECT 23 UNION 
    SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 
) t 
WHERE p.code IN (', @product_codes, ')
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


/* 3b, state stm, ale sestaveni UNIONu dynamicky */
SET @product_codes = '7230991,9705946,9710628,9734100,9734201,9734500,9740966,9740967,9740980,9744605,9744606,9744631,9744633,9760507,9760556,9762549,9762550,9762551,9762569,9762570,9762578,9762639,9762640,9762641,9762653,9762689,9762704,9762707,9763609,9763617,9763618,9779064,9779065,9787016,9787017';
SET @transport_ids = '6,7,8,9,10,13,14,15,16,17,18,20,21,23,25,26,27,28,29';

-- Vytvoření UNION části pro transport IDs
SET @transport_union = REPLACE(REPLACE(@transport_ids, ',', ' UNION SELECT '), ',', '');
SET @transport_union = CONCAT('SELECT ', @transport_union);

SET @sql = CONCAT('
INSERT IGNORE INTO product_transport_type (product_id, transport_type_id) 
SELECT p.id AS productId, t.transportId  
FROM product p 
CROSS JOIN (', @transport_union, ') t 
WHERE p.code IN (', @product_codes, ')
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

On gists

Generated columns (Virtual/Stored)

17.1.2025 • 7.2.2025 (aktualizováno)

MySql MySql tricks MySql - advanced

examples.sql #

-- Základní matematické operace
amount DECIMAL(10,2) GENERATED ALWAYS AS (quantity * price) STORED

-- Práce s textem
full_name VARCHAR(100) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL

-- Použití CASE
status_description VARCHAR(50) GENERATED ALWAYS AS (
    CASE 
        WHEN status = 1 THEN 'Active'
        WHEN status = 0 THEN 'Inactive'
        ELSE 'Unknown'
    END
) VIRTUAL

-- Práce s datumy
age INT GENERATED ALWAYS AS (TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE)) VIRTUAL

-- Komplexnější výpočty
total_with_tax DECIMAL(10,2) GENERATED ALWAYS AS (
    CASE
        WHEN tax_rate IS NULL THEN amount
        ELSE amount * (1 + tax_rate/100)
    END
) STORED

-- Formátování a manipulace s textem
formatted_phone VARCHAR(20) GENERATED ALWAYS AS (
    CONCAT('+420 ', SUBSTR(phone, 1, 3), ' ', SUBSTR(phone, 4, 3), ' ', SUBSTR(phone, 7))
) VIRTUAL

On gists

Column check constraint

17.1.2025 • 7.2.2025 (aktualizováno)

MySql MySql tricks MySql - advanced

examples.sql #

-- CONSTRAINT [constraint_name] CHECK (expression) [ENFORCED | NOT ENFORCED]

/*
ENFORCED - MySQL aktivně kontroluje podmínku při INSERT/UPDATE
NOT ENFORCED - MySQL podmínku nekontroluje, slouží jen jako dokumentace/metadata
*/

-- Kontrola formátu emailu pomocí REGEXP
CONSTRAINT valid_email CHECK (email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$')

-- Kontrola, že koncová data je později než počáteční
CONSTRAINT valid_date_range CHECK (end_date > start_date)

-- Kontrola že sloupec obsahuje pouze velká písmena
CONSTRAINT uppercase_only CHECK (column_name = UPPER(column_name))

-- Kontrola že věk je v rozumném rozsahu
CONSTRAINT valid_age CHECK (age >= 0 AND age < 150)

-- Kombinace více podmínek pro plat
CONSTRAINT salary_rules CHECK (
    (role = 'Junior' AND salary BETWEEN 30000 AND 50000) OR
    (role = 'Senior' AND salary BETWEEN 60000 AND 120000)
)

-- Kontrola že skóre je buď NULL nebo v rozsahu 0-100
CONSTRAINT valid_score CHECK (score IS NULL OR (score >= 0 AND score <= 100))

-- Kontrola že status je jedna z povolených hodnot
CONSTRAINT valid_status CHECK (
    status IN ('pending', 'approved', 'rejected', 'on_hold')
)

-- Komplexní validace telefonního čísla
CONSTRAINT valid_phone CHECK (
    phone_number REGEXP '^\\+?[1-9][0-9]{7,14}$'
)

-- Kontrola že discount je menší než cena
CONSTRAINT valid_discount CHECK (discount < price)

-- Kontrola že součet procentuálních hodnot nepřesáhne 100
CONSTRAINT percentage_sum CHECK (
    value1 + value2 + value3 <= 100
)


-- Komplexní kontrola hesla (minimální délka, musí obsahovat číslo a velké písmeno)
CONSTRAINT password_strength CHECK (
    LENGTH(password) >= 8 
    AND password REGEXP '[0-9]'
    AND password REGEXP '[A-Z]'
    AND password != username
)

-- Kontrola věku na základě data narození s aktuálním datem
CONSTRAINT valid_birth_date CHECK (
    YEAR(birth_date) >= 1900
    AND birth_date <= CURRENT_DATE
    AND TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE) <= 120
)

-- Složitá business logika s CASE
CONSTRAINT complex_pricing CHECK (
    CASE 
        WHEN customer_type = 'VIP' THEN price >= 1000 AND discount <= price * 0.3
        WHEN customer_type = 'Regular' THEN price >= 500 AND discount <= price * 0.1
        WHEN customer_type = 'New' THEN price >= 100 AND discount = 0
        ELSE price >= 0
    END
)

-- Kombinace podmínek pro rezervační systém
CONSTRAINT valid_reservation CHECK (
    (status = 'confirmed' AND payment_id IS NOT NULL AND amount > 0)
    OR (status = 'pending' AND (payment_id IS NULL OR amount = 0))
    OR (status = 'cancelled' AND cancellation_reason IS NOT NULL)
)

-- Kontrola formátu a validity dat
CONSTRAINT valid_document CHECK (
    (doc_type = 'invoice' AND doc_number REGEXP '^INV-[0-9]{6}$')
    OR (doc_type = 'order' AND doc_number REGEXP '^ORD-[0-9]{6}$')
    AND issue_date <= due_date
    AND YEAR(issue_date) >= 2020
)

-- Kontrola závislostí mezi více sloupci
CONSTRAINT project_status_rules CHECK (
    (status = 'completed' AND end_date IS NOT NULL AND progress = 100)
    OR (status = 'in_progress' AND end_date IS NULL AND progress BETWEEN 1 AND 99)
    OR (status = 'planned' AND end_date IS NULL AND progress = 0)
)

-- Sezónní ceník s různými pravidly
CONSTRAINT seasonal_pricing CHECK (
    CASE 
        WHEN MONTH(booking_date) IN (6,7,8) THEN price >= 1000  -- Letní sezóna
        WHEN MONTH(booking_date) IN (12,1,2) THEN price >= 800  -- Zimní sezóna
        WHEN DAYOFWEEK(booking_date) IN (1,7) THEN price >= 700 -- Víkendy
        ELSE price >= 500  -- Mimo sezónu
    END
)

-- Validace adresy
CONSTRAINT valid_address CHECK (
    LENGTH(street) >= 3
    AND LENGTH(city) >= 2
    AND postal_code REGEXP '^[0-9]{5}$'
    AND (
        country = 'CZ' AND phone REGEXP '^\\+420[0-9]{9}$'
        OR 
        country = 'SK' AND phone REGEXP '^\\+421[0-9]{9}$'
    )
)

-- Komplexní kontrola produktu
CONSTRAINT product_rules CHECK (
    CASE product_type
        WHEN 'physical' THEN 
            weight > 0 
            AND dimensions IS NOT NULL 
            AND digital_url IS NULL
        WHEN 'digital' THEN 
            weight = 0 
            AND dimensions IS NULL 
            AND digital_url IS NOT NULL
        WHEN 'service' THEN 
            weight = 0 
            AND dimensions IS NULL 
            AND duration > 0
    END
    AND (
        price > 0
        OR (price = 0 AND is_free_sample = TRUE)
    )
)

On gists

Insert only when @var is true

29.11.2024 • 7.2.2025 (aktualizováno)

MySql MySql tricks MySql - advanced

insert.sql #

SET @cnt = (SELECT COUNT(1) FROM image_size WHERE folder = '840x1190');

INSERT INTO `image_size` 
    (`id`, `folder`, `quality`, `xsize`, `ysize`, 
    `image_resize_id`, `diagonal`, `watermark__image_id`, 
    `watermark_xsize`, `watermark_ysize`, 
    `watermark_xpos`, `watermark_ypos`, 
    `crop_left`, `crop_top`, `trim`, `grayscale`, 
    `filter_color`, `filter_opacity`)
    
SELECT NULL, '840x1190', 95, 840, 1190, 
       0, NULL, NULL, 
       NULL, NULL, 
       NULL, NULL, 
       NULL, NULL, 0, 0, 
       NULL, NULL
WHERE @cnt = 0;

On gists

MySQL - Remove duplicate entries

27.5.2024

MySql MySql - advanced

remove.sql #

DELETE FROM your_table
WHERE rowid NOT IN (
    SELECT MAX(rowid)
    FROM your_table
    GROUP BY column1, column2, ...
);

On gists

Advanced usage of CASE WHEN THEN

12.5.2024 • 2.6.2024 (aktualizováno)

MySql MySql - advanced

when-then.sql #

-- https://towardsdev.com/sql-case-statement-with-code-examples-04f77dab8d5a

-- 1. Simple case
SELECT
    order_id,
    CASE customer_id
        WHEN 1 THEN 'Premium'
        WHEN 2 THEN 'Gold'
        WHEN 3 THEN 'Silver'
        ELSE 'Regular'
    END AS customer_type
FROM orders;


-- 2. Searched CASE Statement
SELECT
    order_id,
    CASE
        WHEN order_amount > 1000 THEN 'High'
        WHEN order_amount > 500 THEN 'Medium'
        ELSE 'Low'
    END AS order_priority
FROM orders;

-- 3. Using CASE in WHERE Clause
SELECT *
FROM customers
WHERE CASE
    WHEN country = 'USA' THEN sales_region = 'North America'
    WHEN country = 'UK' THEN sales_region = 'Europe'
    ELSE FALSE
END;

-- 4. Using CASE with Aggregate Functions
SELECT
    department,
    COUNT(CASE WHEN salary > 50000 THEN 1 END) AS high_salary_count,
    COUNT(CASE WHEN salary <= 50000 THEN 1 END) AS low_salary_count
FROM employees
GROUP BY department;


-- 5. Nesting CASE Statements
SELECT
    order_id,
    CASE
        WHEN payment_status = 'paid' THEN
            CASE
                WHEN shipping_status = 'shipped' THEN 'Delivered'
                ELSE 'Processing'
            END
        ELSE 'Pending'
    END AS order_status
FROM orders;

-- 6. Using CASE in JOIN Conditions
SELECT
    o.order_id,
    o.order_date,
    c.customer_name
FROM orders o
JOIN customers c
ON CASE
    WHEN o.customer_id = 1 THEN c.customer_id = o.customer_id
    WHEN o.customer_id = 2 THEN c.country = 'USA'
    ELSE c.country = 'UK'
END;


-- INSERT
INSERT INTO employees (employee_id, name, salary)
VALUES
 (101, 'John Doe', 
 CASE
 WHEN department = 'Engineering' THEN 80000
 WHEN department = 'Marketing' THEN 70000
 ELSE 60000
 END);

On gists

LEFT JOIN -> JOIN ON, WHERE

16.3.2024

MySql MySql - advanced

join.sql #

/*
https://www.codeproject.com/Articles/231132/Difference-between-And-clause-along-with-on-and-Wh
*/


+--------+---------------+
| DeptId | DeptName      |
+--------+---------------+
|      1 | HR            |
|      2 | Payroll       |
|      3 | Admin         |
|      4 | Marketing     |
|      5 | HR & Accounts |
+--------+---------------+

+-------+----------+--------+-----------+
| EmpId | EmpName  | DeptId | EmpSalary |
+-------+----------+--------+-----------+
|     1 | John     |      1 |   5000.00 |
|     2 | Albert   |      1 |   4500.00 |
|     3 | Crain    |      2 |   6000.00 |
|     4 | Micheal  |      2 |   5000.00 |
|     5 | David    |   NULL |     34.00 |
|     6 | Kelly    |   NULL |    457.00 |
|     7 | Rudy     |      1 |    879.00 |
|     8 | Smith    |      2 |   7878.00 |
|     9 | Karsen   |      5 |    878.00 |
|    10 | Stringer |      5 |    345.00 |
|    11 | Cheryl   |   NULL |      0.00 |
+-------+----------+--------+-----------+


-- 1
select * from employees e
left join departments d on e.deptid = d.deptid;
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName  | DeptId | EmpSalary | DeptId | DeptName      |
+-------+----------+--------+-----------+--------+---------------+
|     1 | John     |      1 |   5000.00 |      1 | HR            |
|     2 | Albert   |      1 |   4500.00 |      1 | HR            |
|     3 | Crain    |      2 |   6000.00 |      2 | Payroll       |
|     4 | Micheal  |      2 |   5000.00 |      2 | Payroll       |
|     5 | David    |   NULL |     34.00 |   NULL | NULL          |
|     6 | Kelly    |   NULL |    457.00 |   NULL | NULL          |
|     7 | Rudy     |      1 |    879.00 |      1 | HR            |
|     8 | Smith    |      2 |   7878.00 |      2 | Payroll       |
|     9 | Karsen   |      5 |    878.00 |      5 | HR & Accounts |
|    10 | Stringer |      5 |    345.00 |      5 | HR & Accounts |
|    11 | Cheryl   |   NULL |      0.00 |   NULL | NULL          |
+-------+----------+--------+-----------+--------+---------------+


-- 2
select * from employees e
left join departments d on e.deptid = d.deptid
and ( d.deptname = 'HR' or d.deptname  = 'HR & Accounts');
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName  | DeptId | EmpSalary | DeptId | DeptName      |
+-------+----------+--------+-----------+--------+---------------+
|     1 | John     |      1 |   5000.00 |      1 | HR            |
|     2 | Albert   |      1 |   4500.00 |      1 | HR            |
|     3 | Crain    |      2 |   6000.00 |   NULL | NULL          |
|     4 | Micheal  |      2 |   5000.00 |   NULL | NULL          |
|     5 | David    |   NULL |     34.00 |   NULL | NULL          |
|     6 | Kelly    |   NULL |    457.00 |   NULL | NULL          |
|     7 | Rudy     |      1 |    879.00 |      1 | HR            |
|     8 | Smith    |      2 |   7878.00 |   NULL | NULL          |
|     9 | Karsen   |      5 |    878.00 |      5 | HR & Accounts |
|    10 | Stringer |      5 |    345.00 |      5 | HR & Accounts |
|    11 | Cheryl   |   NULL |      0.00 |   NULL | NULL          |
+-------+----------+--------+-----------+--------+---------------+


-- 3
select * from employees e
left join departments d on e.deptid = d.deptid
where ( d.deptname = 'HR' or d.deptname  = 'HR & Accounts');
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName  | DeptId | EmpSalary | DeptId | DeptName      |
+-------+----------+--------+-----------+--------+---------------+
|     1 | John     |      1 |   5000.00 |      1 | HR            |
|     2 | Albert   |      1 |   4500.00 |      1 | HR            |
|     7 | Rudy     |      1 |    879.00 |      1 | HR            |
|     9 | Karsen   |      5 |    878.00 |      5 | HR & Accounts |
|    10 | Stringer |      5 |    345.00 |      5 | HR & Accounts |
+-------+----------+--------+-----------+--------+---------------+


-- to same ale z druheho pohledu, slo by resit pres RIGHT JOIN

-- 1
select * from departments d
left join employees e on e.deptId = d.deptId;
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName      | EmpId | EmpName  | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
|      1 | HR            |     1 | John     |      1 |   5000.00 |
|      1 | HR            |     2 | Albert   |      1 |   4500.00 |
|      1 | HR            |     7 | Rudy     |      1 |    879.00 |
|      2 | Payroll       |     3 | Crain    |      2 |   6000.00 |
|      2 | Payroll       |     4 | Micheal  |      2 |   5000.00 |
|      2 | Payroll       |     8 | Smith    |      2 |   7878.00 |
|      3 | Admin         |  NULL | NULL     |   NULL |      NULL |
|      4 | Marketing     |  NULL | NULL     |   NULL |      NULL |
|      5 | HR & Accounts |     9 | Karsen   |      5 |    878.00 |
|      5 | HR & Accounts |    10 | Stringer |      5 |    345.00 |
+--------+---------------+-------+----------+--------+-----------+


-- 2
select * from departments d
left join employees e on e.deptId = d.deptId and
( d.deptname = 'HR' or d.deptname  = 'HR & Accounts');
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName      | EmpId | EmpName  | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
|      1 | HR            |     1 | John     |      1 |   5000.00 |
|      1 | HR            |     2 | Albert   |      1 |   4500.00 |
|      1 | HR            |     7 | Rudy     |      1 |    879.00 |
|      2 | Payroll       |  NULL | NULL     |   NULL |      NULL |
|      3 | Admin         |  NULL | NULL     |   NULL |      NULL |
|      4 | Marketing     |  NULL | NULL     |   NULL |      NULL |
|      5 | HR & Accounts |     9 | Karsen   |      5 |    878.00 |
|      5 | HR & Accounts |    10 | Stringer |      5 |    345.00 |
+--------+---------------+-------+----------+--------+-----------+

-- 3
SELECT * from departments d
left join employees e on e.deptId = d.deptId
where ( d.deptname = 'HR' or d.deptname  = 'HR & Accounts');
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName      | EmpId | EmpName  | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
|      1 | HR            |     1 | John     |      1 |   5000.00 |
|      1 | HR            |     2 | Albert   |      1 |   4500.00 |
|      1 | HR            |     7 | Rudy     |      1 |    879.00 |
|      5 | HR & Accounts |     9 | Karsen   |      5 |    878.00 |
|      5 | HR & Accounts |    10 | Stringer |      5 |    345.00 |
+--------+---------------+-------+----------+--------+-----------+

On gists

Pivot table

16.3.2024 • 17.3.2024 (aktualizováno)

MySql - advanced

pivot.sql #

-- static
SELECT
  product_id,
  SUM(CASE WHEN month = 'January' THEN sales_amount ELSE 0 END) AS January,
  SUM(CASE WHEN month = 'February' THEN sales_amount ELSE 0 END) AS February,
  SUM(CASE WHEN month = 'March' THEN sales_amount ELSE 0 END) AS March
FROM sales
GROUP BY product_id;

-- dynamic
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN month = ''',
      month,
      ''' THEN sales_amount ELSE 0 END) AS ',
      CONCAT('`', month, '`')
    )
  ) INTO @sql
FROM sales;

SET @sql = CONCAT('SELECT product_id, ', @sql, ' FROM sales GROUP BY product_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;



-- another table
SELECT
  `Sales Date`,
  MAX(CASE WHEN `Product Name` = 'Product AthanEN `Sales Amount` END) AS `Product A`,
  MAX(CASE WHEN `Product Name` = 'Product B' then `Sales Amount` END) AS `Product B`,
  MAX(CASE WHEN `Product Name` = 'Product C' than `Sales Amount` END) AS `Product C`
FROM
  `sales_data`
GROUP BY
  `Sales Date`;
  
  
  SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN `Product Name` = ''', `Product Name`, ''' THEN `Sales Amount` END) AS `', `Product Name`, '`')
  ) INTO @sql
FROM
  `sales_data`;

SET @sql = CONCAT('SELECT `Sales Date`, ', @sql, ' FROM `sales_data` GROUP BY `Sales Date`');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;



/* month-year invoices */
select rok,
sum(case when mesic=1 then castka else 0 end) as 'leden',
sum(case when mesic=2 then castka else 0 end) as 'únor',
sum(case when mesic=3 then castka else 0 end) as 'březen',
sum(case when mesic=4 then castka else 0 end) as 'duben',
sum(case when mesic=5 then castka else 0 end) as 'květen',
sum(case when mesic=6 then castka else 0 end) as 'červen',
sum(case when mesic=7 then castka else 0 end) as 'červenec',
sum(case when mesic=8 then castka else 0 end) as 'srpen',
sum(case when mesic=9 then castka else 0 end) as 'září',
sum(case when mesic=10 then castka else 0 end) as 'říjen',
sum(case when mesic=11 then castka else 0 end) as 'listopad',
sum(case when mesic=12 then castka else 0 end) as 'prosinec'
from
(
select year(datum) as rok, month(datum) as mesic, sum(castka) as castka
from faktury group by year(datum), month(datum)
) as soucty
group by rok

On gists

window fn - standings rank with previous rank

15.3.2024

MySql - advanced

rank.sql #

CREATE TABLE `standing` (
	`team` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`pts` INT(11) NULL DEFAULT NULL,
	`season` INT(11) NULL DEFAULT NULL
)
COLLATE='utf8mb4_general_ci'
ENGINE=MyISAM
;
  
  
  SELECT
    team,
    season,
    pts,
    RANK() OVER (PARTITION BY season ORDER BY pts DESC) AS rnk,
    LAG(rank_prev_season) OVER (PARTITION BY team ORDER BY season) AS prev_rnk
FROM
    (
        SELECT
            team,
            season,
            pts,
            RANK() OVER (PARTITION BY season ORDER BY pts DESC) AS rank_prev_season
        FROM
            standing
    ) AS subquery
ORDER BY
    season, rnk;



+--------+--------+------+-----+----------+
| team   | season | pts  | rnk | prev_rnk |
+--------+--------+------+-----+----------+
| Plzen  |   2021 |   60 |   1 |     NULL |
| Sparta |   2021 |   55 |   2 |     NULL |
| Slavia |   2021 |   40 |   3 |     NULL |
| Sparta |   2022 |   54 |   1 |        2 |
| Slavia |   2022 |   50 |   2 |        3 |
| Plzen  |   2022 |   48 |   3 |        1 |
| Slavia |   2023 |   55 |   1 |        2 |
| Plzen  |   2023 |   49 |   2 |        3 |
| Sparta |   2023 |   38 |   3 |        1 |
+--------+--------+------+-----+----------+

On gists

sql table from select

11.3.2024

MySql MySql tricks MySql - advanced

howto.sql #

-- 1

  SELECT 'Mon' 
    UNION 
    SELECT 'Tue'
    UNION 
    SELECT 'Web'
    UNION 
    SELECT 'Thu'
    UNION 
    SELECT 'Fri'
    UNION 
    SELECT 'Sat'
    UNION 
    SELECT 'Sun';



-- 2
WITH RECURSIVE days_cte AS (
  SELECT 'Mon' AS day_name
  UNION ALL
  SELECT
    CASE day_name
      WHEN 'Mon' THEN 'Tue'
      WHEN 'Tue' THEN 'Wed'
      WHEN 'Wed' THEN 'Thu'
      WHEN 'Thu' THEN 'Fri'
      WHEN 'Fri' THEN 'Sat'
      WHEN 'Sat' THEN 'Sun'
      ELSE 'Mon' -- Pro zajištění cyklického opakování
    END
  FROM days_cte
  WHERE day_name != 'Sun'
)
SELECT day_name FROM days_cte;