Как правильно произвести выборку из подзапроса
Перейти к содержимому

Как правильно произвести выборку из подзапроса

  • автор:

Подзапросы выборки в запросах

Применимо к: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

Выражения подзапросов выборки являются вложенными выражениями SELECT, которые используются для ограничения пространства куба, из которого вычисляется внешнее выражение SELECT. Подзапросы выборки позволяют определять новое пространство, в котором будут выполняться все вычисления.

Подзапросы выборки в примерах

Начнем с примера того, как подзапросы выборки могут помочь сформировать результаты, которые нам требуется отобразить. Предположим, требуется сформировать таблицу, отражающую динамику продаж за несколько лет по 10 самым популярным продуктам.

Полученный результат должен иметь следующий вид.

Sum of Years Year 1 .
Sum of Top 10 Products
Product A
.

Для получения подобного результата можно было бы написать следующее многомерное выражение:

SELECT [Date].[Calendar Year].MEMBERS on 0 , TOPCOUNT( [Product].[Product].MEMBERS , 10 , [Measures].[Sales Amount] ) ON 1 FROM [Adventure Works] 

Оно возвращает следующие результаты.

All Periods CY 2005 CY 2006 CY 2007 CY 2008
Все продукты $ 80 450 596,98 8 065 435,31 $ 24 144 429,65 $ 32 202 669,43 $ 16 038 062,60 $
Mountain-200 Black, 38 1 634 647,94 $ (null) (null) 894 207,97 $ 740 439,97 $
Mountain-200 Black, 42 1 285 524,65 $ (null) (null) 722 137,65 $ 563 387,00 $
Mountain-200 Silver, 38 1 181 945,82 $ (null) (null) 634 600,78 $ 547 345,03 $
Mountain-200 Black, 46 995 927,43 $ (null) (null) 514 995,76 $ 480 931,68 $
Mountain-200 Silver, 42 1 005 111,77 $ (null) (null) 529 543,29 $ 475 568,49 $
Mountain-200 Silver, 46 975 932,56 $ (null) (null) 526 759,30 $ 449 173,26 $
Road-150 Red, 56 792 228,98 $ 382 159,24 $ 410 069,74 $ (null) (null)
Mountain-200 Black, 38 1 471 078,72 $ (null) 789 958,49 $ 681 120,23 $ (null)
Road-350-W Yellow, 48 1 380 253,88 $ (null) (null) 744 988,37 $ 635 265,50 $

Это очень близко к тому, что нам нужно, за исключением того, что запрос возвратил 9 продуктов, а не 10 и что итог «Все продукты» отражает сумму по всем продуктам, а не сумму по возвращенным 9 (в данном случае) самым популярным. Еще одна попытка решить проблему представлена в следующем многомерном запросе:

SELECT [Date].[Calendar Year].MEMBERS on 0 , TOPCOUNT( [Product].[Product].CHILDREN, 10, [Measures].[Sales Amount]) ON 1 FROM [Adventure Works] 

Оно возвращает следующие результаты.

All Periods CY 2005 CY 2006 CY 2007 CY 2008
Mountain-200 Black, 38 1 634 647,94 $ (null) (null) 894 207,97 $ 740 439,97 $
Mountain-200 Black, 42 1 285 524,65 $ (null) (null) 722 137,65 $ 563 387,00 $
Mountain-200 Silver, 38 1 181 945,82 $ (null) (null) 634 600,78 $ 547 345,03 $
Mountain-200 Black, 46 995 927,43 $ (null) (null) 514 995,76 $ 480 931,68 $
Mountain-200 Silver, 42 1 005 111,77 $ (null) (null) 529 543,29 $ 475 568,49 $
Mountain-200 Silver, 46 975 932,56 $ (null) (null) 526 759,30 $ 449 173,26 $
Road-150 Red, 56 792 228,98 $ 382 159,24 $ 410 069,74 $ (null) (null)
Mountain-200 Black, 38 1 471 078,72 $ (null) 789 958,49 $ 681 120,23 $ (null)
Road-350-W Yellow, 48 1 380 253,88 $ (null) (null) 744 988,37 $ 635 265,50 $
Road-150 Red, 62 566 797,97 $ 234 018,86 $ 332 779,11 $ (null) (null)

Это было очень близко к желаемому результату, поскольку отсутствует только сумма по продуктам. На данном этапе можно было бы начать корректировать приведенное выше многомерное выражение таким образом, чтобы недостающая строка возвращалась, однако решение этой задачи может оказаться довольно громоздким.

Еще один подход к данной проблеме — попробовать переопределить пространство куба, в котором вычисляется многомерное выражение. Что если «новый» куб содержал бы только данные по 10 самым популярным продуктам? В таком кубе все элементы соответствовали бы только этим 10 популярным продуктам, и задачу тогда решил бы простой запрос.

В приведенном ниже многомерном выражении используется инструкция подзапроса выборки, переопределяющая пространство куба под 10 продуктов и формирующая следующий результат.

SELECT [Date].[Calendar Year].MEMBERS on 0 , [Product].[Product].MEMBERS on 1 FROM (SELECT TOPCOUNT( [Product].[Product].CHILDREN , 10 , [Measures].[Sales Amount] ) ON 0 FROM [Adventure Works] ) WHERE [Measures].[Sales Amount] 

Приведенное выше выражение возвратит следующие результаты.

All Periods CY 2005 CY 2006 CY 2007 CY 2008
Все продукты 19 997 183,30 $ 1 696 815,63 $ 2 816 611,28 $ 7 930 797,72 $ 7 552 958,66 $
Mountain-200 Silver, 38 2 160 981,60 $ (null) (null) 1 024 359,10 $ 1 136 622,49 $
Mountain-200 Silver, 42 1 914 547,85 $ (null) (null) 903 061,68 $ 1 011 486,18 $
Mountain-200 Silver, 46 1 906 248,55 $ (null) (null) 877 077,79 $ 1 029 170,76 $
Mountain-200 Black, 38 1 811 229,02 $ (null) 896 511,60 $ 914 717,43 $ (null)
Mountain-200 Black, 38 2 589 363,78 $ (null) (null) 1 261 406,37 $ 1 327 957,41 $
Mountain-200 Black, 42 2 265 485,38 $ (null) (null) 1 126 055,89 $ 1 139 429,49 $
Mountain-200 Black, 46 1 957 528,24 $ (null) (null) 946 453,88 $ 1 011 074,37 $
Road-150 Red, 62 1 769 096,69 $ 828 011,68 $ 941 085,01 $ (null) (null)
Road-150 Red, 56 1 847 818,63 $ 868 803,96 $ 979 014,67 $ (null) (null)
Road-350-W Yellow, 48 1 774 883,56 $ (null) (null) 877 665,59 $ 897 217,96 $

