Задачі з SQL

лютий 2015

Сontent

Оператор CASE

Завдання

Необхідно вивести значення моделі PC, її вартість з таблиці Product_PC та один з коментрарів ("Найбільша ціна", "Найменша ціна", "Середня ціна","Модель відсутня") коментарій;

Таблиця Product_PC

modelprice
1121600
1122700
1123650
2220NULL
2240650
2260NULL

Рішення завдання

Досить цікава задачка для початківців.

А для більш досвідчених, питання було б: "- Чому в одинакових запитах WHEN price IS NULL різні відповіді SQL-запиту?"


завдання MySQL оператор CASE

Рис.1. Варіанти вибору CASE



Завдання можна вирішити застосовуючи оператор вибору CASE(), розмірковуючи слідуючим чином, якщо ціна ' X ', то вибираємо коментарій ' Coment Y '.

Схема вибору CASE

Рис.2. Схема вибору CASE





Що ж каже теорія з цього приводу: Синтаксис CASE(). Документація mysql.com

Оператор CASE() може бути застосований в одній із форм:

Перша форма:
CASE case_value
   WHEN when_value THEN statement_list
   [WHEN when_value THEN statement_list] ...
   [ELSE statement_list]
END CASE


Друга форма:(True)

CASE
    WHEN search_condition THEN statement_list
   [WHEN search_condition THEN statement_list] ...
   [ELSE statement_list]
END CASE


SQL-вирази оператора WHEN повинні мати одинакову синтаксичну форму, помилковим буде змішувати першу та другу синтаксичну форму оператора CASE().

Згідно правил оператор CASE() працює слідуючим чином, розглянемо варіанти:
1.1. При застосуванні першої форми CASE() умова WHEN виконує statement_list , як лише значення case_value буде задовільняти умові when_value синтаксичної схеми поданої вище.

2.1. При застосуванні другої синтаксичної форми CASE() умова WHEN виконує statement_list, за умови що приймає значення TRUE.
2.2. Якщо ні одна з умов не задовільняє критеріям вибору, тоді буде виконано ELSE statement_list.
2.3. Якщо відсутня частина з ELSE, буде повернуто NULL-значення.
2.4. Якщо задовільняється декілька критеріїв, тоді буде повернуте значення виразу THEN statement_list першого з них, інші перевірятись не будуть!

1.1. Аналіз роботи першого синтаксичного варіанту оператора CASE()

1.1. При застосуванні першої форми CASE() умова WHEN виконує statement_list , як лише значення case_value буде задовільняти умові when_value синтаксичної схеми поданої вище.

Запишемо для оператор вибору CASE() слідуючий SQL-запит:

SELECT model, price, 
   CASE price 
   WHEN (SELECT MAX(price)  
         FROM Product_PC) 
   THEN 'Найбільша ціна' 
   WHEN (SELECT MIN(price)  
         FROM Product_PC) 
   THEN 'Найменша ціна' 
   ELSE 'Все інше' 
   END comment 
FROM Product_PC


Об'єктно-орієнтовний стиль кодування php-частини даного SQL-запиту:

<?php   
$con = new mysqli("host","user","pass","db");

