практическая. Вложенные запросы sql (Microsoft Access sql)
 Скачать 52.98 Kb.
|
|
SELECT Count(T.AccountName) AS CountOfAccountName FROM (SELECT AccountName. select count(*) from (select distinct snum from tabl) t1 Вложенные запросы SQL (Microsoft Access SQL) 18.09.2015 Чтение занимает 2 мин  Область применения: Access 2013, Office 2013 Вложенный запрос - это оператор SELECT, вложенный в оператор SELECT…INTO, INSERT…INTO, DELETE или UPDATE или в другой вложенный запрос. Синтаксис Вы можете использовать три формы синтаксиса для создания вложенного запроса: сравнение [ANY | ALL | SOME ] (sqlstatement) выражение [NOT] IN (sqlstatement) [NOT] EXISTS (sqlstatement) Вложенный запрос состоит из следующих частей:
Примечания Вы можете использовать вложенный запрос вместо выражения в списке полей оператора SELECT или предложении WHERE или HAVING. Во вложенном запросе вы используете оператор SELECT для предоставления набора одного или нескольких определенных значений для оценки в выражении для предложения WHERE или HAVING. Используйте предикат ANY или SOME, которые являются синонимами, для получения записей в основном запросе, который удовлетворяет сравнению с любыми записями, полученными во вложенном запросе. Следующий пример возвращает все продукты, для которых цена за единицу выше, чем у любого продукта, продаваемого со скидкой 25 процентов или более: SQLКопировать SELECT * FROM Products WHERE UnitPrice > ANY (SELECT UnitPrice FROM OrderDetails WHERE Discount >= .25); Используйте предикат ALL для получения записей в основном запросе, который удовлетворяет сравнению со всеми записями, полученными во вложенном запросе. Если вы изменили предикат с ANY на ALL в предыдущем примере, запрос будет возвращать только те продукты, у которых цена за единицу больше, чем у всех продуктов, проданных со скидкой 25 процентов или более. Это гораздо более строгое ограничение. Используйте предикат IN для получения только тех записей в основном запросе, для которых определенная запись во вложенном запросе содержит одинаковое значение. В примере ниже возвращаются все продукты со скидкой 25 процентов или больше: SQLКопировать SELECT * FROM Products WHERE ProductID IN (SELECT ProductID FROM OrderDetails WHERE Discount >= .25); С другой стороны, вы можете использовать NOT IN для получения только тех записей в основном запросе, для которых ни одна запись во вложенном запросе не содержит одинаковое значение. Используйте предикат EXISTS (с необязательным зарезервированным словом NOT) в сравнениях ИСТИНА/ЛОЖЬ, чтобы определить, возвращает ли вложенный запрос какие-либо записи. Также можно использовать псевдонимы имени таблицы во вложенном запросе для ссылки на таблицы, указанные в предложении FROM за пределами вложенного запроса. Пример ниже возвращает имена сотрудников, чья заработная плата равна или выше средней заработной платы всех сотрудников на аналогичной должности. Для таблицы "Сотрудники" присваивается псевдоним "T1": SQLКопировать SELECT LastName, FirstName, Title, Salary FROM Employees AS T1 WHERE Salary >= (SELECT Avg(Salary) FROM Employees WHERE T1.Title = Employees.Title) Order by Title; В приведенном выше примере зарезервированное слово AS не является обязательным. Некоторые вложенные запросы поддерживаются в перекрестных запросах, в частности, в качестве предикатов (например в предложении WHERE). Вложенные запросы в виде выходных данных (в списке SELECT) не поддерживаются в перекрестных запросах. Пример В данном примере перечислены имена и контактные данные каждого клиента, разместившего заказ во втором квартале 1995 года. В этом примере выполняется вызов процедуры EnumFields, которую можно найти в примере для оператора SELECT. VBКопировать Sub SubQueryX() Dim dbs As Database, rst As Recordset ' Modify this line to include the path to Northwind ' on your computer. Set dbs = OpenDatabase("Northwind.mdb") ' List the name and contact of every customer ' who placed an order in the second quarter of ' 1995. Set rst = dbs.OpenRecordset("SELECT ContactName," _ & " CompanyName, ContactTitle, Phone" _ & " FROM Customers" _ & " WHERE CustomerID" _ & " IN (SELECT CustomerID FROM Orders" _ & " WHERE OrderDate Between #04/1/95#" _ & " And #07/1/95#);") ' Populate the Recordset. rst.MoveLast ' Call EnumFields to print the contents of the ' Recordset. Pass the Recordset object and desired ' field width. EnumFields rst, 25 dbs.Close End Sub Подзапросы в MS Access Стандартом SQL предусматривается возможность, вкладывать запросы друг в друга, что имеет большое практическое применение. Еще раз - одни запросы могут управлять другими. Чтобы разобрать все тонкости данного понятия, предлагаю, вначале, рассмотреть практический пример использования подзапроса, а затем перейти к его теоретическому обсуждению. К примеру, нас интересуют книги издательства BHV. Для это, как вариант одного из решений, мы можем использовать подзапрос. SELECT * FROM Books Where Books.ID_PRESS= (SELECT ID FROM Press WHERE Name='BHV'); Чтобы выполнить основной (он же внешний) запрос, SQL прежде всего должен выполнить подзапрос (он же внутренний запрос) в предложении WHERE. Таким образом, вначале выполняется подзапрос, как если бы он был единтсвенным запросом. Другими словами, просматриваются все записи таблицы Press и выбираются все записи, для которых значение поля Name='BHV'. Полученный результат подставляется в основной запрос и выполняется основной запрос. В конечном результате, оказывается выбранными записи, содержащие информацию только об издательстве BHV. Теперь поговорим об ограничениях, которое накладывает использование подзапросов... Подзапрос должен выбирать одно и только одно поле. Более того, тип данных этого поля должен соответсвовать типу значения, используемому в основном запросе. К примеру, запрос представленный ниже, должен привести к ошибке, так как в нем на выходе подзапрос дает несколько значений: SELECT * FROM Books Where Books.ID_PRESS= (SELECT ID FROM Press WHERE Name='BHV' OR Name='Бином');  Почему ошибка? Выходные данные содержат более одной строки (возвращается ID для издательства BHV и Бином ). Таким образом, используя подзапросы, основанные на операторах отношения, необходимо быть уверенным, что конечными выходными данными подзапроса является только одна строка. Отлично, с перебором (то есть когда на выходе из подзапроса оказывается несколько значений) разабрались. А что если подзапрос вообще не вернет никаких данных, так называемый NULL-выход? Это приведет к тому, что подзапрос будет оценен не как TRUE или FALSE, а как имеющий значение UNKNOWN. Результат UNKNOWN аналогичен результату FALSE - основной запрос не выберет ни одной строки, т.е. тоже приведет к NULL-выходу. Например, среди издательств представленных в нашей БД издательство Agatone отсутсвует, таким образом рассмотренный выше вариант с NULL-выходом сработает и в данном случае. SELECT * FROM Books WHERE Books.ID_PRESS= (SELECT ID FROM Press WHERE Name='Agatone'); Естественно, возникает вопрос: " а сработает ли следующий подзапрос?" SELECT * FROM Books WHERE Books.ID_PRESS= (SELECT ID FROM Press WHERE Name='Agatone' or Name='BHV'); Да сработает, так как подзапрос на выходе вернет одну строку. В некоторых случаях, возможно использовать DISTINCT для гарантии получения единственного значения на выходе подзапроса. То есть, если Вы не уверены в том, что подзапрос вернет только одну строку, Вы можете подстраховаться, используя DISTINCT с подзапросами. Следующее ограничение использования подзапросов, это некоммутативность или, более доступными словами, неперемещаемость. В соответсвии с соглашениями ANSI это обозначает, что рассмотренный ранее запрос SELECT * FROM Books Where Books.ID_PRESS= (SELECT ID FROM Press WHERE Name='BHV'); нельзя перписать в виде SELECT * FROM Books Where (SELECT ID FROM Press WHERE Name='BHV') = Books.ID_PRESS; Однако наш "визуальнейший их все СУБД" играет по своим правилам. На приведенный последний запрос, Access сгенерирует точно таки же выходные данные как и на предпоследний (правильный) запрос. Немного о взаимосваязи агрегатных функциий с подзапросами. Учитывая тот факт, что агрегатные функции на выходе ывдают единственно значение для любого колличесвта строк, то, логчно сделать вывод, что любой подзапрос, использующий единственную агрегатную функцию без предложения GROUP BY, дает в результате единственное значение для использования его в основном запросе. Вспомним прошлое занятие... А именно, оператор IN. Почему вспомним именно о нем? Дело в том, что благодаря оператору IN возможно сформулировать подзапросы, в результате выполнения которых получается любое колличесво строк. Сразу оговорюсь, что операторы BETWEEN, LIKE, IS NULL к подзапросам применять нельзя. Пример, выбрать имена и фамилии всех студентов, которые брали книги в промежутке между 1 Января 2001 года и текущей датой: SELECT FirstName & " " & LastName AS [Имя и Фамилия] FROM Students WHERE first.Id In ( SELECT ID_STUDENT FROM S_Cards WHERE DateIn >#1-1-2001# AND DateIn ); Рассмотрим реализацию следующего запроса: "найти всех студентов, кто на данный момент работает с книгой "Реестр Windows 2000" автора Ольга Кокорева". SELECT * FROM Students WHERE Students.Id IN (SELECT ID_Student FROM S_Cards WHERE ID_BOOK= (SELECT ID FROM Books WHERE Name='Реестр Windows 2000' AND ID_Author= (SELECT ID FROM Authors WHERE FirstNAme='Ольга' AND LastName='Кокорева' ))); Еще один пример, узнаем информации об авторах, средний объем книг которых (в страницах) более 600 страниц. SELECT * FROM Authors WHERE ID IN ( SELECT IDA FROM ( SELECT ID_Author AS IDA, AVG(Pages) FROM Books GROUP BY ID_Author HAVING AVG(Pages)>600) ); Анализ данного запроса, естественно, начинается из внутреннего подзапроса. Вначале, выбираются все записи удовлетворяющие самому внутреннему подзапросу, выбираются записи сгруппированные по идентификатору автора. Из полученного множества строк, выбрасываются все строки не удовлетворяющиее условию AVG(Pages)>600. Полученный результат, подставляется в предложение FROM, первого подзапроса, откуда выбираются значения поля ID_Author (в нашем случае используется синоним IDA для столбца ID_Author). В результате выполнения данного подзапроса на выходе получаем опять-таки несколько значений. Но благодаря оператору IN, наш подзапрос выполняется корректно, так как, еще раз напомню, оператор IN позволяет работать со множеством значений. Таким образом, как мы убедились на практике, подзапросы могут быть использованы нетолько в предложении WHERE, но и в предложении FROM. По аналогии с предложением WHERE подзапрос может быть использован в предложении HAVING. Отдельная разновидность подзапросов выделяется в группу связанных подзапросов. Выясним, что это такое. Когда в SQL используются подзапросы, во внутреннем (вложенном) запросе можно ссылаться на таблицу, имя которой указано в предложениее FROM внешнего запроса. Именно таким образом формируется связанный подзапрос. В этом варианте использования подзапросов, подзапрос выполняется повторно, точнее по одному разу для каждой строки таблицы из основного запроса. Например, узнаем всю информацию об издательствах, у которых общее колличество страниц выпущенных ними книг больше 700: SELECT * FROM Press first WHERE 700<( SELECT SUM (Pages) FROM Books second WHERE first.ID=second.ID_Press ) ORDER BY 2; К всеобщему счастью тема поздапросов объемная. По этой причине мы еще не раз затроним тему подзапрсов в последующих занятиях. Вложенные запросы SQL (Microsoft Access SQL) 18.09.2015 Чтение занимает 2 мин Область применения: Access 2013, Office 2013 Вложенный запрос - это оператор SELECT, вложенный в оператор SELECT…INTO, INSERT…INTO, DELETE или UPDATE или в другой вложенный запрос. Синтаксис Вы можете использовать три формы синтаксиса для создания вложенного запроса: сравнение [ANY | ALL | SOME ] (sqlstatement) выражение [NOT] IN (sqlstatement) [NOT] EXISTS (sqlstatement) Вложенный запрос состоит из следующих частей:
Примечания Вы можете использовать вложенный запрос вместо выражения в списке полей оператора SELECT или предложении WHERE или HAVING. Во вложенном запросе вы используете оператор SELECT для предоставления набора одного или нескольких определенных значений для оценки в выражении для предложения WHERE или HAVING. Используйте предикат ANY или SOME, которые являются синонимами, для получения записей в основном запросе, который удовлетворяет сравнению с любыми записями, полученными во вложенном запросе. Следующий пример возвращает все продукты, для которых цена за единицу выше, чем у любого продукта, продаваемого со скидкой 25 процентов или более: SQLКопировать SELECT * FROM Products WHERE UnitPrice > ANY (SELECT UnitPrice FROM OrderDetails WHERE Discount >= .25); Используйте предикат ALL для получения записей в основном запросе, который удовлетворяет сравнению со всеми записями, полученными во вложенном запросе. Если вы изменили предикат с ANY на ALL в предыдущем примере, запрос будет возвращать только те продукты, у которых цена за единицу больше, чем у всех продуктов, проданных со скидкой 25 процентов или более. Это гораздо более строгое ограничение. Используйте предикат IN для получения только тех записей в основном запросе, для которых определенная запись во вложенном запросе содержит одинаковое значение. В примере ниже возвращаются все продукты со скидкой 25 процентов или больше: SQLКопировать SELECT * FROM Products WHERE ProductID IN (SELECT ProductID FROM OrderDetails WHERE Discount >= .25); С другой стороны, вы можете использовать NOT IN для получения только тех записей в основном запросе, для которых ни одна запись во вложенном запросе не содержит одинаковое значение. Используйте предикат EXISTS (с необязательным зарезервированным словом NOT) в сравнениях ИСТИНА/ЛОЖЬ, чтобы определить, возвращает ли вложенный запрос какие-либо записи. Также можно использовать псевдонимы имени таблицы во вложенном запросе для ссылки на таблицы, указанные в предложении FROM за пределами вложенного запроса. Пример ниже возвращает имена сотрудников, чья заработная плата равна или выше средней заработной платы всех сотрудников на аналогичной должности. Для таблицы "Сотрудники" присваивается псевдоним "T1": SQLКопировать SELECT LastName, FirstName, Title, Salary FROM Employees AS T1 WHERE Salary >= (SELECT Avg(Salary) FROM Employees WHERE T1.Title = Employees.Title) Order by Title; В приведенном выше примере зарезервированное слово AS не является обязательным. Некоторые вложенные запросы поддерживаются в перекрестных запросах, в частности, в качестве предикатов (например в предложении WHERE). Вложенные запросы в виде выходных данных (в списке SELECT) не поддерживаются в перекрестных запросах. Пример В данном примере перечислены имена и контактные данные каждого клиента, разместившего заказ во втором квартале 1995 года. В этом примере выполняется вызов процедуры EnumFields, которую можно найти в примере для оператора SELECT. VBКопировать Sub SubQueryX() Dim dbs As Database, rst As Recordset ' Modify this line to include the path to Northwind ' on your computer. Set dbs = OpenDatabase("Northwind.mdb") ' List the name and contact of every customer ' who placed an order in the second quarter of ' 1995. Set rst = dbs.OpenRecordset("SELECT ContactName," _ & " CompanyName, ContactTitle, Phone" _ & " FROM Customers" _ & " WHERE CustomerID" _ & " IN (SELECT CustomerID FROM Orders" _ & " WHERE OrderDate Between #04/1/95#" _ & " And #07/1/95#);") ' Populate the Recordset. rst.MoveLast ' Call EnumFields to print the contents of the ' Recordset. Pass the Recordset object and desired ' field width. EnumFields rst, 25 dbs.Close End Sub | ||||||||||||||||||||