Обчислення в sql. Порахувати кількість записів в групах (створених GROUP BY) Sql підрахунок кількості записів за умовою

є запит виду:

SELECT i. *, COUNT (*) AS currencies, SUM (ig.quantity) AS total, SUM (g.price * ig.quantity) AS price, c.briefly AS cname FROM invoice AS i, invoice_goods AS ig, good g LEFT JOIN currency c ON (c.id \u003d g.currency) WHERE ig.invoice_id \u003d i.id AND g.id \u003d ig.good_id GROUP BY g.currency ORDER BY i.date DESC;

тобто вибирається список замовлень, в якому вважаються сумарні вартості товарів в різних валютах (валюта встановлена \u200b\u200bу товару, стовпець cname в результаті - назва валюти)

потрібно в стовпці результату currencies отримувати кількість записів з однаковим i.id, однак, експерименти з параметрами COUNT () ні до чого не привели - завжди повертає 1

Питання: чи можливо отримати справжнє значення в стовпці currencies? Тобто якщо замовлені товари з цінами в 3х різних валютах, currencies \u003d 3?

Занадто великі вольності допускає MySQL по відношенню до SQL однако. Що наприклад значить i. * В контексті цього селекта? Всі колонки таблиці invoice? Так як до них не застосовується жодна групова функція, то непогано було б щоб вони були перераховані в GROUP BY, інакше не зовсім ясний принцип угруповання рядків. Якщо необхідно отримати всі товари по всіх замовленнях в розрізі валют, це одне, якщо необхідно отримати всі товари згруповані по валютах в розрізі кожного замовлення, це зовсім інше.
Виходячи з вашого селекта можна припустити наступну структуру даних:
Таблиця invoice:

Таблиця invoice_goods:

Таблиця goods:

Таблиця currency:

Що поверне ваш поточний селект? За ідеєю він поверне для кожного замовлення N-рядків по кожній валюті в якій в цьому замовленні є товари. Але через те що в group by не вказано нічого крім g.currency, це не очевидно :), більш того, колонка c.briefly теж вносить свою лепту в неявне освіту груп. Що ж ми маємо в підсумку, для кожної унікального поєднання i. *, G.currency і c.briefly буде сформована група до рядків якої будуть застосовані функції SUM і COUNT. Те що в результаті гри з параметром COUNT у вас виходила завжди 1 означає що в результуючої групі була тільки одна запис (тобто групи формуються не так як вам, можливо, потрібно, можете описати вимоги детальніше?). З вашого запитання не зрозуміло, що ви хотіли б дізнатися - скільки різних валют учавствовало в замовленні або скільки замовлень було в даній валюті? У першому випадку можливі кілька варіантів все залежить від можливостей mySQL, у другому треба по-іншому написати селект вираз.

Занадто великі вольності допускає MySQL по відношенню до SQL однако. Що наприклад значить i. * В контексті цього селекта? Всі колонки таблиці invoice?

Так саме. Але великої ролі це не грає, тому що корисних в даному випадку серед них (колонок) немає. Нехай i. * Буде i.id. Для визначеності.

Що поверне ваш поточний селект? За ідеєю він поверне для кожного замовлення N-рядків по кожній валюті в якій в цьому замовленні є товари. Але через те що в group by не вказано нічого крім g.currency, це не очевидно :),

Саме так.
Поверне він наступне (в цьому прикладі з i я вибираю тільки id, а не всі стовпці):

id	currencies	total	price	cname
33	1	1.00	198.00	BF
33	1	4.00	1548.04	РУБ

більш того, колонка c.briefly теж вносить свою лепту в неявне освіту груп.

Яким чином? За c.id \u003d g.currency робиться з'єднання таблиць, а угруповання по g.currency.

Те що в результаті гри з параметром COUNT у вас виходила завжди 1 означає що в результуючої групі була тільки одна запис

Ні, група будувалася з 1й записи. Наскільки я це зрозумів, COUNT () повертає 1 саме тому (адже стовпці, які в групі відрізняються (правда, крім стовпця валют), створюються аггрегатнимі функціями).

(Тобто групи формуються не так як вам, можливо, потрібно, можете описати вимоги детальніше?).

Групи формуються так як треба, кожна група - це сумарна вартість товарів у кожній валюті. Однак, мені, крім того, потрібно порахувати, скільки ж елементів в цієї групі.

З вашого запитання не зрозуміло, що ви хотіли б дізнатися - скільки різних валют учавствовало в замовленні або скільки замовлень було в даній валюті?

Да уж, запрацювався небагато. Якраз перше.

dmig [досьє]
Під "неявним" участю в утворенням групи я маю на увазі, що якщо колонка не вказана в GROUP BY, і при цьому НЕ є аргументом в груповому режимі, то результат селекта буде ідентичний тому, як якщо б ця колонок БУЛА вказана в GROUP BY. Ваш селект, і селект наведений нижче виведуть абсолютно однаковий результат (не звертайте уваги на join "и, я просто навів їх до єдиного формату запису):

Select i.id id, count (*) currencies, sum (ig.quantity) total, SUM (g.price * ig.quantity) price, c.briefly cname FROM invoice i join invoice_goods ig on (ig.invoice_id \u003d i. id) join good g on (g.id \u003d ig.good_id) LEFT OUTER JOIN currency c ON (c.id \u003d g.currency) group by i.id, c.briefly

Виходить що в кожному рядку результуючої вибірки є одна, і тільки одна валюта (якби це було інакше, то і рядків було б дві). Про кількість яких елементів в такому випадку мова? Про пунктах замовлення? Тоді ваш селект абсолютно правильний, просто по даній валюті, в даному замовленні тільки один пункт.
Давайте розглянемо схему даних:

В одному замовленні безліч пунктів (рядків), так?
Кожен пункт це товар в довіднику goods, так?
Кожен товар має певну (і тільки одну) валюту, це випливає з c.id \u003d g.currency, так?

Скільки валют в замовленні? Стільки скільки в ньому пунктів з РІЗНИМИ валютами.
Складати g.price * ig.quantity має сенс тільки для пунктів в одній валюті;) (хоча кілометри з годинником, теж складати можна :) Так що ж вас не влаштовує !? Ви стверджуєте, що вам треба скільки різних валют учавствовало в замовленні
і в такому випадку зробити це в рамках цього ж селекта без усіляких хитрощів (які швидше за все не потягне MySQL) не вийде; (
Я нажаль не знавець MySQL. У oracle зробити це одним селект можна, але чи допоможе вам таку пораду? Навряд чи;)

# В одному замовленні безліч пунктів (рядків), так?
# Кожен пункт це товар в довіднику goods, так?
# Кожен товар має певну (і тільки одну) валюту, це випливає з c.id \u003d g.currency, так?