Приведенные выше результаты в точности соответствуют требованию.

Рассмотрим, что именно сделал подзапрос выборки для получения этого результата. Он возвратил новый куб, содержащий все другие измерения для продукта «как есть», но в измерении самого продукта он отфильтровал все элементы таким образом, чтобы остались только 10 самых популярных продуктов, которые нас интересовали. Практически мы убрали все данные, которые не соответствовали критерию «10 самых популярных» и перестроили куб. В данном примере важно отметить еще один момент: 10 самых популярных продуктов вычислялись по всем элементам и всем измерениям в кубе. Это верно, поскольку в подзапросе выборки не было никаких иных ограничений фильтра.

Подзапросы выборки могут быть сложными настолько, насколько это необходимо. Следующий пример иллюстрирует формирование таблицы, аналогичной приведенной выше, но с фильтром «France» в измерении «Sales Territory» и «Internet» в измерении «Sales Channel».

SELECT [Date].[Calendar Year].MEMBERS on 0 , [Product].[Product].MEMBERS on 1 FROM (SELECT TOPCOUNT( [Product].[Product].CHILDREN , 10 , [Measures].[Sales Amount] ) ON 0 , [Sales Territory].[Sales Territory].[Region].[France] on 1 , [Sales Channel].[Sales Channel].[Internet] on 2 FROM [Adventure Works] ) WHERE [Measures].[Sales Amount] 

Выданы следующие результаты.

All Periods CY 2005 CY 2006 CY 2007 CY 2008
Все продукты 748 682,49 $ 32 204,43 $ 73 125,18 $ 269 506,56 $ 373 846,32 $
Mountain-200 Silver, 38 90 479,61 $ (null) (null) 41 759,82 $ 48 719,79 $
Mountain-200 Silver, 42 97 439,58 $ (null) (null) 39 439,83 $ 57 999,75 $
Mountain-200 Silver, 46 102 079,56 $ (null) (null) 27 839,88 $ 74 239,68 $
Mountain-200 Black, 38 26 638,28 $ (null) 12 294,59 $ 14 343,69 $ (null)
Mountain-200 Black, 38 96 389,58 $ (null) (null) 41 309,82 $ 55 079,76 $
Mountain-200 Black, 42 80 324,65 $ (null) (null) 43 604,81 $ 36 719,84 $
Mountain-200 Black, 46 107 864,53 $ (null) (null) 45 899,80 $ 61 964,73 $
Road-150 Red, 62 46 517,51 $ 14 313,08 $ 32 204,43 $ (null) (null)
Road-150 Red, 56 46 517,51 $ 17 891,35 $ 28 626,16 $ (null) (null)
Road-350-W Yellow, 48 54 431,68 $ (null) (null) 15 308,91 $ 39 122,77 $

Приведенные выше результаты — это 10 самых популярных продуктов, проданных во Франции через Интернет.

Инструкция подзапроса выборки

Имя участника-службы для подзапроса выборки:

[WITH [ . ]] SELECT [ [, . ]] FROM [ | (< sub-select-statement >)] [WHERE ] [[CELL] PROPERTIES [, . ]] < sub-select-statement >:= SELECT [ [, . ]] FROM [ | (< sub-select-statement >)] [WHERE ] 

Подзапрос выборки представляет собой еще одну инструкцию Select, где определения оси и среза фильтруют пространство куба, для которого вычисляется внешнее выражение SELECT.

Если элемент указан в одной из осей или предложении среза, то данный элемент с его предками и потомками включается в подпространство куба для подзапроса выборки; все элементы с общим родителем, не указанные в предложении оси или среза, вместе с их потомками отфильтровываются из подпространства. Таким образом, пространство внешней операции выбора ограничивается существующими элементами в предложении оси или среза вместе с предками и потомками, как упоминалось выше.

Поскольку элемент «Все» всех неупомянутых измерений в предложении оси или среза принадлежат к пространству выбора, то все потомки элемента «Все» в этих измерениях также включаются в пространство подзапроса выборки.

Элемент «Все» во всех измерениях в пространстве вложенного куба вычисляется повторно, что отражает ограничения нового пространства.

Это иллюстрируется в приведенном ниже примере: первое многомерное выражение помогает отобразить неотфильтрованные значения куба, второе — иллюстрирует эффект применения фильтра в предложении подзапроса выборки.

SELECT < [Customer].[Customer Geography].[All Customers] , [Customer].[Customer Geography].[Country].&[United States] , [Customer].[Customer Geography].[State-Province].&[OR]&[US] , [Customer].[Customer Geography].[City].&[Portland]&[OR] , [Customer].[Customer Geography].[State-Province].&[WA]&[US] , [Customer].[Customer Geography].[City].&[Seattle]&[WA] >ON 1 , <[Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount]>ON 0 FROM [Adventure Works] 

Возвращает следующие значения:

Internet Sales Amount Reseller Sales Amount
Все клиенты 29 358 677,22 $ $ 80 450 596,98
США 9 389 789,51 $ $ 80 450 596,98
Орегон 1 170 991,54 $ $ 80 450 596,98
Портленд 110 649,54 $ $ 80 450 596,98
Вашингтон 2 467 248,34 $ $ 80 450 596,98
Сиэтл; 75 164,86 $ $ 80 450 596,98