$result = $con->query("
SELECT model, price,
 CASE price 
WHEN (SELECT MAX(price) FROM Product_PC) THEN 'Найбільша ціна' WHEN (SELECT MIN(price) FROM Product_PC) THEN 'Найменша ціна' ELSE 'Все інше' END comment"); echo 'model : price : coment <br> '; while ($row = $result->fetch_assoc()){ echo $row['model'].' '.$row['price'].' '.$row['comment']; }; $con->close(); ?>

Результат виконання php-частини:

model : price : coment
1121 600 Найменша ціна
1122 700 Найбільша ціна
1123 650 Все інше
2220 Все інше
2240 650 Все інше
2260 Все інше

Варто відмітити! Модель PC 2220 та 2260 має NULL значення, тому ціна не вказана.

Цікаво, якщо застосувати такий SQL-запит, щоб виключити поля з NULL-значеннями, то результат виконання буде ELSE-statement.

Причина звісно в NULL, невідоме; поскільки оператор CASE() не може порівняти значення ціни price з невідомим і як результат ігнорує вибір WHEN.

Кодування з рядочком на виключення NULL-значень:

SELECT model,price, 
CASE price 
WHEN (SELECT MAX(price) 
      FROM Product_PC) 
THEN 'Найбільша ціна' 
WHEN (SELECT MIN(price) 
     FROM Product_PC) 
THEN 'Найменша ціна'
WHEN price IS NULL THEN 'Модель відсутня'
ELSE 'Середня ціна' 
END comment
FROM Product_PC

Результат виконання php-частини буде слідуючий:

model : price : coment
1121 600 Найменша ціна
1122 700 Найбільша ціна
1123 650 Середня ціна
2220 Середня ціна
2240 650 Середня ціна
2260 Середня ціна


Корекція NULL-значеннь таблиці за допомогою php-частини:

echo (!empty($row['price']) ? 
$row['model'].' '.$row['price'].' '.$row['comment']:
$row['model'].' '.'Модель відсутня').'<br>';


<?php
$con = new mysqli("host","user","pass","db");
$result = $con->query("
SELECT model, price, 
 CASE price 
 WHEN (SELECT MAX(price)  
       FROM Product_PC) 
 THEN 'Найбільша ціна' 
 WHEN (SELECT MIN(price)  
       FROM Product_PC) THEN 'Найменша ціна' 
 ELSE 'Середня ціна' 
 END comment");
echo 'model : price : coment <br>'; 
while ($row = $result->fetch_assoc()){ 

echo (!empty($row['price']) ? 
  $row['model'].' '.$row['price'].' '.$row['comment']:
  $row['model'].' '.'Модель відсутня'). '<br>';

};

$con->close();
?>

Результат виконання такої php-частини буде слідуючий:

model : price : coment
1121 600 Найменша ціна
1122 700 Найбільша ціна
1123 650 Середня ціна
2220 Модель відсутня
2240 650 Середня ціна
2260 Модель відсутня


2.1. Аналіз роботи другого синтаксичного варіанту оператора CASE()

Друга форма:(True)

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

2.1. При застосуванні другої синтаксичної форми CASE() умова WHEN виконує statement_list, за умови що приймає значення TRUE.
2.2. Якщо ні одна з умов не задовільняє критеріям вибору, тоді буде виконано ELSE statement_list.
2.3. Якщо відсутня частина з ELSE, буде повернуто NULL-значення.
2.4. Якщо задовільняється декілька критеріїв, тоді буде повернуте значення виразу THEN statement_list першого з них, інші перевірятись не будуть!

Запишемо для оператор вибору CASE() слідуючий SQL-запит використовуючи другу форму синтаксису:

SELECT model, 
 CASE  
     WHEN price IS NULL  
     THEN 'Модель відсутня' 
     ELSE price   
     END price  
FROM Product_PC;

Результат виконання php-частини:

model : price
1121 600
1122 700
1123 650
2220 Модель відсутня
2240 650
2260 Модель відсутня

Чи наприклад запишемо ось такий SQL-запит:

SELECT model,  
 CASE  
 WHEN price=600 THEN 'Ціна 600' 
 WHEN price=700 THEN 'Ціна 700' 
 ELSE price   
 END price  
FROM Product_PC;

Результат виконання php-частини:

model : price
1121 Ціна 600
1122 Ціна 700
1123 650
2220
2240 650
2260

Що ж якщо в таблиці два одинакових значення ціни? В нашому випадку 650:

SELECT model,  
 CASE  
 WHEN price=650 THEN 'Ціна 650' 
 WHEN price=700 THEN 'Ціна 700' 
 ELSE price   
 END price  
FROM Product_PC;

Результат виконання php-частини:

model : price
1121 600
1122 Ціна 700
1123 Ціна 650
2220
2240 Ціна 650
2260

Ну і нарешті ще одне рішення даного завдання:

SELECT model, price,
   CASE 
   WHEN price=(SELECT MAX(price) 
               FROM Product_PC ) 
   THEN 'Ціна максимальна' 
   WHEN price=(SELECT MIN(price) 
              FROM Product_PC ) 
   THEN 'Ціна мінімальна' 
   WHEN price IS NULL 
   THEN 'модель відсутня'
   ELSE price 
  END coment 
FROM Product_PC;

Результат виконання php-частини:

model : price : coment
1121 600 Ціна мінімальна
1122 700 Ціна максимальна
1123 650 650
2220 модель відсутня
2240 650 650
2260 модель відсутня



На цьому це завдання вважаю завершене, сподіваюсь вона комусь стане в нагоді. Анатолій

лютий 2015 року