Так.
Один замовлення: одна запис в таблиці invoice, їй відповідають n (\u003e 0) записів в invoice_goods, кожній з яких відповідає 1 запис в таблиці goods, запис "валюта" в кожній з яких, в свою чергу, відповідає 1й записи в таблиці currency ( LEFT JOIN - на випадок редагування довідника валют кривими руками - таблиці типу MyISAM не підтримують зовнішніх ключів).

Скільки валют в замовленні? Стільки скільки в ньому пунктів з РІЗНИМИ валютами.

Так, саме так.

Складати g.price * ig.quantity має сенс тільки для пунктів в одній валюті;) (хоча кілометри з годинником, теж складати можна :)

Саме тому робиться угруповання по id валюти (g.currency).

У oracle зробити це одним селект можна, але чи допоможе вам таку пораду?

М.б.
Я трохи спілкувався з Oracle, з pl / sql знаком.

Варіант №1.

Select a. *, Count (*) over (partition by a.id) currencies from (select i.id id, sum (ig.quantity) total, SUM (g.price * ig.quantity) price, c.briefly cname FROM invoice i join invoice_goods ig on (ig.invoice_id \u003d i.id) join good g on (g.id \u003d ig.good_id) LEFT OUTER JOIN currency c ON (c.id \u003d g.currency) group by i.id, c.briefly) a

Це використовує т.зв. analytic function. З імовірністю 99% НЕ працює в MySQL.

Варіант №2.
Створюється функція, countCurrencies наприклад, яка по id замовлення повертає кількість валют яке в ньому брало участь і тоді:

Select i.id id, countCurrencies (i.id) currencies, sum (ig.quantity) total, SUM (g.price * ig.quantity) price, c.briefly cname FROM invoice i join invoice_goods ig on (ig.invoice_id \u003d i.id) join good g on (g.id \u003d ig.good_id) LEFT OUTER JOIN currency c ON (c.id \u003d g.currency) group by i.id, c.briefly, countCurrencies (i.id)

Може прокатати ... але буде викликатися для кожної валюти кожного замовлення. Не знаю чи дає MySQL робити GROUP BY по функції ...

варіант №3

Select i.id id, agr.cnt currencies, sum (ig.quantity) total, SUM (g.price * ig.quantity) price, c.briefly cname FROM invoice i join invoice_goods ig on (ig.invoice_id \u003d i.id ) join good g on (g.id \u003d ig.good_id) LEFT OUTER JOIN currency c ON (c.id \u003d g.currency) left outer join (select ii.id, count (distinct gg.currency) cnt from invoice ii, invoce_goods iig, good gg where ii.id \u003d iig.invoice_id and gg.id \u003d iig.good_id group by ii.id) agr on (i.id \u003d agr.id) group by i.id, c.briefly, agr. cnt

Напевно найправильніший ... і цілком можливо самий робочий варіант з усіх.

Найшвидший це Варіант №1. №2 найменш ефективний, тому що чим більше валют в замовленні, тим частіше вони вважаються.
№3 теж в принципі не кращий за швидкістю, але принаймні можна покластися на кешування всередині СУБД.

результатом всіх трьох СЕЛЕКТА буде наступне:

id		currencies		total		price		cname
33		2		1.00		198.00		BF
33		2		4.00		1548.04		РУБ

для одного і того ж id цифра в колонці currencies буде завжди однакова, це те що вам треба?

Описується використання арифметичних операторів і побудова обчислюваних стовпців. Розглядаються підсумкові (агрегатні) функції COUNT, SUM, AVG, MAX, MIN. Дається приклад використання оператора GROUP BY для групування в запитах вибірки даних. Описується застосування пропозиції HAVING.

Побудова обчислюваних полів

У загальному випадку для створення обчислюється (похідного) поля в списку SELECT слід вказати деякий вираз мови SQL. У цих виразах застосовуються арифметичні операції додавання, віднімання, множення і ділення, а також вбудовані функції мови SQL. Можна вказати ім'я будь-якого стовпця (поля) таблиці або запиту, але використовувати ім'я стовпця тільки тієї таблиці або запиту, які вказані в списку пропозиції FROM відповідної інструкції. При побудові складних виразів можуть знадобитися дужки.

Стандарти SQL дозволяють явно задавати імена стовпців результуючої таблиці, для чого застосовується фраза AS.

SELECT Товар.Названіе, Товар.Цена, Сделка.Колічество, Товар.Цена * Сделка.Колічество AS Вартість FROM Товар INNER JOIN Угода ON Товар.КодТовара \u003d Сделка.КодТовара Приклад 6.1. Розрахунок загальної вартості для кожної угоди.

Приклад 6.2. Отримати список фірм із зазначенням прізвища та ініціалів клієнтів.