В приведенном выше примере «Seattle» является дочерним для «Washington», «Portland» — для «Орегона», «Oregon» и «Washington» — дочерние для «США», а «США» — для [Customer Geography].[All Customers]. Все элементы, показанные в данном примере, имеют другие элементы с общим родителем, влияющие на статистическое значение родителя, т. е. города Спокане, Такома и Эверетт имеют общего родителя с Сиэтлом и все они будут влиять на сумму для продаж через Интернет в штате Вашингтон. Значение Reseller Sales Amount не зависит от атрибута «Customer Geography», поэтому в результатах отображается значение «Все». Следующее многомерное выражение иллюстрирует влияние фильтра на предложение подзапроса выборки.

SELECT < [Customer].[Customer Geography].[All Customers] , [Customer].[Customer Geography].[Country].&[United States] , [Customer].[Customer Geography].[State-Province].&[OR]&[US] , [Customer].[Customer Geography].[City].&[Portland]&[OR] , [Customer].[Customer Geography].[State-Province].&[WA]&[US] , [Customer].[Customer Geography].[City].&[Seattle]&[WA] >ON 1 , <[Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount]>ON 0 FROM ( SELECT [Customer].[State-Province].&[WA]&[US] ON 0 FROM [Adventure Works] ) 

Возвращает следующие значения:

Internet Sales Amount Reseller Sales Amount
Все клиенты 2 467 248,34 $ $ 80 450 596,98
США 2 467 248,34 $ $ 80 450 596,98
Вашингтон 2 467 248,34 $ $ 80 450 596,98
Сиэтл; 75 164,86 $ $ 80 450 596,98

Приведенные выше результаты показывают, что только предки и потомки штата Вашингтон входят в подпространство, по которому вычислялась внешняя инструкция SELECT. Орегон и Портленд были удалены из вложенного куба, поскольку Орегон и все другие штаты, имеющие общего родителя, не были упомянуты в подзапросе выборки, тогда как Вашингтон был.

Элемент «Все» отразил фильтрацию по штату Вашингтон, причем не только в измерении [Customer Geography], но также и в других измерениях, пересекающихся с [Customer Geography]. Все измерения, не пересекающиеся с [Customer Geography], остаются во вложенном кубе без изменений.

Приведенные ниже многомерные выражения иллюстрируют, каким образом элемент «Все» в других измерениях отражает фильтрацию в подзапросе выборки. Первый запрос отображает результаты без изменений, а второй показывает действие фильтра:

SELECT < [Customer].[Customer Geography].[All Customers] , [Customer].[Customer Geography].[Country].&[United States] , [Customer].[Customer Geography].[State-Province].&[OR]&[US] , [Customer].[Customer Geography].[City].&[Portland]&[OR] , [Customer].[Customer Geography].[State-Province].&[WA]&[US] , [Customer].[Customer Geography].[City].&[Seattle]&[WA] >ON 1 , [Product].[Product Line].MEMBERS ON 0 FROM [Adventure Works] WHERE [Measures].[Internet Sales Amount] 
Все продукты Accessory Компоненты Mountain Дорожная Touring
Все клиенты 29 358 677,22 $ 604 053,30 $ (null) 10 251 183,52 $ 14 624 108,58 $ 3 879 331,82 $
США 9 389 789,51 $ 217 168,79 $ (null) 3 547 956,78 $ 4 322 438,41 $ 1 302 225,54 $
Орегон 1 170 991,54 $ 30 513,17 $ (null) 443 607,98 $ 565 372,10 $ 131 498,29 $
Портленд 110 649,54 $ 2 834,17 $ (null) 47 099,91 $ 53 917,17 $ 6 798,29 $
Вашингтон 2 467 248,34 $ 62 662,92 $ (null) 945 219,38 $ 1 155 880,07 $ 303 485,97 $
Сиэтл; 75 164,86 $ 2 695,74 $ (null) 19 914,53 $ 44 820,06 $ 7 734,54 $
SELECT < [Customer].[Customer Geography].[All Customers] , [Customer].[Customer Geography].[Country].&[United States] , [Customer].[Customer Geography].[State-Province].&[OR]&[US] , [Customer].[Customer Geography].[City].&[Portland]&[OR] , [Customer].[Customer Geography].[State-Province].&[WA]&[US] , [Customer].[Customer Geography].[City].&[Seattle]&[WA] >ON 1 , [Product].[Product Line].MEMBERS ON 0 FROM ( SELECT [Customer].[State-Province].&[WA]&[US] ON 0 FROM [Adventure Works] ) WHERE [Measures].[Internet Sales Amount] 
Все продукты Accessory Компоненты Mountain Дорожная Touring
Все клиенты 2 467 248,34 $ 62 662,92 $ (null) 945 219,38 $ 1 155 880,07 $ 303 485,97 $
США 2 467 248,34 $ 62 662,92 $ (null) 945 219,38 $ 1 155 880,07 $ 303 485,97 $
Вашингтон 2 467 248,34 $ 62 662,92 $ (null) 945 219,38 $ 1 155 880,07 $ 303 485,97 $
Сиэтл; 75 164,86 $ 2 695,74 $ (null) 19 914,53 $ 44 820,06 $ 7 734,54 $

В приведенном выше результате видим, что значения All Products скорректированы и отражают только данные по штату Вашингтон, как и предполагалось.

Подзапросы выборки могут быть вложенными, глубина вложенности может быть любой и ограничена только доступной памятью. Самый внутренний подзапрос выборки определяет начальное подпространство, к которому применяется фильтр, передаваемый затем соседней внешней операции выбора. Здесь важно отметить тот факт, что вложение не является коммутативной операцией, поэтому порядок вложенности может влиять на результаты. В приведенном ниже примере показаны различия в зависимости от порядка вложенности.

SELECT [Sales Territory].[Sales Territory Region].MEMBERS on 0 , [Product].[Product].MEMBERS on 1 FROM (SELECT TOPCOUNT( [Product].[Product].CHILDREN, 5, [Measures].[Sales Amount]) ON 0 FROM (SELECT TOPCOUNT( [Sales Territory].[Sales Territory Region].CHILDREN, 5, [Measures].[Sales Amount]) on 0 FROM [Adventure Works] ) ) WHERE [Measures].[Sales Amount] 

