изучаем SQL. Она позволяет решать многошаговые задачи одним выражением
 Скачать 1.6 Mb.
|
|
SELECT ones.num + tens.num + hundreds.num > FROM > (SELECT 0 num UNION ALL > SELECT 1 num UNION ALL > SELECT 2 num UNION ALL > SELECT 3 num UNION ALL > SELECT 4 num UNION ALL > SELECT 5 num UNION ALL > SELECT 6 num UNION ALL > SELECT 7 num UNION ALL > SELECT 8 num UNION ALL > SELECT 9 num) ones > CROSS JOIN > (SELECT 0 num UNION ALL > SELECT 10 num UNION ALL > SELECT 20 num UNION ALL > SELECT 30 num UNION ALL > SELECT 40 num UNION ALL > SELECT 50 num UNION ALL > SELECT 60 num UNION ALL 208 Глава 10. И снова соединения > SELECT 70 num UNION ALL > SELECT 80 num UNION ALL > SELECT 90 num) tens > CROSS JOIN > (SELECT 0 num UNION ALL > SELECT 100 num UNION ALL > SELECT 200 num UNION ALL > SELECT 300 num) hundreds; + + | ones.num + tens.num + hundreds.num | + + | 0 | | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | 11 | | 12 | | 391 | | 392 | | 393 | | 394 | | 395 | | 396 | | 397 | | 398 | | 399 | + + 400 rows in set (0.00 sec) Если найти декартово произведение трех наборов – {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, {0, 10, 20, 30, 40, 50, 60, 70, 80, 90} и {0, 100, 200, 300} – и сло жить значения во всех трех столбцах, получится результат, состоя щий из 400 строк, содержащих все числа от 0 до 399. Хотя это больше 366 строк, необходимых для формирования набора дней 2004 года, из бавиться от лишних строк достаточно просто. Вскоре я покажу, как это сделать. Следующий шаг – преобразовать числа в набор дат. Для этого восполь зуемся функцией date_add() и добавим каждое число в результирую щий набор к 1 января 2004 года. Затем введем условие фильтрации, чтобы отбросить все даты 2005 года. Перекрестные соединения 209 mysql> SELECT DATE_ADD('2004 01 01', > INTERVAL (ones.num + tens.num + hundreds.num) DAY) dt > FROM > (SELECT 0 num UNION ALL > SELECT 1 num UNION ALL > SELECT 2 num UNION ALL > SELECT 3 num UNION ALL > SELECT 4 num UNION ALL > SELECT 5 num UNION ALL > SELECT 6 num UNION ALL > SELECT 7 num UNION ALL > SELECT 8 num UNION ALL > SELECT 9 num) ones > CROSS JOIN > (SELECT 0 num UNION ALL > SELECT 10 num UNION ALL > SELECT 20 num UNION ALL > SELECT 30 num UNION ALL > SELECT 40 num UNION ALL > SELECT 50 num UNION ALL > SELECT 60 num UNION ALL > SELECT 70 num UNION ALL > SELECT 80 num UNION ALL > SELECT 90 num) tens > CROSS JOIN > (SELECT 0 num UNION ALL > SELECT 100 num UNION ALL > SELECT 200 num UNION ALL > SELECT 300 num) hundreds > WHERE DATE_ADD('2004 01 01', > INTERVAL (ones.num + tens.num + hundreds.num) DAY) < '2005 01 01'; + + | dt | + + | 2004 01 01 | | 2004 01 02 | | 2004 01 03 | | 2004 01 04 | | 2004 01 05 | | 2004 01 06 | | 2004 01 07 | | 2004 01 08 | | 2004 01 09 | | 2004 01 10 | | 2004 02 20 | | 2004 02 21 | | 2004 02 22 | | 2004 02 23 | 210 Глава 10. И снова соединения | 2004 02 24 | | 2004 02 25 | | 2004 02 26 | | 2004 02 27 | | 2004 02 28 | | 2004 02 29 | | 2004 03 01 | | 2004 12 20 | | 2004 12 21 | | 2004 12 22 | | 2004 12 23 | | 2004 12 24 | | 2004 12 25 | | 2004 12 26 | | 2004 12 27 | | 2004 12 28 | | 2004 12 29 | | 2004 12 30 | | 2004 12 31 | + + 366 rows in set (0.01 sec) В этом подходе замечательно то, что результирующий набор автомати чески включает 29 февраля без всяких дополнительных вмеша тельств, поскольку сервер БД вычисляет его, когда добавляет 59 дней к 1 января 2004 года. Теперь, когда у нас есть механизм получения всех дней 2004 года, что мы должны с ними сделать? Ну, может потребоваться сгенерировать запрос, по которому будут выводиться все дни 2004 года с номерами счетов, открытых в каждый из дней, с числом осуществленных опера ций и т. д. Вот пример, дающий ответ на первый вопрос: mysql> SELECT days.dt, COUNT(a.account_id) > FROM account a RIGHT OUTER JOIN > (SELECT DATE_ADD('2004 01 01', > INTERVAL (ones.num + tens.num + hundreds.num) DAY) dt > FROM > (SELECT 0 num UNION ALL > SELECT 1 num UNION ALL > SELECT 2 num UNION ALL > SELECT 3 num UNION ALL > SELECT 4 num UNION ALL > SELECT 5 num UNION ALL > SELECT 6 num UNION ALL > SELECT 7 num UNION ALL > SELECT 8 num UNION ALL > SELECT 9 num) ones > CROSS JOIN Перекрестные соединения 211 > (SELECT 0 num UNION ALL > SELECT 10 num UNION ALL > SELECT 20 num UNION ALL > SELECT 30 num UNION ALL > SELECT 40 num UNION ALL > SELECT 50 num UNION ALL > SELECT 60 num UNION ALL > SELECT 70 num UNION ALL > SELECT 80 num UNION ALL > SELECT 90 num) tens > CROSS JOIN > (SELECT 0 num UNION ALL > SELECT 100 num UNION ALL > SELECT 200 num UNION ALL > SELECT 300 num) hundreds > WHERE DATE_ADD('2004 01 01', > INTERVAL (ones.num + tens.num + hundreds.num) DAY) < > '2005 01 01') days > ON days.dt = a.open_date > GROUP BY days.dt; + + + | dt | COUNT(a.account_id) | + + + | 2004 01 01 | 0 | | 2004 01 02 | 0 | | 2004 01 03 | 0 | | 2004 01 04 | 0 | | 2004 01 05 | 0 | | 2004 01 06 | 0 | | 2004 01 07 | 0 | | 2004 01 08 | 0 | | 2004 01 09 | 0 | | 2004 01 10 | 0 | | 2004 01 11 | 0 | | 2004 01 12 | 1 | | 2004 01 13 | 0 | | 2004 01 14 | 0 | | 2004 01 15 | 0 | | 2004 12 15 | 0 | | 2004 12 16 | 0 | | 2004 12 17 | 0 | | 2004 12 18 | 0 | | 2004 12 19 | 0 | | 2004 12 20 | 0 | | 2004 12 21 | 0 | | 2004 12 22 | 0 | | 2004 12 23 | 0 | | 2004 12 24 | 0 | 212 Глава 10. И снова соединения | 2004 12 25 | 0 | | 2004 12 26 | 0 | | 2004 12 27 | 0 | | 2004 12 28 | 1 | | 2004 12 29 | 0 | | 2004 12 30 | 0 | | 2004 12 31 | 0 | + + + 366 rows in set (0.03 sec) Это один из самых интересных запросов, встречавшихся до сих пор в данной книге. Его ценность в том, что он включает перекрестные со единения, внешние соединения, функцию работы с датами, группи ровку, операции с множествами (union all) и агрегатную функцию (count()). Это не самое элегантное решение заданной проблемы, но оно послужит примером того, как с небольшой долей творчества и твер дым знанием языка программирования даже такой редко используе мый механизм, как перекрестные соединения, можно сделать могуще ственным инструментом в наборе инструментов SQL. Естественные соединения Если вы ленивы (а кто не ленив?), можно выбрать тип соединения, при котором сервер БД сам определяет необходимые условия соединения указанных вами таблиц. Известный как естественное соединение (na tural join ), этот тип соединения делает предположение о необходимых условиях соединения, полагаясь на идентичные имена столбцов в таб лицах. Например, таблица account включает столбец cust_id, являю щийся внешним ключом к таблице customer, первичный ключ которой также имеет имя cust_id. Таким образом, можно написать запрос, ис пользующий для соединения этих двух таблиц natural join: mysql> SELECT a.account_id, a.cust_id, c.cust_type_cd, c.fed_id > FROM account a NATURAL JOIN customer c; + + + + + | account_id | cust_id | cust_type_cd | fed_id | + + + + + | 1 | 1 | I | 111 11 1111 | | 2 | 1 | I | 111 11 1111 | | 3 | 1 | I | 111 11 1111 | | 4 | 2 | I | 222 22 2222 | | 5 | 2 | I | 222 22 2222 | | 6 | 3 | I | 333 33 3333 | | 7 | 3 | I | 333 33 3333 | | 8 | 4 | I | 444 44 4444 | | 9 | 4 | I | 444 44 4444 | | 10 | 4 | I | 444 44 4444 | | 11 | 5 | I | 555 55 5555 | | 12 | 6 | I | 666 66 6666 | | 13 | 6 | I | 666 66 6666 | Естественные соединения 213 | 14 | 7 | I | 777 77 7777 | | 15 | 8 | I | 888 88 8888 | | 16 | 8 | I | 888 88 8888 | | 17 | 9 | I | 999 99 9999 | | 18 | 9 | I | 999 99 9999 | | 19 | 9 | I | 999 99 9999 | | 20 | 10 | B | 04 1111111 | | 21 | 10 | B | 04 1111111 | | 22 | 11 | B | 04 2222222 | | 23 | 12 | B | 04 3333333 | | 24 | 13 | B | 04 4444444 | + + + + + 24 rows in set (0.02 sec) Поскольку задано естественное соединение, сервер проверил описания таблиц и добавил для этих двух таблиц условие соединения a.cust_id = c.cust_id Все это хорошо и замечательно, но что будет, если имена столбцов в таблицах не совпадают? Например, в таблице account также есть внешний ключ к таблице branch, но этот столбец в таблице account на зван open_branch_id, а не branch_id. Посмотрим, что произойдет, если попытаться провести natural join между таблицами account и branch: mysql> SELECT a.account_id, a.cust_id, a.open_branch_id, > FROM account a NATURAL JOIN branch b; + + + + + | account_id | cust_id | open_branch_id | name | + + + + + | 1 | 1 | 2 | Headquarters | | 2 | 1 | 2 | Headquarters | | 3 | 1 | 2 | Headquarters | | 4 | 2 | 2 | Headquarters | | 5 | 2 | 2 | Headquarters | | 6 | 3 | 3 | Headquarters | | 7 | 3 | 3 | Headquarters | | 8 | 4 | 1 | Headquarters | | 9 | 4 | 1 | Headquarters | | 10 | 4 | 1 | Headquarters | | 11 | 5 | 4 | Headquarters | | 12 | 6 | 1 | Headquarters | | 13 | 6 | 1 | Headquarters | | 14 | 7 | 2 | Headquarters | | 15 | 8 | 4 | Headquarters | | 16 | 8 | 4 | Headquarters | | 17 | 9 | 1 | Headquarters | | 18 | 9 | 1 | Headquarters | | 19 | 9 | 1 | Headquarters | | 20 | 10 | 4 | Headquarters | | 21 | 10 | 4 | Headquarters | | 22 | 11 | 2 | Headquarters | | 23 | 12 | 4 | Headquarters | 214 Глава 10. И снова соединения | 24 | 13 | 3 | Headquarters | | 1 | 1 | 2 | So. NH Branch | | 2 | 1 | 2 | So. NH Branch | | 3 | 1 | 2 | So. NH Branch | | 4 | 2 | 2 | So. NH Branch | | 5 | 2 | 2 | So. NH Branch | | 6 | 3 | 3 | So. NH Branch | | 7 | 3 | 3 | So. NH Branch | | 8 | 4 | 1 | So. NH Branch | | 9 | 4 | 1 | So. NH Branch | | 10 | 4 | 1 | So. NH Branch | | 11 | 5 | 4 | So. NH Branch | | 12 | 6 | 1 | So. NH Branch | | 13 | 6 | 1 | So. NH Branch | | 14 | 7 | 2 | So. NH Branch | | 15 | 8 | 4 | So. NH Branch | | 16 | 8 | 4 | So. NH Branch | | 17 | 9 | 1 | So. NH Branch | | 18 | 9 | 1 | So. NH Branch | | 19 | 9 | 1 | So. NH Branch | | 20 | 10 | 4 | So. NH Branch | | 21 | 10 | 4 | So. NH Branch | | 22 | 11 | 2 | So. NH Branch | | 23 | 12 | 4 | So. NH Branch | | 24 | 13 | 3 | So. NH Branch | + + + + + 96 rows in set (0.03 sec) Кажется, здесь что то не так; запрос должен возвращать не более 24 строк, поскольку в таблице account 24 строки. Произошло следующее: поскольку сервер не смог найти два столбца с одинаковыми именами в этих двух таблицах, условие соединения сформировано не было, и для таблиц было выполнено перекрестное соединение, что в резуль тате дало 96 строк (24 счета умножить на 4 отделения). Так что стоит ли снижение нагрузки на наши дряхлые пальцы (в виду отсутствия необходимости набирать условие соединения) возникаю щих при этом неприятностей? Конечно, нет. Следует избегать приме нения этого типа соединения и использовать внутренние соединения с явными условиями соединения. Упражнения Следующие упражнения протестируют понимание внешних и пере крестных соединений. Ответы приведены в приложении С. Упражнения 215 10.1 Напишите запрос, возвращающий все типы счетов и открытые счета этих типов (для соединения с таблицей product используйте столбец product_cd таблицы account). Должны быть включены все типы счетов, даже если не был открыт ни один счет определенного типа. 10.2 Переформулируйте запрос из упражнения 10.1 и примените другой тип внешнего соединения (т. е. если в упражнении 10.1 использова лось левостороннее внешнее соединение, используйте правосторон нее), так чтобы результаты были, как в упражнении 10.1. 10.3 Проведите внешнее соединение таблицы account с таблицами individ ual и business (посредством столбца account.cust_id) таким образом, чтобы результирующий набор содержал по одной строке для каждого счета. Должны быть включены столбцы count.account_id, account.prod uct_cd , individual.fname, individual.lname и business.name. 10.4 (дополнительно) Разработайте запрос, который сформирует набор {1, 2, 3,…, 99, 100}. (Совет: используйте перекрестное соединение как минимум с двумя подзапросами в блоке from.) Условная логика В определенных ситуациях может потребоваться, чтобы SQL выраже ния вели себя так или иначе в зависимости от значений определенных столбцов или выражений. Эта глава посвящена написанию выраже ний, которые могут вести себя по разному в зависимости от данных, полученных во время выполнения. Что такое условная логика? Условная логика – это просто способность выбирать одно из направле ний выполнения программы. Например, при запросе информации о клиенте может потребоваться в зависимости от типа клиента извлечь столбцы fname/lname таблицы individual или столбец name таблицы busi ness . С помощью внешних соединений можно было бы выбрать обе строки и дать возможность вызывающему определить, какую из них использовать: mysql> SELECT c.cust_id, c.fed_id, c.cust_type_cd, > CONCAT(i.fname, ' ', i.lname) indiv_name, > b.name business_name > FROM customer c LEFT OUTER JOIN individual i > ON c.cust_id = i.cust_id > LEFT OUTER JOIN business b > ON c.cust_id = b.cust_id; + + + + + + | cust_id | fed_id | cust_type_cd | indiv_name | business_name | + + + + + + | 1 | 111 11 1111 | I | James Hadley | NULL | | 2 | 222 22 2222 | I | Susan Tingley | NULL | | 3 | 333 33 3333 | I | Frank Tucker | NULL | | 4 | 444 44 4444 | I | John Hayward | NULL | | 5 | 555 55 5555 | I | Charles Frasier | NULL | Что такое условная логика? 217 | 6 | 666 66 6666 | I | John Spencer | NULL | | 7 | 777 77 7777 | I | Margaret Young | NULL | | 8 | 888 88 8888 | I | Louis Blake | NULL | | 9 | 999 99 9999 | I | Richard Farley | NULL | | 10 | 04 1111111 | B | NULL | Chilton Engineering | | 11 | 04 2222222 | B | NULL | Northeast Cooling Inc. | | 12 | 04 3333333 | B | NULL | Superior Auto Body | | 13 | 04 4444444 | B | NULL | AAA Insurance Inc. | + + + + + + 13 rows in set (0.13 sec) Вызывающий может взглянуть на значение столбца cust_type_cd и вы брать, какой столбец использовать – indiv_name или business_name. Од нако вместо этого можно было бы применить условную логику, вос пользовавшись выражением case, чтобы определить тип клиента и воз вратить соответствующую строку. mysql> SELECT c.cust_id, c.fed_id, > CASE > WHEN c.cust_type_cd = 'I' > THEN CONCAT(i.fname, ' ', i.lname) > WHEN c.cust_type_cd = 'B' > THEN b.name > ELSE 'Unknown' > END name > FROM customer c LEFT OUTER JOIN individual i > ON c.cust_id = i.cust_id > LEFT OUTER JOIN business b > ON c.cust_id = b.cust_id; + + + + | cust_id | fed_id | name | + + + + | 1 | 111 11 1111 | James Hadley | | 2 | 222 22 2222 | Susan Tingley | | 3 | 333 33 3333 | Frank Tucker | | 4 | 444 44 4444 | John Hayward | | 5 | 555 55 5555 | Charles Frasier | | 6 | 666 66 6666 | John Spencer | | 7 | 777 77 7777 | Margaret Young | | 8 | 888 88 8888 | Louis Blake | | 9 | 999 99 9999 | Richard Farley | | 10 | 04 1111111 | Chilton Engineering | | 11 | 04 2222222 | Northeast Cooling Inc. | | 12 | 04 3333333 | Superior Auto Body | | 13 | 04 4444444 | AAA Insurance Inc. | + + + + 13 rows in set (0.00 sec) Эта версия запроса возвращает один столбец name. Он формируется выра жением case, начинающимся во второй строке запроса , которое в дан 218 Глава 11. Условная логика ном случае проверяет значение столбца cust_type_cd и возвращает имя/ фамилию физического лица или название фирмы. Выражение case Все основные серверы БД включают встроенные функции, имитирую щие выражение if then else, которое есть в большинстве языков про граммирования (например, функция decode() Oracle, функция if() MySQL и функция coalesce() SQL Server). Выражения case тоже разра ботаны для поддержки логики if then else, но в сравнении со встроен ными функциями обладают двумя преимуществами: • Выражение case является частью стандарта SQL (версия SQL92) и реализовано в Oracle Database, SQL Server и MySQL. • Выражения case встроены в грамматику SQL и могут быть включе ны в выражения select, insert, update и delete. В следующих двух разделах представлены выражения case двух раз ных типов, а затем я привожу несколько примеров выражений case в действии. |