SELECT Фірма, Прізвище + "" + Left (Ім'я, 1) + "." + Left (батькові, 1) + "." AS ПІБ FROM Клієнт Приклад 6.2. Отримання списку фірм із зазначенням прізвища та ініціалів клієнтів.

У запиті використана вбудована функція Left, що дозволяє вирізати в текстовій змінної один символ зліва в даному випадку.

Приклад 6.3. Отримати список товарів із зазначенням року і місяця продажу.

SELECT Товар.Названіе, Year (Сделка.Дата) AS Рік, Month (Сделка.Дата) AS Місяць FROM Товар INNER JOIN Угода ON Товар.КодТовара \u003d Сделка.КодТовара Приклад 6.3. Отримання списку товарів із зазначенням року і місяця продажу.

У запиті використані вбудовані функції Year і Month для виділення року і місяця з дати.

Використання підсумкових функцій

За допомогою підсумкових (агрегатних) функцій в рамках SQL-запиту можна отримати ряд узагальнюючих статистичних відомостей про безліч відібраних значень вихідного набору.

Користувачеві доступні наступні основні підсумкові функції:

Count (Вираз) - визначає кількість записів в вихідному наборі SQL-запиту;
Min / Max (Вираз) - визначають найменше та найбільше з безлічі значень в деякому полі запиту;
Avg (Вираз) - ця функція дозволяє розрахувати середнє значення безлічі значень, що зберігаються в певному полі відібраних запитом записів. Воно є арифметичним середнім значенням, тобто сумою значень, поділеній на їх кількість.
Sum (Вираз) - обчислює суму безлічі значень, що містяться в певному полі відібраних запитом записів.

Найчастіше в якості вираження виступають імена стовпців. Вираз може обчислюватися і за значеннями кількох таблиць.

Всі ці функції оперують зі значеннями в єдиному стовпці таблиці або з арифметичним виразом і повертають єдине значення. Функції COUNT, MIN і MAX застосовні як до числовим, так і до нечислове полях, тоді як функції SUM і AVG можуть використовуватися тільки в разі числових полів, за винятком COUNT (*). При обчисленні результатів будь-яких функцій спочатку виключаються всі порожні значення, після чого необхідна операція застосовується тільки до решти конкретним значенням стовпця. Варіант COUNT (*) - особливий випадок використання функції COUNT, його призначення полягає в підрахунку всіх рядків в результуючій таблиці, незалежно від того, містяться там порожні, що дублюються або будь-які інші значення.

Якщо до застосування узагальнюючої функції необхідно виключити дублюються значення, слід перед ім'ям стовпця у визначенні функції помістити ключове слово DISTINCT. Воно не має сенсу для функцій MIN і MAX, проте його використання може вплинути на результати виконання функцій SUM і AVG, тому необхідно заздалегідь обміркувати, чи має воно бути присутнім в кожному конкретному випадку. Крім того, ключове слово DISTINCT може бути зазначено в будь-який запит не більше одного разу.

Дуже важливо відзначити, що підсумкові функції можуть використовуватися тільки в списку пропозиції SELECT і в складі пропозиції HAVING. У всіх інших випадках це неприпустимо. Якщо список в реченні SELECT містить підсумкові функції, А в тексті запиту відсутня фраза GROUP BY, що забезпечує об'єднання даних в групи, то жоден з елементів списку пропозиції SELECT не може включати будь-яких посилань на поля, за винятком ситуації, коли поля виступають в якості аргументів підсумкових функцій.

Приклад 6.4. Визначити першу за алфавітом назву товару.

SELECT Min (Товар.Названіе) AS Min_Названіе FROM Товар Приклад 6.4. Визначення першого за алфавітом назви товару.

Приклад 6.5. Визначити кількість угод.

SELECT Count (*) AS Колічество_сделок FROM Угода Приклад 6.5. Визначити кількість угод.

Приклад 6.6. Визначити сумарну кількість проданого товару.

SELECT Sum (Сделка.Колічество) AS Колічество_товара FROM Угода Приклад 6.6. Визначення сумарної кількості проданого товару.

Приклад 6.7. Визначити середню ціну проданого товару.

SELECT Avg (Товар.Цена) AS Avg_Цена FROM Товар INNER JOIN Угода ON Товар.КодТовара \u003d Сделка.КодТовара; Приклад 6.7. Визначення середньої ціни проданого товару.

SELECT Sum (Товар.Цена * Сделка.Колічество) AS Вартість FROM Товар INNER JOIN Угода ON Товар.КодТовара \u003d Сделка.КодТовара Приклад 6.8. Підрахунок загальної вартості проданих товарів.

Пропозиція GROUP BY

Часто в запитах потрібно формувати проміжні підсумки, що зазвичай відображається появою в запиті фрази "для кожного ...". Для цієї мети в операторі SELECT використовується пропозицію GROUP BY. Запит, в якому присутня GROUP BY, називається групують запитом, оскільки в ньому групуються дані, отримані в результаті виконання операції SELECT, після чого для кожної окремої групи створюється єдина сумарна рядок. Стандарт SQL вимагає, щоб пропозиція SELECT і фраза GROUP BY були тісно пов'язані між собою. При наявності в операторі SELECT фрази GROUP BY кожен елемент списку в реченні SELECT повинен мати єдине значення для всієї групи. Більш того, пропозиція SELECT може включати тільки такі типи елементів: імена полів, підсумкові функції, Константи і вирази, що включають комбінації перерахованих вище елементів.

Всі імена полів, наведені в списку пропозиції SELECT, повинні бути присутніми і у фразі GROUP BY - за винятком випадків, коли ім'я стовпця використовується в підсумкової функції. Зворотне правило не є справедливим - у фразі GROUP BY можуть бути імена стовпців, відсутні в списку пропозиції SELECT.

Якщо спільно з GROUP BY використовується пропозицію WHERE, то воно обробляється першим, а гуртування піддаються тільки ті рядки, які задовольняють умові пошуку.

Стандартом SQL визначено, що при проведенні групування все відсутні значення розглядаються як рівні. Якщо два рядки таблиці в одному і тому ж группіруемих стовпці містять значення NULL і ідентичні значення у всіх інших непустих группіруемих шпальтах, вони поміщаються в одну і ту ж групу.

Приклад 6.9. Обчислити середній обсяг покупок, зроблених кожним покупцем.

SELECT Кліент.Фамілія, Avg (Сделка.Колічество) AS Среднее_колічество FROM Клієнт INNER JOIN Угода ON Кліент.КодКліента \u003d Сделка.КодКліента GROUP BY Кліент.Фамілія Приклад 6.9. Обчислення середнього обсягу покупок, зроблених кожним покупцем.

Фраза "кожним покупцем" знайшла своє відображення в SQL-запиті у вигляді пропозиції GROUP BY Кліент.Фамілія.

Приклад 6.10. Визначити, на яку суму був проданий товар кожного найменування.

SELECT Товар.Названіе, Sum (Товар.Цена * Сделка.Колічество) AS Вартість FROM Товар INNER JOIN Угода ON Товар.КодТовара \u003d Сделка.КодТовара GROUP BY Товар.Названіе Приклад 6.10. Визначення, на яку суму був проданий товар кожного найменування.

SELECT Кліент.Фірма, Count (Сделка.КодСделкі) AS Колічество_сделок FROM Клієнт INNER JOIN Угода ON Кліент.КодКліента \u003d Сделка.КодКліента GROUP BY Кліент.Фірма Приклад 6.11. Підрахунок кількості угод, здійснених кожною фірмою.

SELECT Кліент.Фірма, Sum (Сделка.Колічество) AS Общее_Колічество, Sum (Товар.Цена * Сделка.Колічество) AS Вартість FROM Товар INNER JOIN (Клієнт INNER JOIN Угода ON Кліент.КодКліента \u003d Сделка.КодКліента) ON Товар.КодТовара \u003d Угода .КодТовара GROUP BY Кліент.Фірма Приклад 6.12. Підрахунок загальної кількості купленого для кожної фірми товару і його вартості.

Приклад 6.13. Визначити сумарну вартість кожного товару за кожний місяць.

SELECT Товар.Названіе, Month (Сделка.Дата) AS Місяць, Sum (Товар.Цена * Сделка.Колічество) AS Вартість FROM Товар INNER JOIN Угода ON Товар.КодТовара \u003d Сделка.КодТовара GROUP BY Товар.Названіе, Month (Сделка.Дата ) Приклад 6.13. Визначення сумарної вартості кожного товару за кожний місяць.

Приклад 6.14. Визначити сумарну вартість кожного товару першого сорту за кожен місяць.

SELECT Товар.Названіе, Month (Сделка.Дата) AS Місяць, Sum (Товар.Цена * Сделка.Колічество) AS Вартість FROM Товар INNER JOIN Угода ON Товар.КодТовара \u003d Сделка.КодТовара WHERE Товар.Сорт \u003d "Перший" GROUP BY Товар .Названіе, Month (Сделка.Дата) Приклад 6.14. Визначення сумарної вартості кожного товару першого сорту за кожен місяць.

пропозиція HAVING

За допомогою HAVING відображаються всі попередньо згруповані за допомогою GROUP BY блоки даних, що задовольняють заданим в HAVING умов. Це додаткова можливість "профільтрувати" вихідний набір.

Умови в HAVING відрізняються від умов в WHERE:

HAVING виключає з результуючого набору даних групи з результатами агрегованих значень;
WHERE виключає з розрахунку агрегатних значень по угрупованню записи, що не відповідають умові;
в умови пошуку WHERE не можна ставити агрегатні функції.

Приклад 6.15. Визначити фірми, у яких загальна кількість угод перевищила три.

SELECT Кліент.Фірма, Count (Сделка.Колічество) AS Колічество_сделок FROM Клієнт INNER JOIN Угода ON Кліент.КодКліента \u003d Сделка.КодКліента GROUP BY Кліент.Фірма HAVING Count (Сделка.Колічество)\u003e 3 Приклад 6.15. Визначення фірм, у яких загальна кількість угод перевищила три.

Приклад 6.16.Вивести список товарів, проданих на суму понад 10000 руб.

SELECT Товар.Названіе, Sum (Товар.Цена * Сделка.Колічество) AS Вартість FROM Товар INNER JOIN Угода ON Товар.КодТовара \u003d Сделка.КодТовара GROUP BY Товар.Названіе HAVING Sum (Товар.Цена * Сделка.Колічество)\u003e 10000 Приклад 6.16. Вид списку товарів, проданих на суму понад 10000 руб.

Приклад 6.17. Вивести список товарів, проданих на суму понад 10000 без зазначення суми.

SELECT Товар.Названіе FROM Товар INNER JOIN Угода ON Товар.КодТовара \u003d Сделка.КодТовара GROUP BY Товар.Названіе HAVING Sum (Товар.Цена * Сделка.Колічество)\u003e 10000 Приклад 6.17. Вид списку товарів, проданих на суму понад 10000 без зазначення суми.

Щоб визначити кількість записів в таблиці MySQL, потрібно скористатися спеціальною функцією COUNT ().

Функція COUNT () повертає кількість записів в таблиці, які відповідають заданому критерію.

Функція COUNT (expr) завжди вважає тільки ті рядки, у яких результатом виразу expr є NOT NULL.

Винятком з цього правила є використання функції COUNT () із зірочкою в якості аргументу - COUNT (*). В цьому випадку вважаються всі рядки, незалежно від того, NULL вони або NOT NULL.

Наприклад, функція COUNT (*) повертає загальна кількість записів в таблиці:

SELECT COUNT (*) FROM table_name

Як порахувати кількість записів і вивести на екран

Приклад PHP + MySQL-коду для підрахунку і виведення загальної кількості рядків:

$ Res \u003d mysql_query ( "SELECT COUNT (*) FROM table_name") $ row \u003d mysql_fetch_row ($ res); $ Total \u003d $ row; // всього записів echo $ total; ?\u003e

Цей приклад ілюструє найпростіший варіант використання функції COUNT (). Але за допомогою цієї функції можна виплнять і інші завдання.

Вказавши певний стовпець таблиці у вигляді параметра, функція COUNT (column_name) повертає кількість записів цього шпальти, які не містять значення NULL. Записи зі значеннями NULL ігноруються.

SELECT COUNT (column_name) FROM table_name

Використовувати функцію mysql_num_rows () не можна, тому що для того, щоб дізнатися загальна кількість записів, потрібно виконати запит SELECT * FROM db, тобто отримати всі записи, а це небажано, тому краще використовувати функцію count.

$ Result \u003d mysql_query ( "SELECT COUNT (*) as rec FROM db");

Використання функції COUNT () на прикладі

Ось ще один приклад використання функції COUNT (). Припустимо, є таблиця ice_cream з каталогом морозива, в якій знаходяться ідентифікатори категорій і назви морозива.

Будемо вчитися підводити підсумки. Ні, це ще не підсумки вивчення SQL, а підсумки значень стовпців таблиць бази даних. Агрегатні функції SQL діють відносно значень стовпця з метою отримання єдиного результуючого значення. Найбільш часто застосовуються агрегатні функції SQL SUM, MIN, MAX, AVG і COUNT. Слід розрізняти два випадки застосування агрегатних функцій. Перший: агрегатні функції використовуються самі по собі і повертають одне результуюче значення. Другий: агрегатні функції використовуються з оператором SQL GROUP BY, тобто з угрупованням по полях (стовпцях) для отримання результуючих значень в кожній групі. Розглянемо спочатку випадки використання агрегатних функцій без угруповання.

Функція SQL SUM

Функція SQL SUM повертає суму значень стовпця таблиці бази даних. Вона може застосовуватися тільки до стовпців, значеннями яких є числа. Запити SQL для отримання результуючої суми починаються так:

SELECT SUM (ім'я_стовпця) ...

Після цього виразу випливає FROM (ім'я_таблиці), а далі за допомогою конструкції WHERE може бути задана умова. Крім того, перед ім'ям стовпця може бути зазначено DISTINCT, і це означає, що враховуватися будуть тільки унікальні значення. За замовчуванням ж враховуються всі значення (для цього можна особливо вказати не DISTINCT, а ALL, але слово ALL не є обов'язковим).

Приклад 1. Є база даних фірми з даними про її підрозділах і співробітників. Таблиця Staff крім усього має стовпець з даними про заробітну плату співробітників. Вибірка з таблиці має наступний вигляд (для збільшення картинки клацнути по ній лівою кнопкою миші):

Для отримання суми розмірів всіх заробітних плат використовуємо наступний запит:

SELECT SUM (Salary) FROM Staff

Цей запит поверне значення 287664,63.

А тепер . У вправах вже починаємо ускладнювати завдання, наближаючи їх до тих, що зустрічаються на практиці.

Функція SQL MIN

Функція SQL MIN також діє щодо стовпців, значеннями яких є числа і повертає мінімальне серед всіх значень стовпця. Ця функція має синтаксис аналогічний синтаксису функції SUM.

Приклад 3. База даних і таблиця - ті ж, що і в прикладі 1.

Потрібно дізнатися мінімальну заробітну плату співробітників відділу з номером 42. Для цього пишемо наступний запит:

Запит поверне значення 10505,90.

І знову вправа для самостійного рішення. В цьому і деяких інших вправах потрібно вже не тільки таблиця Staff, але і таблиця Org, що містить дані про підрозділи фірми:

Приклад 4. До таблиці Staff додається таблиця Org, що містить дані про підрозділи фірми. Вивести мінімальну кількість років, опрацьованих одним співробітником у відділі, розташованому в Бостоні.

Функція SQL MAX

Аналогічно працює і має аналогічний синтаксис функція SQL MAX, яка застосовується, коли потрібно визначити максимальне значення серед всіх значень стовпця.

Приклад 5.

Потрібно дізнатися максимальну заробітну плату співробітників відділу з номером 42. Для цього пишемо наступний запит:

Запит поверне значення 18352,80

Прийшов час вправи для самостійного рішення.

Приклад 6. Знову працюємо з двома таблицями - Staff і Org. Вивести назву відділу і максимальне значення комісійних, одержуваних одним співробітником у відділі, що належить до групи відділів (Division) Eastern. використовувати JOIN (з'єднання таблиць) .

Функція SQL AVG

Зазначене щодо синтаксису для попередніх описаних функцій вірно і щодо функції SQL AVG. Ця функція повертає середнє значення серед всіх значень стовпця.

Приклад 7. База даних і таблиця - ті ж, що і в попередніх прикладах.

Нехай потрібно дізнатися середній трудовий стаж співробітників відділу з номером 42. Для цього пишемо наступний запит:

Результатом буде значення 6,33

Приклад 8. Працюємо з однією таблицею - Staff. Вивести середню зарплату співробітників зі стажем від 4 до 6 років.

Функція SQL COUNT

Функція SQL COUNT повертає кількість записів таблиці бази даних. Якщо в запиті вказати SELECT COUNT (ім'я_стовпця) ..., то результатом буде кількість записів без урахування тих записів, в яких значенням стовпця є NULL (невизначений). Якщо використовувати в якості аргументу зірочку і почати запит SELECT COUNT (*) ..., то результатом буде кількість всіх записів (рядків) таблиці.

Приклад 9. База даних і таблиця - ті ж, що і в попередніх прикладах.

Потрібно дізнатися число всіх співробітників, які отримують комісійні. Число співробітників, у яких значення стовпця Comm - НЕ NULL, поверне наступний запит:

SELECT COUNT (Comm) FROM Staff

Результатом буде значення 11.

Приклад 10. База даних і таблиця - ті ж, що і в попередніх прикладах.

Якщо потрібно дізнатися загальна кількість записів в таблиці, то застосовуємо запит із зірочкою в якості аргументу функції COUNT:

SELECT COUNT (*) FROM Staff

Результатом буде значення 17.

В наступному вправі для самостійного рішення потрібно використовувати підзапит.

Приклад 11. Працюємо з однією таблицею - Staff. Вивести число співробітників у відділі планування (Plains).

Агрегатні функції разом з SQL GROUP BY (угрупованням)

Тепер розглянемо застосування агрегатних функцій разом з оператором SQL GROUP BY. Оператор SQL GROUP BY служить для угруповання результуючих значень по стовпцях таблиці бази даних. На сайті є урок, присвячений окремо цього оператора .

Приклад 12.Є база даних порталу оголошень. У ній є таблиця Ads, яка містить дані про оголошеннях, поданих за тиждень. Стовпець Category містить дані про великих категоріях оголошень (наприклад, Нерухомість), а стовпець Parts - про більш дрібних частинах, що входять в категорії (наприклад, частини Квартири та Дачі є частинами категорії Нерухомість). Стовпець Units містить дані про кількість поданих оголошень, а стовпець Money - про грошові суми, виручених за подачу оголошень.

Category	Part	Units	Money
транспорт	автомашини	110	17600
Нерухомість	квартири	89	18690
Нерухомість	дачі	57	11970
транспорт	Мотоцикли	131	20960
будматеріали	дошки	68	7140
Електротехніка	Телевізори	127	8255
Електротехніка	Холодильники	137	8905
будматеріали	Регіпс	112	11760
Дозвілля	книги	96	6240
Нерухомість	будинки	47	9870
Дозвілля	музика	117	7605
Дозвілля	ігри	41	2665

Використовуючи оператор SQL GROUP BY, знайти суми грошей, виручених за подачу оголошень в кожній категорії. Пишемо наступний запит:

SELECT Category, SUM (Money) AS Money FROM Ads GROUP BY Category

Приклад 13. База даних і таблиця - та ж, що в попередньому прикладі.

Використовуючи оператор SQL GROUP BY, з'ясувати, в якій частині кожної категорії було подано найбільшу кількість оголошень. Пишемо наступний запит:

SELECT Category, Part, MAX (Units) AS Maximum FROM Ads GROUP BY Category

Результатом буде наступна таблиця:

Підсумкові і індивідуальні значення в одній таблиці можна отримати об'єднанням результатів запитів за допомогою оператора UNION .

Реляційні бази даних і мова SQL

ОБЧИСЛЕННЯ

підсумкові функції

У виразах SQL-запитів нерідко потрібно виконати попередню обробку даних. З цією метою використовуються спеціальні функції і вирази.

Досить часто потрібно дізнатися, скільки записів відповідає тому або іншому запиту,яка сума значень деякого числового стовпця, його максимальне, мінімальне та середнє значення. Для цього служать так звані підсумкові (статистичні, агрегатні) функції. Підсумкові функції обробляють набори записів, задані, наприклад, виразом WHERE. Якщо їх включити в список стовпців, наступний за оператором SELECT, то результатная таблиця буде містити не тільки стовпці таблиці бази даних, але і значення, обчислені за допомогою цих функцій. далі наведеносписок підсумкових функцій.

COUNT (параметр ) Повертає кількість записів, зазначених у параметрі. Якщо потрібно отримати кількість всіх записів, то в якості параметра слід вказати символ зірочки (*). Якщо в якості параметра вказати ім'я стовпця, то функція поверне кількість записів, в яких цей стовпець має значення, відмінні від NULL. Щоб дізнатися, скільки різних значень містить стовпець, перед його ім'ям слід вказати ключове слово DISTINCT. наприклад:

SELECT COUNT (*) FROM Клієнти;

SELECT COUNT (Сумма_заказа) FROM Клієнти;

SELECT COUNT (DISTINCT Сумма_заказа) FROM Клієнти;

Спроба виконати наступний запит призведе до повідомлення про помилку:

SELECT Регіон, COUNT (*) FROM Клієнти;

SUM (параметр ) Повертає суму значень наданого в секції стовпчика. Параметр може являти собою і вираз, що містить ім'я стовпця. наприклад:

SELECT SUM (Сумма_заказа) FROM Клієнти;

Дане SQL-вираз повертає таблицю, що складається з одного стовпця і запису і містить суму всіх визначених значень стовпця Сумма_заказа з таблиці Клієнти.

Припустимо, що у вихідній таблиці значення стовпця Сумма_заказа виражені в рублях, а нам потрібно обчислити загальну суму в доларах. Якщо поточний обмінний курс дорівнює, наприклад, 27,8, то отримати необхідний результат можна за допомогою формули:

SELECT SUM (Сумма_заказа * 27.8) FROM Клієнти;

AVG (параметр ) Повертає середнє арифметичне всіх значень наданого в секції стовпчика. Параметр може являти собою вираз, що містить ім'я стовпця. наприклад:

SELECT AVG (Сумма_заказа) FROM Клієнти;

SELECT AVG (Сумма_заказа * 27.8) FROM Клієнти

WHERE Регіон<> "Северо_3апад";

МАХ (параметр ) Повертає максимальне значення в стовпці, зазначеному в параметрі. Параметр може також являти собою вираз, що містить ім'я стовпця. наприклад:

SELECT МАХ (Сумма__заказа) FROM Клієнти;

SELECT МАХ (Сумма_заказа * 27.8) FROM Клієнти

W HERE регіон<> "Северо_3апад";

MIN (параметр ) Повертає мінімальне значення в стовпці, зазначеному в параметрі. Параметр може являти собою вираз, що містить ім'я стовпця. наприклад:

SELECT MIN (Сумма_заказа) FROM Клієнти;

SELECT MIN (Сумма__заказа * 27. 8) FROM Клієнти

W HERE регіон<> "Северо_3апад";

На практиці нерідко потрібно отримати підсумкову таблицю, яка містить сумарні, усереднені, максимальні і мінімальні значення числових стовпців. Для цього слід використовувати угруповання (GROUP BY) і підсумкові функції.

SELECT Регіон, SUM (Сумма_заказа) FROM Клієнти

GROUP BY Регіон;

Результатная таблиця для даного запиту містить імена регіонів і підсумкові (загальні) суми замовлень всіх клієнтів з відповідних регіонів (рис. 5).

Тепер розглянемо запит на отримання всіх підсумкових даних по регіонах:

SELECT Регіон, SUM (Сумма_заказа), AVG (Сумма_заказа), МАХ (Сумма_заказа),MIN (Сумма_заказа)

FROM Клієнти

GROUP BY Регіон;

Вихідна і результатная таблиці показані на рис. 8. У прикладі тільки Північно-Західний регіон представлений у вихідній таблиці більш ніж одним записом. Тому в результатной таблиці для нього різні підсумкові функції дають різні значення.

Мал. 8. Підсумкова таблиця сум замовлень по регіонах

При використанні підсумкових функцій в списку стовпців в операторі SELECT заголовки відповідних їм стовпців в результатной таблиці мають вигляд Expr1001, Expr1002 і т.д. (Або що-небудь аналогічне, в залежності від реалізації SQL). Однак заголовки для значень підсумкових функцій і інших стовпців ви можете задавати на свій розсуд. Для цього досить після стовпчика в операторі SELECT вказати вираз виду:

AS заголовок_столбца

Ключове слово AS (як) означає, що в результатной таблиці відповідний стовпець повинен мати заголовок, вказаний після AS. Який призначається заголовок ще називають псевдонімом. У наступному прикладі (рис. 9) задаються псевдоніми для всіх обчислюваних стовпців:

SELECT Регіон,

SUM (Сумма_заказа) AS [Загальна сума замовлення],

AVG (Сумма_заказа) AS [Середня сума замовлення],

МАХ (Сумма_заказа) AS Максимум,

MIN (Сумма_заказа) AS Мінімум,

FROM Клієнти

GROUP BY Регіон;

Мал. 9. Підсумкова таблиця сум замовлень по регіонах із застосуванням псевдонімів стовпчика

Псевдоніми, які складаються з декількох слів, розділених пробілами, полягають в квадратні дужки.

Підсумкові функції можна використовувати в виразах SELECT і HAVING, але їх не можна застосовувати в вираженні WHERE. Oneратор HAVING аналогічний оператору WHERE, але на відміну від WHERE він відбирає записи в групах.

Припустимо, потрібно визначити, в яких регіонах більше одного клієнта. З цією метою можна скористатися таким запитом:

SELECT Регіон, Count (*)

FROM Клієнти

GROUP BY Регіон HAVING COUNT (*)\u003e 1;

Функції значень

При роботі з даними часто доводиться їх обробляти (перетворювати до потрібного вигляду): виділити в рядку деяку подстроку, видалити ведучі та заключні прогалини, округлити число, обчислити квадратний корінь, визначити поточний час і т. П. У SQL є наступні три типи функцій:

рядкові функції;
числові функції;
функції дати-часу.

строкові функції

Строкові функції приймають в якості параметра рядок і повертають після її обробки рядок або NULL.

SUBSTRING (Рядок FROM початок) повертає підрядок, яка утворюється з рядка, яка вказана в якості параметрарядок. підрядок починається з символу, порядковий номер якого вказаний у параметрі початок, і має довжину, зазначену в параметрі довжина. Нумерація символів рядка ведеться зліва направо, починаючи з 1. Квадратні дужки тут вказують лише на те, що укладена в них вираз не є обов'язковим. якщо виразFOR довжина не використовується, то повертається підрядок відпочаток і до кінця заданої стрічки. значення параметрівпочаток і довжина повинні вибиратися так, щоб шукана підрядок дійсно знаходилася всередині заданої стрічки. В іншому випадку функція SUBSTRING поверне NULL.

наприклад:

SUBSTRING ( "Дорога Маша!" FROM 9 FOR 4) повертає "Маша";

SUBSTRING ( "Дорога Маша!" FROM 9) повертає "Маша!";

SUBSTRING ( "Дорога Маша!" FROM 15) повертає NULL.

Використовувати цю функцію в SQL-вираженні можна, наприклад, так:

SELECT * FROM Клієнти

WHERE SUBSTRING (Регіон FROM 1 FOR 5) \u003d "Північ";

UPPER (рядок ) Переводить всі символи зазначеної в параметрі рядка в верхній регістр.
LOWER (рядок ) Переводить всі символи зазначеної в параметрі рядка в нижній регістр.
TRIM (LEADING | TRAILING | BOTH [ "символ"] FROM рядок ) Видаляє провідні (LEADING), заключні (TRAILING) або ті та інші (BOTH) символи з рядка. За замовчуванням видаляється символом є пробіл ( ""), тому його можна не вказувати. Найчастіше ця функція використовується саме для видалення пробілів.

наприклад:

TRIM (LEADING "" FROM "місто Санкт-Петербург") обертає "місто Санкт-Петербург";

TRIM (TRALING "" FROM "місто Санкт-Петербург") повертає "місто Санкт-Петербург";

TRIM (BOTH "" FROM "місто Санкт-Петербург") повертає "місто Санкт-Петербург";

TRIM (BOTH FROM "місто Санкт-Петербург") повертає "місто Санкт-Петербург";

TRIM (BOTH "г" FROM "місто Санкт-Петербург") повертає "ород Санкт-Петербур".

Серед цих функцій найбільш часто використовувані - SUBSTRING () І TRIM ().

числові функції

Числові функції як параметр можуть приймати дані не тільки числового типу, але повертають завжди число або NULL (невизначене значення).

POSITION ( целеваяСтрока IN рядок) Шукає входження цільової рядки в зазначений рядок. У разі успішного пошуку повертає номер положення її першого символу, інакше 0. Якщо цільовий рядок має нульову довжину (наприклад, рядок ""), то функція повертає 1. Якщо хоча б один з параметрів має значення NULL, то повертається NULL. Нумерація символів рядка ведеться зліва направо, починаючи з 1.

наприклад:

POSITION ( "e" IN "Привіт всім") повертає 5;

POSITION ( "всeм" IN "Привіт всім") повертає 8;

POSITION ( "" Привіт всім ") повертає 1;

POSITION ( "Привіт!" IN "Привіт всім") повертає 0.

У таблиці Клієнти (див. Рис. 1) стовпець Адреса містить, крім назви міста, поштовий індекс, назва вулиці та інші дані. Можливо, вам буде потрібно вибрати записи про клієнтів, які проживають в певному місті. Так, якщо потрібно вибрати записи, які стосуються клієнтам, які проживають в Санкт-Петербурзі, то можна скористатися наступним виразом SQL-запиту:

SELECT * FROM Клієнти

WHERE POSITION ( "Санкт - Петербург" IN Адреса)\u003e 0;

Зауважимо, що цей простий запит на вибірку даних можна сформулювати інакше:

SELECT * FROM Клієнти

WHERE Адреса LIKE "% Петербург%";

EXTRACT (параметр ) Витягує елемент зі значення типу дата-час або з інтервалу. наприклад:

EXTRACT (MONTH FROM DATE "2005-10-25")повертає 10.

CHARACTER_LENGTH (рядок ) Повертає кількість символів в рядку.

наприклад:

CHARACTER_LENGTH ( "Привіт всім") повертає 11.

OCTET_LENGTH (рядок ) Повертає кількість октетів (байтів) в рядку. Кожен символ латиниці або кирилиці представляється одним байтом, а символ китайського алфавіту двома байтами.
CARDINALITY (параметр ) Приймає як параметр колекцію елементів і повертає кількість елементів в колекції (кардинальне число). Колекція може бути, наприклад, масивом або мультімножество, що містить елементи різних типів.
ABS (число ) Повертає абсолютне значення числа. наприклад:

ABS (-123) повертає 123;

ABS (2 - 5) повертає 3.

МО D (число1, число2 ) Повертає залишок від цілочисельного ділення першого числа на друге. наприклад:

MOD (5, з) повертає 2;

MOD (2, з) повертає 0.

LN (число ) Повертає натуральний логарифм числа.
ЕХР (число) повертає е число (Підстава натурального логарифма в ступеня число).
POWER (число1, число2 ) Повертає число1число2 (Число1 в ступеня число2).
SQRT (число ) Повертає квадратний корінь з числа.
FLOOR (число ) Повертає найбільше ціле число, яке не перевищує задане параметром (округлення в меншу сторону). наприклад:

FLOOR (5.123) повертає 5.0.

CEIL (число) або CEILING (число ) Повертає найменше ціле число, яке менше заданого параметром округлення в більшу сторону). наприклад:

CEIL (5.123) повертає 6. 0.

WIDTH_BUCKET (Число1, число2, чіслоЗ, чісло4) повертає ціле число в діапазоні між 0 і чісло4 + 1. Параметри число2 і чіслоЗ задають числовий відрізок, розділений на рівновеликі інтервали, кількість яких задається параметром число 4. Функція визначає номер інтервалу, в який потрапляє значення число1. Якщо число1 знаходиться за межами заданого діапазону, то функція повертає 0 або число 4 + 1. Наприклад:

WIDTH_BUCKET (3.14, 0, 9, 5) повертає 2.

Функції дати-часу

У мові SQL є три функції, які повертають поточні дату і час.

CURRENT_DATE повертає поточну дату (тип DATE).

Наприклад: 2005-06-18.

CURRENT_TIME (число ) Повертає поточний час (тип TIME). Цілочисельний параметр вказує точність представлення секунд. Наприклад, при значенні 2 секунди будуть представлені з точністю до сотих (дві цифри в дробової частини):

12:39:45.27.

CURRENT_TIMESTAMP (число ) Повертає дату і час (тип TIMESTAMP). Наприклад, 2005-06-18 12: 39: 45.27. Цілочисельний параметр вказує точність представлення секунд.

Зверніть увагу, що дата і час, які повертаються цими функціями, мають не символьний тип. Якщо у Вас можуть запитати їх у вигляді символьних рядків, то для цього слід використовувати функцію перетворення типу CAST ().

Функції дати-часу зазвичай застосовуються в запитах на вставку, оновлення та видалення даних. Наприклад, під час запису відомостей про продажі в спеціально передбачений для цього стовпець вносяться поточні дата і час. Після підбиття підсумків за місяць або квартал, дані про продажі за звітний період можна видалити.

обчислювані вирази

Обчислювані вирази будуються з констант (числових, рядкових, логічних), функцій, імен полів і даних інших типів шляхом з'єднання їх арифметичними, строковими, логічними та іншими операторами. У свою чергу, вирази можуть бути об'єднані за допомогою операторів в більш складні (складені) вираження. Для управління порядком обчислення виразів використовуються круглі дужки.

Логічні оператори AND, OR і NOT і функції були розглянуті раніше.

Арифметичні оператори:

+ Складання;
- віднімання;
* Множення;
/ Поділ.

Строковий оператор тільки один оператор конкатенації або склейки рядків (| |). У деяких реалізаціях SQL (наприклад, Microsoft Access) замість (| |) використовується символ (+). Оператор конкатенації приписує другий рядок до кінця першої приклад, вираз:

"Саша" | | "Любить" | | "Машу"

поверне в якості результату рядок "Сашалюбіт Машу".

При складанні виразів необхідно стежити, щоб операнди операторів мали допустимі типи. Наприклад, вираз: 123 + "Саша" неприпустимо, оскільки арифметичний оператор додавання застосовується до строковому операнду.

Обчислювані вирази можуть перебувати після оператора SELECT, а також у виразах умов операторів WHERE і HAVING.

Розглянемо кілька прикладів.

Нехай таблиця Продажі містить стовпці Тіп_товара, Кількість і Ціна, а нам потрібно знати виручку для кожного типу товару. Для цього досить у список стовпців після оператора SELECT включити вираз Кількість * Ціна:

SELECT Тіп_товара, Кількість, Ціна, Кількість * ЦінаAS

Разом FROM Продажі;

Тут використовується ключове слово AS (як) для завдання псевдоніма стовпчика з обчислюються даними.

На рис. 10 показані вихідна таблиця Продажі і результатная таблиця запиту.

Мал. 10. Результат запиту з обчисленням виручки по кожному типу товару

Якщо потрібно дізнатися загальну виручку від продажу всіх товарів, то достатньо застосувати наступний запит:

SELECT SUM (Кількість * Ціна) FROM Продажі;

Наступний запит містить обчислювані вирази і в списку стовпців, і в умови оператора WHERE. Він вибирає з таблиці продажу ті товари, виручка від продажу яких більше 1000:

SELECT Тіп_товара, Кількість * Ціна AS Разом

FROM Продажі

WHERE Кількість * Ціна\u003e 1000;

Припустимо, що потрібно отримати таблицю, в якій два стовпці:

Товар, який містить тип товару і ціну;

Разом, що містить виручку.

Оскільки передбачається, що в початковій таблиці продажу стовпець Тіп_товара є символьним (тип CHAR), а стовпець Ціна числовий, то при об'єднанні (склейці) даних з цих стовпців необхідно виконати приведення числового типу до символьного за допомогою функції CAST (). Запит, що виконує це завдання, виглядає так (рис. 11):

FROM Продажі;

Мал. 11. Результат запиту з об'єднанням різнотипних даних в одному стовпці

Примітка. У Microsoft Access аналогічний запит буде мати наступний вигляд:

SELECT Тіп_товара + "(Ціна:" + CStr (Ціна) + ")" AS Товар,

Кількість * Ціна AS Разом

FROM Продажі;

Умовні вирази з оператором CASE

У звичайних мовах програмування є оператори умовного переходу, які дозволяють управляти обчислювальним процесом в залежності від того, виконується чи ні деяка умова. У мові SQL таким оператором є CASE (випадок, обставина, екземпляр). У SQL: 2003 цей оператор повертає значення і, отже, може використовуватися в виразах. Він має дві основні форми, які ми розглянемо в даному розділі.

Оператор CASE зі значеннями

Оператор CASE зі значеннями має наступний синтаксис:

CASE проверяемое_значеніе

WHEN значення1 THEN результат1

WHEN значення2 THEN резул'тат2

. . .

WHEN значенням N THEN результат N

ELSE результатх

У разі, коли проверяемое_значеніе одно значення1 , Оператор CASE повертає значеннярезультат1 , Вказане після ключового слова THEN (то). В іншому випадку проверяемое_значеніе порівнюється ззначення2 , І якщо вони рівні, то повертається значення результат2. В іншому випадку перевіряється значення порівнюється з наступним значенням, зазначеним після ключового слова WHEN (коли) і т. Д. Якщо проверяемое_значеніе не дорівнює жодному з таких значень, то повертається значеннярезультат X , Вказане після ключового слова ELSE (інакше).

Ключове слово ELSE не є обов'язковим. Якщо воно відсутнє і жодне зі значень, що підлягають порівнянню, не дорівнює перевіряється значенням, то оператор CASE повертає NULL.

Припустимо, на основі таблиці Клієнти (див. Рис. 1) потрібно отримати таблицю, в якій назви регіонів замінені їх кодовими номерами. Якщо у вихідній таблиці різних регіонів не надто багато, то для вирішення даного завдання зручно скористатися запитом з оператором CASE:

SELECT Ім'я, Адреса,

CASE Регіон

WHEN "Москва" THEN "77"

WHEN "Тверська область" THEN "69"

. . .

ELSE Регіон

AS Код регіону

FROM Клієнти;

Оператор CASE з умовами пошуку

Друга форма оператора CASE передбачає його використання при пошуку в таблиці тих записів, які задовольняють певній умові:

CASE

WHEN условіе1 THEN результат1

WHEN уоловіе2 THEN результат2

. . .

WHEN умова N THEN результат N

ELSE результатх

Оператор CASE перевіряє, чи істинне условіе1 для першого запису в наборі, визначеному оператором WHERE, або у всій таблиці, якщо WHERE відсутня. Якщо так, то CASE повертає значення результат1. В іншому випадку для цього запису перевіряється условіе2. Якщо воно істинне, то повертається значення результат2 і т. Д. Якщо жодна з умов не виконується, то повертається значення результатX , Вказане після ключ го слова ELSE.

Ключове слово ELSE не є обов'язковим. Якщо воно відсутнє і жодна з умов не виконується, оператор CASE обертає NULL. Після того як оператор, що містить CASE, виконається для першого запису, відбувається перехід до наступного запису. Так триває до тих пір, поки не буде оброблений весь набір записів.

Припустимо, в таблиці книги (Назва, Ціна) стовпець має значення NULL, якщо відповідної книги немає в наявності. Наступний запит повертає таблицю, в якій замість NULL відображається текст "Немає в наявності":

SELECT Назва,

CASE

WHEN Ціна IS NULL THEN "Немає в наявності"

ELSE CAST (Ціна AS CHAR (8))

AS Ціна

FROM Книги;

Всі значення одного і того ж стовпчика повинні мати однакові типи. Тому в даному запиті використовується функція перетворення типів CAST для приведення числових значень стовпця Ціна до символьного типу.

Зверніть увагу, що замість першої форми оператора CASE завжди можна використовувати другу:

CASE

WHEN проверяемое_значеніе \u003d значення1 THEN результат1

WHEN проверяемое_значеніе \u003d значення2 THEN результат2

. . .

WHEN проверяемое_значеніе \u003d значенняN THEN peзyльтaтN

ELSE резул'татХ

Функції NULLIF і COALESCE

У ряді випадків, особливо в запитах на оновлення даних (оператор UPDATE), зручно використовувати замість громіздкого оператора CASE більш компактні функції NULLIF () (NULL, якщо) і COALESCE () (об'єднувати).

Функція NULLIF ( значення1, значення2) Повертає NULL, якщо значення першого параметра відповідає значенню другого параметра, в разі невідповідності повертається значення першого параметра без змін. Тобто якщо рівність значення1 \u003d значення2 виконується, то функція повертає NULL, інакше значення значення1.

Ця функція еквівалентна оператору CASE в наступних двох формах:

CASE значення1

WHEN значення2 THEN NULL

ELSE значення1

CASE

WHEN значення1 \u003d значення2 THEN NULL

ELSE значення1

Функція COALESCE ( значення1, значення2, ...,значення N) приймає список значень, які можуть бути як певними, так і невизначеними (NULL). Функція повертає певне значення зі списку або NULL, якщо всі значення не визначені.

Ця функція еквівалентна наступному оператору CASE:

CASE

WHEN значення 1 IS NOT NULL THEN значення 1

WHEN значення 2 IS NOT NULL THEN значення 2

. . .

WHEN значення N IS NOT NULL THEN значення N

ELSE NULL

Припустимо, що в таблиці Книги (Назва, Ціна) стовпець Ціна має значення NULL, якщо відповідної книги немає в наявності. Наступний запит повертає таблицю, в якій замістьNULL відображається текст "Немає в наявності":

SELECT Назва, COALESCE (CAST (Ціна AS CHAR (8)),

"Немає в наявності") AS Ціна

FROM Книги;