Возвращены следующие результаты.

All Sales Territories Австралия Канада Central Northwest Southwest
Все продукты 7 591 495,49 $ 1 281 059,99 $ 1 547 298,12 $ 600 205,79 $ 1 924 763,50 $ 2 238 168,08 $
Mountain-200 Silver, 38 1 449 576,15 $ 248 702,93 $ 275 052,45 $ 141 103,65 $ 349 487,01 $ 435 230,12 $
Mountain-200 Black, 38 1 722 896,50 $ 218 024,05 $ 418 726,43 $ 123 929,46 $ 486 694,63 $ 475 521,93 $
Mountain-200 Black, 42 1 573 655,14 $ 239 137,96 $ 319 921,61 $ 130 102,75 $ 420 445,84 $ 464 046,98 $
Mountain-200 Black, 46 1 420 500,58 $ 192 320,16 $ 230 875,99 $ 117 044,49 $ 424 813,66 $ 455 446,27 $
Road-150 Red, 56 1 424 867,11 $ 382 874,89 $ 302 721,64 $ 88 025,44 $ 243 322,36 $ 407 922,78 $
SELECT [Sales Territory].[Sales Territory Region].MEMBERS on 0 , [Product].[Product].MEMBERS on 1 FROM (SELECT TOPCOUNT( [Sales Territory].[Sales Territory Region].CHILDREN, 5, [Measures].[Sales Amount]) ON 0 FROM (SELECT TOPCOUNT( [Product].[Product].CHILDREN, 5, [Measures].[Sales Amount]) on 0 FROM [Adventure Works] ) ) WHERE [Measures].[Sales Amount] 

Возвращены следующие результаты.

All Sales Territories Австралия Канада Northwest Southwest Соединенное Королевство
Все продукты 7 938 218,56 $ 1 096 312,24 $ 1 474 255,49 $ 2 042 674,72 $ 2 238 099,55 $ 1 086 876,56 $
Mountain-200 Silver, 38 1 520 958,53 $ 248 702,93 $ 275 052,45 $ 349 487,01 $ 435 230,12 $ 212 486,03 $
Mountain-200 Silver, 42 1 392 237,14 $ 198 127,15 $ 229 679,01 $ 361 233,58 $ 407 854,24 $ 195 343,16 $
Mountain-200 Black, 38 1 861 703,23 $ 218 024,05 $ 418 726,43 $ 486 694,63 $ 475 521,93 $ 262 736,19 $
Mountain-200 Black, 42 1 702 427,25 $ 239 137,96 $ 319 921,61 $ 420 445,84 $ 464 046,98 $ 258 874,87 $
Mountain-200 Black, 46 1 460 892,41 $ 192 320,16 $ 230 875,99 $ 424 813,66 $ 455 446,27 $ 157 436,31 $

Очевидно, между двумя наборами результатов есть различия. Первый запрос ответил на вопрос, каковы пять наиболее популярных продуктов в пяти наиболее успешных регионах, а второй запрос — где лучше всего продаются пять наиболее популярных продуктов.

Комментарии

Для подзапросов выборки действуют следующие ограничения.

  • Предложение WHERE не фильтрует подпространство.
  • Предложение WHERE изменяет элемент по умолчанию только во вложенном кубе.
  • Предложение NON EMPTY не допускается в предложении оси; вместо этого используйте выражение функции NonEmpty (многомерные выражения).
  • Предложение HAVING не допускается в предложении оси; вместо этого используйте выражение функции Filter (многомерные выражения).
  • По умолчанию вычисляемые элементы не допускаются во вложенных выборах; Однако это ограничение можно изменить для каждого сеанса, назначив значение свойству SubQueries строка подключения в ConnectionString свойстве или DBPROP_MSMD_SUBQUERIES в разделе Поддерживаемые свойства XMLA (XMLA). Дополнительные сведения о поведении вычисляемых элементов в зависимости от значений свойства SubQueries или DBPROP_MSMD_SUBQUERIES см. в разделе Вычисляемые элементы в подзапросах выборки и вложенных кубах.

Обратная связь

Были ли сведения на этой странице полезными?

Обратная связь

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.

Отправить и просмотреть отзыв по

Как правильно произвести выборку из подзапроса

В выражении SELECT мы можем вводить подзапросы четырьмя способами:

  1. В условии в выражении WHERE
  2. В условии в выражении HAVING
  3. В качестве таблицы для выборки в выражении FROM
  4. В качестве спецификации столбца в выражении SELECT

Рассмотрим некоторые из этих случаев. Например, получим все товары, у которых цена выше средней:

SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products)

Чтобы получить нужные товары, нам вначале надо выполнить подзапрос на получение средней цены товара: SELECT AVG(Price) FROM Products .

Оператор IN

Нередко подзапросы применяются вместе с оператором IN , который выбирает из набора значений. И подзапрос как раз может предоставить требуемый набор значений. Например, выберем все товары из таблицы Products, на которые есть заказы в таблице Orders:

SELECT * FROM Products WHERE Id IN (SELECT ProductId FROM Orders)

То есть подзапрос в данном случае выбирает все идентификаторы товаров из Orders, затем по этим идентификаторам извлекаютя товары из Products.

Добавив оператор NOT , мы можем выбрать те товары, на которые нет заказов в таблице Orders:

SELECT * FROM Products WHERE Id NOT IN (SELECT ProductId FROM Orders)

Стоит отметить, что это не самый эффективный способ для извлечения связанных данных из других таблиц, так как для сведения данных из разных таблиц можно использовать оператор JOIN, который рассматривается в следующей главе.

Получение набора значений

При использовании в операторах сравнения подзапросы должны возвращать одно скалярное значение. Но иногда возникает необходимость получить набор значений. Чтобы при использовании в операторах сравнения подзапрос мог возвращать набор значений, перед ним необходимо использовать один из операторов: ALL , SOME или ANY .

При использовании ключевого слова ALL условие в операции сравнения должно быть верно для всех значений, которые возвращаются подзапросом. Например, найдем все товары, цена которых меньше чем у любого товара фирмы Apple:

