alan_beaulieu-learning_sql-ru (865932), страница 37

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 37)

И снова соединения> 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 года.Перекрестные соединения209mysql> SELECT DATE_ADD('20040101',> 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('20040101',> INTERVAL (ones.num + tens.num + hundreds.num) DAY) < '20050101';++| dt|++| 20040101 || 20040102 || 20040103 || 20040104 || 20040105 || 20040106 || 20040107 || 20040108 || 20040109 || 20040110 |.........| 20040220 || 20040221 || 20040222 || 20040223 |210Глава 10.

И снова соединения| 20040224 || 20040225 || 20040226 || 20040227 || 20040228 || 20040229 || 20040301 |.........| 20041220 || 20041221 || 20041222 || 20041223 || 20041224 || 20041225 || 20041226 || 20041227 || 20041228 || 20041229 || 20041230 || 20041231 |++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('20040101',>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('20040101',>INTERVAL (ones.num + tens.num + hundreds.num) DAY) <>'20050101') days> ON days.dt = a.open_date> GROUP BY days.dt;+++| dt| COUNT(a.account_id) |+++| 20040101 |0 || 20040102 |0 || 20040103 |0 || 20040104 |0 || 20040105 |0 || 20040106 |0 || 20040107 |0 || 20040108 |0 || 20040109 |0 || 20040110 |0 || 20040111 |0 || 20040112 |1 || 20040113 |0 || 20040114 |0 || 20040115 |0 |.........| 20041215 |0 || 20041216 |0 || 20041217 |0 || 20041218 |0 || 20041219 |0 || 20041220 |0 || 20041221 |0 || 20041222 |0 || 20041223 |0 || 20041224 |0 |211212Глава 10.

И снова соединения| 20041225 |0 || 20041226 |0 || 20041227 |0 || 20041228 |1 || 20041229 |0 || 20041230 |0 || 20041231 |0 |+++366 rows in set (0.03 sec)Это один из самых интересных запросов, встречавшихся до сих порв данной книге. Его ценность в том, что он включает перекрестные соединения, внешние соединения, функцию работы с датами, группировку, операции с множествами (union all) и агрегатную функцию(count()). Это не самое элегантное решение заданной проблемы, но онопослужит примером того, как с небольшой долей творчества и твердым знанием языка программирования даже такой редко используемый механизм, как перекрестные соединения, можно сделать могущественным инструментом в наборе инструментов SQL.Естественные соединенияЕсли вы ленивы (а кто не ленив?), можно выбрать тип соединения, прикотором сервер БД сам определяет необходимые условия соединенияуказанных вами таблиц.

Известный как естественное соединение (natural 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| 111111111 ||2 |1 | I| 111111111 ||3 |1 | I| 111111111 ||4 |2 | I| 222222222 ||5 |2 | I| 222222222 ||6 |3 | I| 333333333 ||7 |3 | I| 333333333 ||8 |4 | I| 444444444 ||9 |4 | I| 444444444 ||10 |4 | I| 444444444 ||11 |5 | I| 555555555 ||12 |6 | I| 666666666 ||13 |6 | I| 666666666 |Естественные соединения213|14 |7 | I| 777777777 ||15 |8 | I| 888888888 ||16 |8 | I| 888888888 ||17 |9 | I| 999999999 ||18 |9 | I| 999999999 ||19 |9 | I| 999999999 ||20 |10 | B| 041111111 ||21 |10 | B| 041111111 ||22 |11 | B| 042222222 ||23 |12 | B| 043333333 ||24 |13 | B| 044444444 |+++++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 строки.

Характеристики

Список файлов ответов (шпаргалок)