💻 Примеры

💻 Примеры

Классификация остатков: CASE и IF

-- CASE
SELECT quantity,
       CASE
           WHEN quantity < 20 THEN 'Almost finish'
           ELSE 'Enough'
       END AS category
FROM inventory_transactions;

-- IF (MySQL)
SELECT quantity,
       IF(quantity < 20, 'Almost finish', 'Enough') AS category
FROM inventory_transactions;

Категория размера заказа по диапазону

SELECT *,
       CASE
           WHEN quantity < 30 THEN 'small'
           WHEN quantity BETWEEN 30 AND 70 THEN 'medium'
           ELSE 'large'
       END AS category
FROM purchase_order_details
WHERE purchase_order_id BETWEEN 90 AND 100;

Вложенный IF для трёх категорий

SELECT *,
       IF(quantity < 30, 'small',
          IF(quantity > 70, 'large', 'medium')) AS category
FROM purchase_order_details
WHERE purchase_order_id BETWEEN 90 AND 100;

Уникальные значения с фильтром и сортировкой

SELECT DISTINCT purchase_order_id
FROM purchase_order_details
WHERE unit_cost > 10
ORDER BY purchase_order_id DESC;

Пагинация с фильтром

SELECT *
FROM customers
WHERE job_title = 'Owner'
ORDER BY state_province ASC
LIMIT 5 OFFSET 1;

Инициалы сотрудника

SELECT CONCAT(LEFT(first_name, 1), '.', LEFT(last_name, 1), '.') AS initials
FROM employees;

Замена NULL строкой

-- IF
SELECT IF(payment_type IS NULL, 'No data', payment_type) AS payment_type_filled
FROM orders;

-- CASE
SELECT CASE
           WHEN payment_type IS NULL THEN 'No data'
           ELSE payment_type
       END AS payment_type_filled
FROM orders;

Строки в верхнем регистре

SELECT UPPER(first_name) AS first_name_upper,
       UPPER(last_name) AS last_name_upper
FROM customers;

Извлечение части телефона

SELECT business_phone,
       SUBSTRING(business_phone, 6, 3) AS phone_part
FROM employees;