SELECT * FROM Products WHERE Price < ALL(SELECT Price FROM Products WHERE Manufacturer='Apple')

Если бы мы в данном случае опустили бы ключевое слово ALL, то мы бы столкнулись с ошибкой.

Допустим, если данный подзапрос возвращает значения vаl1, val2 и val3, то условие фильтрации фактически было бы аналогично объединению этих значений через оператор AND:

WHERE Price < val1 AND Price < val2 AND Price < val3

В тоже время подобный запрос гораздо проще переписать другим образом:

SELECT * FROM Products WHERE Price < (SELECT MIN(Price) FROM Products WHERE Manufacturer='Apple')

Как работает оператор ALL:

  • x > ALL (1, 2) эквивалентно x > 2
  • x < ALL (1, 2) эквивалентно x < 1
  • x = ALL (1, 2) эквивалентно (x = 1) AND (x = 2)
  • x <> ALL (1, 2) эквивалентно x NOT IN (1, 2)

Операторы ANY и SOME условие в операции сравнения должно быть истинным для хотя бы одного из значений, возвращаемых подзапросом. По своему действию оба этих оператора аналогичны, поэтому можно применять любой из них. Например, в следующем случае получим товары, которые стоят меньше самого дорогого товара компании Apple:

SELECT * FROM Products WHERE Price < ANY(SELECT Price FROM Products WHERE Manufacturer='Apple')

И также стоит отметить, что данный запрос можно сделать проще, переписав следующим образом:

SELECT * FROM Products WHERE Price < (SELECT MAX(Price) FROM Products WHERE Manufacturer='Apple')

Как работает оператор ANY (а также SOME):

  • x > ANY (1, 2) эквивалентно x > 1
  • x < ANY (1, 2) эквивалентно x < 2
  • x = ANY (1, 2) эквивалентно x IN (1, 2)
  • x <> ANY (1, 2) эквивалентно (x <> 1) OR (x <> 2)
Подзапрос как спецификация столбца

Результат подзапроса может представлять отдельный столбец в выборке. Например, выберем все заказы и добавим к ним информацию о названии товара:

