alan_beaulieu-learning_sql-ru (865932), страница 38
Текст из файла (страница 38)
Произошло следующее:поскольку сервер не смог найти два столбца с одинаковыми именамив этих двух таблицах, условие соединения сформировано не было,и для таблиц было выполнено перекрестное соединение, что в резуль тате дало 96 строк (24 счета умножить на 4 отделения).Так что стоит ли снижение нагрузки на наши дряхлые пальцы (в видуотсутствия необходимости набирать условие соединения) возникаю щих при этом неприятностей? Конечно, нет. Следует избегать приме нения этого типа соединения и использовать внутренние соединенияс явными условиями соединения.УпражненияСледующие упражнения протестируют понимание внешних и пере крестных соединений.
Ответы приведены в приложении С.Упражнения21510.1Напишите запрос, возвращающий все типы счетов и открытые счетаэтих типов (для соединения с таблицей product используйте столбецproduct_cd таблицы account). Должны быть включены все типы счетов,даже если не был открыт ни один счет определенного типа.10.2Переформулируйте запрос из упражнения 10.1 и примените другойтип внешнего соединения (т. е. если в упражнении 10.1 использова лось левостороннее внешнее соединение, используйте правосторон нее), так чтобы результаты были, как в упражнении 10.1.10.3Проведите внешнее соединение таблицы account с таблицами individual и business (посредством столбца account.cust_id) таким образом,чтобы результирующий набор содержал по одной строке для каждогосчета.
Должны быть включены столбцы count.account_id, account.product_cd, individual.fname, individual.lname и business.name.10.4 (дополнительно)Разработайте запрос, который сформирует набор {1, 2, 3,…, 99, 100}.(Совет: используйте перекрестное соединение как минимум с двумяподзапросами в блоке from.)Условная логикаВ определенных ситуациях может потребоваться, чтобы SQL выраже ния вели себя так или иначе в зависимости от значений определенныхстолбцов или выражений. Эта глава посвящена написанию выраже ний, которые могут вести себя по разному в зависимости от данных,полученных во время выполнения.Что такое условная логика?Условная логика – это просто способность выбирать одно из направле ний выполнения программы.
Например, при запросе информациио клиенте может потребоваться в зависимости от типа клиента извлечьстолбцы fname/lname таблицы individual или столбец name таблицы business. С помощью внешних соединений можно было бы выбрать обестроки и дать возможность вызывающему определить, какую из нихиспользовать: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 | 111111111 | I| James Hadley| NULL||2 | 222222222 | I| Susan Tingley | NULL||3 | 333333333 | I| Frank Tucker| NULL|4 | 444444444 | I| John Hayward| NULL|||5 | 555555555 | I| Charles Frasier | NULL|Что такое условная логика?217|6 | 666666666 | I| John Spencer| NULL||7 | 777777777 | I| Margaret Young | NULL||8 | 888888888 | I| Louis Blake| NULL||9 | 999999999 | I| Richard Farley | NULL||10 | 041111111 | B| NULL| Chilton Engineering|11 | 042222222 | B| NULL| Northeast Cooling Inc.
|||12 | 043333333 | B| NULL| Superior Auto Body||13 | 044444444 | 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 | 111111111 | James Hadley||2 | 222222222 | Susan Tingley||3 | 333333333 | Frank Tucker||4 | 444444444 | John Hayward||5 | 555555555 | Charles Frasier||6 | 666666666 | John Spencer||7 | 777777777 | Margaret Young||8 | 888888888 | Louis Blake||9 | 999999999 | Richard Farley||10 | 041111111 | Chilton Engineering||11 | 042222222 | Northeast Cooling Inc.
||12 | 043333333 | Superior Auto Body||13 | 044444444 | 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в действии.Выражения case с перебором вариантовПриведенное ранее в этой главе выражение case – пример выраженияcase с перебором вариантов (searched case expression), имеющего сле дующий синтаксис:CASEWHEN C1 THEN E1WHEN C2 THEN E2...WHEN CN THEN EN[ELSE ED]ENDВ этом описании символами C1, C2, …, CN обозначены условия, а символа ми E1, E2, …, EN – выражения, которые должны быть возвращены выра жением case.
Если условие в блоке when выполняется, выражение caseвозвращает соответствующее выражение. Кроме того, символ ED пред ставляет применяемое по умолчанию выражение, возвращаемое выра жением case, если не выполнено ни одно из условий C1, C2, …, CN (блокelse является необязательным, поэтому он заключен в квадратные скоб ки). Все выражения, возвращаемые различными блоками when, должныобеспечивать результаты одного типа (например, date, number, varchar).Вот пример выражения case с перебором вариантов:CASEWHEN employee.title = 'Head Teller'THEN 'Head Teller'WHEN employee.title = 'Teller'Выражение case219AND YEAR(employee.start_date) > 2004THEN 'Teller Trainee'WHEN employee.title = 'Teller'AND YEAR(employee.start_date) < 2003THEN 'Experienced Teller'WHEN employee.title = 'Teller'THEN 'Teller'ELSE 'NonTeller'ENDЭто выражение case возвращает строку, с помощью которой можно оп ределять расценки почасовой оплаты, печатать бейджи с именами и т.
д.При вычислении выражения case блоки when обрабатываются сверхувниз. Как только одно из условий блока when принимает значение true,возвращается соответствующее выражение, а все остальные блоки whenигнорируются. Если ни одно из условий блока when не выполняется,возвращается выражение блока else.Хотя предыдущий пример возвращает строковые выражения, помни те, что выражения case могут возвращать выражения любого типа,включая подзапросы. Вот еще одна версия приведенного ранее в этойглаве запроса имени физического лица/названия фирмы, в которойдля извлечения данных из таблиц individual и business вместо внеш них соединений используются подзапросы:mysql> SELECT c.cust_id, c.fed_id,> CASE>WHEN c.cust_type_cd = 'I' THEN>(SELECT CONCAT(i.fname, ' ', i.lname)>FROM individual i>WHERE i.cust_id = c.cust_id)>WHEN c.cust_type_cd = 'B' THEN>(SELECT b.name>FROM business b>WHERE b.cust_id = c.cust_id)>ELSE 'Unknown'> END name> FROM customer c;++++| cust_id | fed_id| name|++++|1 | 111111111 | James Hadley||2 | 222222222 | Susan Tingley||3 | 333333333 | Frank Tucker||4 | 444444444 | John Hayward||5 | 555555555 | Charles Frasier||6 | 666666666 | John Spencer||7 | 777777777 | Margaret Young||8 | 888888888 | Louis Blake||9 | 999999999 | Richard Farley||10 | 041111111 | Chilton Engineering|220Глава 11.
Условная логика|11 | 042222222 | Northeast Cooling Inc. ||12 | 043333333 | Superior Auto Body||13 | 044444444 | AAA Insurance Inc.|++++13 rows in set (0.01 sec)В этом варианте запроса в блок from включена только таблица customerи соответствующее имя для каждого клиента получается с помощьюсвязанного подзапроса. Эта версия мне нравится больше той, где при меняются внешние соединения, поскольку здесь сервер считываетданные таблицы individual и business только в случае необходимости,а не соединяет все три таблицы.Простые выражения caseПростое выражение case (simple case expression) очень похоже на вы ражение case с перебором вариантов, но несколько менее функцио нально.
Вот его синтаксис:CASE V0WHEN V1 THEN E1WHEN V2 THEN E2...WHEN VN THEN EN[ELSE ED]ENDВ этом описании V0 представляет значение, а символы V1, V2, …, VN –значения, сравниваемые с V0. Символы E1, E2, …, EN представляют вы ражения, возвращаемые выражением case, а ED – выражение, котороедолжно быть возвращено, если ни одно из значений набора V1, V2, …, VNне соответствует значению V0.Вот пример простого выражения case:CASE customer.cust_type_cdWHEN 'I' THEN(SELECT CONCAT(i.fname, ' ', i.lname)FROM individual IWHERE i.cust_id = customer.cust_id)WHEN 'B' THEN(SELECT b.nameFROM business bWHERE b.cust_id = customer.cust_id)ELSE 'Unknown Customer Type'ENDПростые выражения case менее функциональны, чем выражения caseс перебором вариантов, потому что в них нельзя задать собственные ус ловия; в них просто используются условия равенства.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