SELECT *, (SELECT ProductName FROM Products WHERE AS Product FROM Orders

Подзапросы в SELECT в MySQL

Подзапросы в команде INSERT

В команде INSERT подзапросы могут применяться для определения значения, которое вставляется в один из столбцов:

INSERT INTO Orders (ProductId, CreatedAt, ProductCount, Price) VALUES ( (SELECT Id FROM Products WHERE ProductName='Galaxy S8'), '2018-05-23', 2, (SELECT Price FROM Products WHERE ProductName='Galaxy S8') )

Подзапросы в команде UPDATE

В команде UPDATE подзапросы могут применяться:

  1. В качестве устанавливаемого значения после оператора SET
  2. Как часть условия в выражении WHERE

Так, увеличим в таблице Orders количество купленных товаров компании Apple на 2:

UPDATE Orders SET ProductCount = ProductCount + 2 WHERE ProductId IN (SELECT Id FROM Products WHERE Manufacturer='Apple');

Или установим для заказа цену товара, полученную в результате подзапроса:

UPDATE Orders SET Price = (SELECT Price FROM Products WHERE + 3000 WHERE в команде DELETE

В команде DELETE подзапросы также применяются как часть условия. Так, удалим все заказы на Galaxy S8:

DELETE FROM Orders WHERE ProductId=(SELECT Id FROM Products WHERE ProductName='Galaxy S8');

Глава 10. ВСТАВКА ОДНОГО ЗАПРОСА ВНУТРЬ ДРУГОГО

В конце Главы 9 мы говорили, что запросы могут управлять другими запросами. В этой главе вы узнаете, как это делается (большей частью) путём помещения запроса внутрь предиката другого запроса и использования вывода внутреннего запроса в верном или неверном условии предиката.

Вы сможете выяснить, какие виды операторов могут использовать подзапросы, и посмотреть, как подзапросы работают со средствами SQL, такими как DISTINCT, с составными функциями и выводимыми выражениями.

Вы узнаете, как использовать подзапросы с предложением HAVING, и получите некоторые наставления, как правильно использовать подзапросы.

К АК РАБОТАЕТ ПОДЗАПРОС?

С помощью SQL вы можете вкладывать запросы друга в друга. Обычно внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса, определяющего, верно оно или нет. Например, предположим, что мы знаем имя продавца: Motika, но не знаем значение его поля snum и хотим извлечь все заказы из таблицы Заказов. Вот способ сделать это (вывод показан на Рис. 10.1 ):

SELECT * FROM Orders WHERE snum = (SELECT snum FROM Salespeople WHERE sname = 'Motika');

Чтобы оценить внешний (основной) запрос, SQL сначала должен оценить внутренний запрос (или подзапрос) внутри предложения WHERE. Он делает это так, как и должен делать запрос, имеющий единственную цель - отыскать через таблицу Продавцов все строки, где поле sname равно значению Motika, а затем извлечь значения поля snum этих строк.

Единственной найденной строкой, естественно, будет snum = 1004. Однако SQL не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предикат прочитал, что

WHERE snum = 1004 =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE snum = | | (SELECT snum | | FROM Salespeople | | WHERE sname = 'Motika'); | |=================================================| | onum amt odate cnum snum | | ----- ------- ---------- ----- ----- | | 3002 1900.10 10/03/1990 2007 1004 | | | =================================================
Рисунок 10.1 Использование подзапроса

Основной запрос затем выполняется как обычно с вышеупомянутыми результатами. Разумеется, подзапрос должен выбрать один, и только один, столбец, а тип данных этого столбца должен совпадать с тем значением, с которым он будет сравниваться в предикате.
Часто, как показано выше, выбранное поле и его значение будут иметь одинаковые имена (в данном случае snum), но это не обязательно. Конечно, если бы мы уже знали номер продавца Motika, мы могли бы просто напечатать WHERE snum = 1004 и работать далее с подзапросом в целом, но это было бы не так универсально. Этот же запрос будет продолжать работать, даже если номер Motika изменился, а с помощью простого изменения имени в подзапросе вы можете использовать его для чего угодно.

З НАЧЕНИЯ, КОТОРЫЕ ПОДЗАПРОС МОЖЕТ ВЫВОДИТЬ

Скорее всего, было бы удобнее, чтобы наш подзапрос в предыдущем примере возвращал одно, и только одно, значение.

Имея выбранное поле snum " WHERE city = "London" вместо "WHERE sname = 'Motika", можно получить несколько различных значений. Это может сделать в предикате основного запроса невозможным оценку верности или неверности, и команда выдаст ошибку.

При использовании подзапросов в предикатах, основанных на реляционных операциях (уравнениях или неравенствах, как объяснено в Главе 4 ), вы должны убедиться, что использовали подзапрос, который будет выдавать одну, и только одну, строку вывода. Если вы используете подзапрос, который не выводит никаких значений вообще, команда не потерпит неудачи, но основной запрос не выведет никаких значений. Подзапросы, которые не производят никакого вывода (или нулевой вывод), вынуждают рассматривать предикат ни как верный, ни как неверный, а как неизвестный. Однако неизвестный предикат имеет тот же самый эффект, что и неверный: никакие строки не выбираются основным запросом (смотри в Главе 5 подробную информацию о неизвестном предикате).

Вот пример плохой стратегии:

SELECT * FROM Orders WHERE snum = (SELECT snum FROM Salespeople WHERE city = Barcelona);

Поскольку мы имеем только одного продавца в Barcelona - Rifkin, то подзапрос будет выбирать одиночное значение snum, и, следовательно, будет принят. Но это только в данном случае. Большинство БД SQL имеют многочисленных пользователей, и, если другой пользователь добавит нового продавца из Barcelona в таблицу, подзапрос выберет два значения, и ваша команда потерпит неудачу.

D ISTINCT С ПОДЗАПРОСАМИ

В некоторых случаях вы можете использовать DISTINCT чтобы вынудить подзапрос генерировать одиночное значение. Предположим что мы хотим найти все порядки кредитования для тех продавцов, которые обслуживают Hoffman'а (cnum = 2001).

Вот способ сделать это (вывод показан на Рисунке 10.2):

SELECT * FROM Orders WHERE snum = (SELECT DISTINCT snum FROM Orders WHERE cnum = 2001); =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE snum = | | (SELECT DISTINCT snum | | FROM Orders | | Where cnum = 2001); | | =============================================== | | onum amt odate cnum snum | | ----- --------- --------- ------ ------- | | 3003 767.19 10/03/1990 2001 1001 | | 3008 4723.00 10/05/1990 2006 1001 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================ Рисунок 10.2 Использование DISTINCT для получения одного значения из подзапроса

Подзапрос установил, что значение поля snum совпало с Hoffman - 1001, а затем основной запрос выделил все заказы с этим значением snum из таблицы Заказов (не разбирая, относятся они к Hoffman или нет). Так как каждый заказчик назначен продавцу, мы знаем, что каждая строка в таблице Заказов с данным значением cnum должна иметь такое же значение snum. Однако, поскольку там может быть любое число таких строк, подзапрос мог бы вывести много (хотя и идентичных) значений snum для данного поля cnum. Аргумент DISTINCT предотвращает это. Если наш подзапрос возвратит более одного значения, это будет указывать на ошибку в наших данных - хорошая вещь для знающих об этом.
Должен быть и альтернативный подход, чтобы ссылаться к таблице Заказчиков, а не к таблице Заказов в подзапросе. Так как поле cnum это первичный ключ таблицы Заказчиков, запрос, выбирающий его, должен выдать только одно значение. Это рационально, только если вы как пользователь имеете доступ к таблице Заказов, но не к таблице Заказчиков. В этом случае вы можете использовать решение, которое мы показали выше. (SQL имеет механизмы, которые определяют, кто имеет привилегии на выполнение действий в определённой таблице. Это будет объясняться в Главе 22 .)

Пожалуйста, учтите, что методика, используемая в предшествующем примере, применима, только когда вы знаете, что два различных поля в таблице должны всегда совпадать, как в нашем случае. Эта ситуация не является типичной в реляционных базах данных (РБД), она является исключением из правил.

П РЕДИКАТЫ С ПОДЗАПРОСАМИ ЯВЛЯЮТСЯ НЕОБРАТИМЫМИ

Вы должны обратить внимание что предикаты, включающие подзапросы, используют выражение

Другими словами, вы не должны записывать предыдущий пример так:

SELECT * FROM Orders WHERE (SELECT DISTINCT snum FROM Orders WHERE cnum = 2001) = snum;

В строгой ANSI-реализации это приведет к неудаче, хотя некоторые программы и позволяют делать такие вещи. ANSI также предохраняет от появления в выводе подзапроса обоих значений при сравнении.

И СПОЛЬЗОВАНИЕ АГРЕГАТНЫХ ФУНКЦИЙ В ПОДЗАПРОСАХ

Тип функций, который автоматически может производить одиночное значение для любого числа строк, конечно же - агрегатная функция.

Любой запрос, использующий одиночную функцию агрегата без предложения GROUP BY, будет выбирать одиночное значение для использования в основном предикате. Например, вы хотите увидеть все заказы, имеющие сумму выше средней на 4-е октября (вывод показан на Рисунке 10.3):

SELECT * FROM Orders WHERE amt > (SELECT AVG (amt) FROM Orders WHERE odate = 10/04/1990); =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE amt > | | (SELECT AVG (amt) | | FROM Orders | | WHERE odate = 01/04/1990); | | =============================================== | | onum amt odate cnum snum | | ----- -------- ---------- ----- ----- | | 3002 1900.10 10/03/1990 2007 1004 | | 3005 2345.45 10/03/1990 2003 1002 | | 3006 1098.19 10/03/1990 2008 1007 | | 3009 1713.23 10/04/1990 2002 1003 | | 3008 4723.00 10/05/1990 2006 1001 | | 3010 1309.95 10/06/1990 2004 1002 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================ Рисунок 10.3 Выбор всех сумм со значением выше среднего на 10/04/1990

Средняя сумма приобретений на 4 октября - 1788.98 (1713.23 + 75.75) делится пополам, что в целом равняется 894.49. Все строки со значением в поле amt выше этого являются выбранными. Имейте в виду, что сгруппированные агрегатные функции, которые являются агрегатными функциями, определёнными в терминах предложения GROUP BY, могут производить многочисленные значения. Они, следовательно, недопустимы в подзапросах такого характера. Даже если GROUP BY и HAVING используются таким способом, что только одна группа выводится с помощью подзапроса, команда будет отклонена в принципе. Вы должны использовать одиночную агрегатную функцию с предложением WHERE, что устранит нежелательные группы.

Например, следующий запрос который должен найти среднее значение комиссионных продавца в Лондоне,

SELECT AVG (comm) FROM Salespeople GROUP BY city HAVlNG city = "London";

не может использоваться в подзапросе! Во всяком случае, это не лучший способ формировать запрос.

Другим способом может быть

SELECT AVG (comm) FROM Salespeople WHERE city = "London";

И СПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ, КОТОРЫЕ ВЫДАЮТ МНОГО СТРОК С ПОМОЩЬЮ ОПЕРАТОРА IN

Вы можете использовать подзапросы, которые производят любое число строк, если вы применяете специальный оператор IN (операторы BETWEEN, LIKE и IS NULL не могут использоваться с подзапросами). Как вы помните, IN определяет набор значений, одно из которых должно совпадать с другим термином уравнения предиката в заказе, чтобы предикат был верным.
Когда вы используете IN с подзапросом, SQL просто формирует этот набор из вывода подзапроса. Мы можем, следовательно, использовать IN чтобы выполнить такой подзапрос, который не будет работать с реляционным оператором, и найти все атрибуты таблицы Заказов для продавца в Лондоне (вывод показан на Рисунке 10.4):

SELECT * FROM Orders WHERE snum IN (SELECT snum FROM Salespeople WHERE city = "LONDON"); =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE snum IN | | (SELECT snum | | FROM Salespeople | | WHERE city = 'London'); | | =============================================== | | onum amt odate cnum snum | | ----- -------- ---------- ----- ------ | | 3003 767.19 10/03/1990 2001 1001 | | 3002 1900.10 10/03/1990 2007 1004 | | 3006 1098.19 10/03/1990 2008 1007 | | 3008 4723.00 10/05/1990 2006 1001 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================ Рисунок 10.4 Использование подзапроса с IN

В ситуации, подобной этой, подзапрос проще для понимания пользователем и проще для выполнения компьютером, чем если бы вы использовали объединение:

SELECT onum, amt, odate, cnum, Orders.snum FROM Orders, Salespeople WHERE Orders.snum = Salespeople.snum AND Salespeople.city = "London";

Хотя это и произведёт тот же самый вывод, что в примере с подзапросом, SQL должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и проверить их снова по составному предикату. Проще и эффективнее извлекать из таблицы Продавцов значения поля snum, где city = "London", а затем искать эти значения в таблице Заказов, как это делается в варианте с подзапросом. Внутренний запрос даёт нам snums=1001 и snum=1004. Внешний запрос затем даёт нам строки из таблицы Заказов, где эти поля snum найдены.
Строго говоря, то, быстрее или нет работает вариант подзапроса, практически зависит от реализации - в какой программе вы это используете. Часть вашей программы, называемая оптимизатор, пытается найти наиболее эффективный способ выполнения ваших запросов. Хороший оптимизатор в любом случае преобразует вариант объединения в подзапрос, но нет достаточно простого способа, чтобы выяснить, выполнено это или нет. Лучше сохранить ваши запросы в памяти, нежели полагаться полностью на оптимизатор.
Конечно, вы можете также использовать оператор IN, даже когда вы уверены, что подзапрос произведет одиночное значение. В любой ситуации, где вы можете использовать реляционный оператор сравнения (=), вы можете использовать IN. В отличие от реляционных операторов, IN не может заставить команду потерпеть неудачу, если больше чем одно значение выбрано подзапросом. Это может быть или преимуществом или недостатком. Вы не увидите непосредственно вывода из подзапросов, если вы полагаете, что подзапрос собирается произвести только одно значение, а он производит несколько. Вы не сможете объяснить различия в выводе основного запроса. Например, рассмотрим команду, которая похожа на предыдущую:

SELECT onum, amt, odate FROM Orders WHERE snum = (SELECT snum FROM Orders WHERE cnum = 2001);

Вы можете устранить потребность в DISTINCT, используя IN вместо (=):

SELECT onum, amt, odate FROM Orders WHERE snum IN (SELECT snum FROM Orders WHERE cnum = 2001);

Что случится, если есть ошибка и один из заказов был аккредитован различным продавцам? Версия, использующая IN, будет выдавать вам все заказы для обоих продавцов. Нет никакого очевидного способа наблюдения за ошибкой, и поэтому сгенерированные отчеты или решения, сделанные на основе этого запроса, не будут содержать ошибки. Вариант, использующий (=), просто потерпит неудачу. Это, по крайней мере, позволило вам узнать, что имеется такая проблема. Вы должны затем выполнять поиск неисправности, выполнив этот подзапрос отдельно и наблюдая значения, которые он производит. В принципе, если вы знаете, что подзапрос должен (по логике) вывести только одно значение, вы должны использовать =.
IN является подходящим, если запрос может ограниченно производить одно или более значений, независимо от того, ожидаете вы их или нет. Предположим, мы хотим знать комиссионные всех продавцов, обслуживающих заказчиков в Лондоне:

SELECT comm FROM Salespeople WHERE snum IN (SELECT snum FROM Customers WHERE city = "London");

Выводимыми для этого запроса, показанного в Рисунке 10.5, являются значения комиссионных продавца Peel (snum = 1001), который имеет обоих заказчиков в Лондоне. Но это только для данного случая. Нет никакой причины, чтобы некоторые заказчики в Лондоне не могли быть назначены кому-то ещё. Следовательно, IN - это наиболее логичная форма для использования в запросе.

=============== SQL Execution Log ============== | | | SELECT comm | | FROM Salespeople | | WHERE snum IN | | (SELECT snum | | FROM Customers | | WHERE city = 'London'); | | =============================================== | | comm | | ------- | | 0.12 | | | | | ================================================ Рисунок 10.5 Использование IN с подзапросом для вывода одного значения

Между прочим, префикс таблицы для поля city в предыдущем примере не обязателен, несмотря на возможную неоднозначность между полями city таблицы Заказчика и таблицы Продавцов. SQL всегда ищет первое поле в таблице, обозначенной в предложении FROM текущего подзапроса. Если поле с данным именем там не найдено, проверяются внешние запросы. В вышеупомянутом примере, "city" в предложении WHERE означает, что имеется ссылка на Customer.city (поле city таблицы Заказчиков). Так как таблица Заказчиков указана в предложении FROM текущего запроса, SQL предполагает что это правильно. Это предположение может быть отменено полным именем таблицы или префиксом псевдонима, о которых мы поговорим позже, когда будем говорить о соотнесенных подзапросах. Если возможен беспорядок, конечно же, лучше всего использовать префиксы.

ПОДЗАПРОСЫ ВЫБИРАЮТ ОДИНОЧНЫЕ СТОЛБЦЫ

Смысл всех подзапросов, обсуждённых в этой главе, в том, что все они выбирают одиночный столбец. Это обязательно, поскольку полученный вывод сравнивается с одиночным значением. Подтверждением этому является то, что SELECT * не может использоваться в подзапросе. Имеется исключение из этого, когда подзапросы используются с оператором EXISTS, о котором мы будем говорить в Главе 12 .

ИСПОЛЬЗОВАНИЕ ВЫРАЖЕНИЙ В ПОДЗАПРОСАХ

Вы можете использовать выражение, основанное на столбце, а не просто сам столбец, в предложении SELECT подзапроса. Это может быть выполнено или с помощью реляционных операторов, или с IN. Например, следующий запрос использует реляционный оператор = (вывод показан на Рисунке 10.6):

SELECT * FROM Customers WHERE cnum = (SELECT snum + 1000 FROM Salespeople WHERE sname = Serres);

Он находит всех заказчиков, чьё значение поля cnum, равное 1000, выше поля snum Serres. Мы предполагаем, что столбец sname не имеет никаких двойных значений (это может быть предписано или UNIQUE INDEX, обсуждаемым в Главе 17 , или ограничением UNIQUE, обсуждаемым в Главе 18 ); иначе

=============== SQL Execution Log ============ | | | SELECT * | | FROM Customers | | WHERE cnum = | | (SELECT snum + 1000 | | WHERE Salespeople | | WHERE sname = 'Serres' | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ----- | | 2002 Giovanni Rome 200 1003 | ============================================= Рисунок 10.6 Использование подзапроса с выражением

подзапрос может произвести несколько значений. Когда поля snum и сnum не имеют такого простого функционального значения как, например, первичный ключ, что не всегда хорошо, запрос типа вышеупомянутого невероятно полезен.

П ОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVING

Вы можете также использовать подзапросы внутри предложения HAVING. Эти подзапросы могут использовать свои собственные агрегатные функции, если они не производят нескольких значений, или использовать GROUP BY или HAVING. Следующий запрос является примером этого (вывод показан на Рисунке 10.7):

SELECT rating, COUNT (DISTINCT cnum) FROM Customers GROUP BY rating HAVING rating > (SELECT AVG (rating) FROM Customers WHERE city = " San Jose'); =============== SQL Execution Log ============= | | | SELECT rating,count (DISTINCT cnum) | | FROM Customers | | GROUP BY rating | | HAVING rating > | | (SELECT AVG (rating)snum + 1000 | | FROM Custimers | | WHERE city = 'San Jose'); | |================================================ | | rating | | -------- -------- | | 200 2 | ================================================
Рисунок 10.7 Поиск в San Jose заказчиков с оценкой выше среднего

Эта команда подсчитывает заказчиков в San Jose с рейтингами выше среднего. Так как имеются другие оценки, отличные от 300, они должны быть выведены с числом номеров заказчиков, которые имели эту оценку.

Р ЕЗЮМЕ

Теперь вы используете запросы в иерархической манере. Вы видели, что использование результата одного запроса для управления другим расширяет возможности, позволяя выполнить большее количество функций.

Вы теперь понимаете, как использовать подзапросы с реляционными операциями и со специальным оператором IN, или в предложении WHERE, или в предложении HAVING внешнего запроса.

В следующих главах мы будем рассматривать подзапросы. Сначала, в Главе 11 , мы обсудим другой вид подзапроса, который выполняется отдельно для каждой строки таблицы, вызываемой во внешнем запросе. Затем, в Главах 12 и 13 , мы представим вам несколько специальных операторов, которые функционируют на всех подзапросах, как это делает IN, за исключением случаев, когда эти операторы могут использоваться только в подзапросах.

Как правильно произвести выборку из подзапроса

Подзапросы являются одним из самых мощных инструментов �� в SQL, который можно использовать в любых видах запросов. В ближайших уроках мы познакомимся с основными типами подзапросов и рассмотрим примеры как их можно использовать.

Подзапрос — это запрос, использующийся в другом SQL запросе. Подзапрос всегда заключён в круглые скобки и обычно выполняется перед основным запросом.

Как и любой другой SQL запрос, подзапрос возвращает результирующий набор, который может быть одним из следующих:

  • одна строка и один столбец;
  • нескольких строк с одним столбцом;
  • нескольких строк с несколькими столбцами.

В зависимости от типа результирующего набора подзапроса определяются операторы, которые могут использоваться в основном запросе.

Получим список всех бронирований самого дорогого на данный момент жилого помещения:

MySQL
SELECT * FROM Reservations WHERE Reservations.room_id = ( SELECT id FROM Rooms ORDER BY price DESC LIMIT 1 ) 

В данном случае запрос на получение самого дорогого жилого помещения выполняется в качестве подзапроса, а затем результат результирующего набора применяется в основном запросе.


Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *