Този урок се фокусира върху SQL заявкикъм базата данни на VBA достъп... Ще разгледаме как се правят заявките INSERT, UPDATE, DELETE към базата данни във VBA и също така ще научим как да получим конкретна стойност от заявка SELECT.

Тези, които програмират VBA достъпдокато работите с основата SQL даннисървърите често са изправени пред такава проста и необходима задача като изпращане на SQL заявка към база данни, било то INSERT, UPDATE или проста SQL заявка SELECT. И тъй като ние сме начинаещи програмисти, ние също трябва да можем да правим това, така че днес ще направим точно това.

Вече засегнахме темата за получаване на данни от SQL сървър, където написахме код във VBA, за да получим тези данни, например в статията за разтоварване на данни в текстов файл от MSSql 2008 или също леко засегната в статията Разтоварване на данни от Access в шаблон на Word и Excel. но по един или друг начин го разгледахме повърхностно и днес предлагам да поговорим за това малко по-подробно.

Забележка! Всички примери по-долу са разгледани с помощта на проекта Access 2003 ADP и базата данни MSSql 2008. Ако не знаете какво е ADP проект като цяло, тогава разгледахме това в статията Как да създадете и конфигурирате проект на Access ADP

Изходни данни за примери

Да кажем, че имаме test_table, която ще съдържа числата и имената на месеците в годината (заявките се правят с Студио за управление)

СЪЗДАВАЙТЕ ТАБЛИЦА. (НЕ НУЛВО, (50) НУЛЕВО) В движение

Както казах, ще използваме ADP проект, конфигуриран да работи с MS SQL 2008, в който създадох тестова форма и добавих бутон за стартиране с подпис "бягай"който трябва да тестваме нашия код, т.е. ние ще напишем целия код в манипулатора на събития " Натискане на бутон».

VBA заявки INSERT, UPDATE, DELETE

За да не се забавя дълго време, нека започнем веднага, да кажем, че трябва да добавим ред към нашата тестова таблица ( кодът е коментиран)/

Private Sub start_Click () "Декларирайте променлива за съхраняване на низа на заявката Dim sql_query като низ" Напишете заявката, от която се нуждаем sql_query = "INSERT INTO test_table (id, name_mon) VALUES (" 6 "," юни ")" "Изпълнете го DoCmd RunSQL sql_query End Sub

В този случай заявката се изпълнява с помощта на текущите параметри за връзка с базата данни. Можем да проверим дали данните са добавени или не.

Както можете да видите, данните са въведени.

За да изтриете един ред, напишете следния код.

Private Sub start_Click () "Декларирайте променлива за съхраняване на низа на заявката Dim sql_query като низ" Напишете заявка за изтриване към нея sql_query = "DELETE test_table WHERE id = 6" "Изпълнете я DoCmd.RunSQL sql_query End Sub

Ако проверим, ще видим, че необходимият ред е изтрит.

За да актуализирате данните, пишете в променливата sql_query заявка за актуализиранеНадявам се смисълът да е ясен.

SELECT заявка към VBA база

Тук нещата са малко по-интересни, отколкото с останалите SQL конструкции.

Първо, да кажем, че трябва да получим всички данни от таблицата и, например, ще ги обработим и покажем в съобщение, а вие, разбира се, можете да го използвате за други цели, за това пишем следното код

Private Sub start_Click () "Деклариране на променливи" За набор от записи от базата данни Dim RS As ADODB.Recordset "Стринг за заявка Dim sql_query като низ" Низ за показване на обобщените данни в съобщението Dim str As String "Създаване на нов обект за набор от записи RS = Нов ADODB .Recordset "Низ за заявка sql_query =" SELECT id, name_mon ОТ test_table "" Изпълнете заявката, като използвате текущите настройки за връзка на проекта RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Докато не преминава през записа (RS.EOF)" Попълнете променливата, за да се покаже съобщението str = str & RS.Fields ("id") & "-" & RS.Fields ("name_mon") & vbnewline "преминаване към следващия запис RS.MoveNext Wend" Показване на съобщението msgbox str End Sub

Тук вече използваме VBA Access цикли, за да преглеждаме всички стойности в нашия набор от записи.

Но доста често е необходимо да получите не всички стойности от набор от записи, а само едно, например името на месеца по неговия код. И за да направите това, използването на цикъл е някак скъпо, така че можем просто да напишем заявка, която ще върне само една стойност и ще се позовава на нея, например, ще получим името на месеца по код 5

Private Sub start_Click () "Деклариране на променливи" За набор от записи от базата данни Dim RS As ADODB.Recordset "Стринг за заявка Dim sql_query като низ" Низ за показване на общата стойност Dim str As String "Създаване на нов обект за набор от записи RS = Нов ADODB.Recordset "Стринг за заявка sql_query =" SELECT name_mon FROM test_table WHERE id = 5 "" Изпълнете заявката, като използвате текущите настройки за връзка на проекта RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic = RS "Вземете нашата стойност. Полета (0) msgbox str Край под

За универсалност тук вече се обърнахме не по името на клетката, а по нейния индекс, т.е. 0 и това е първата стойност в Набор от записи, в крайна сметка получихме стойността "Може".

Както можете да видите, всичко е доста просто. Ако често трябва да получите конкретна стойност от базата ( както в последния пример), тогава препоръчвам да изведете целия код в отделна функция (Как да напишете функция във VBA Access 2003) с един входен параметър, например кода на месеца ( ако вземем предвид нашия пример) и просто, когато е необходимо да изведем тази стойност, извикваме функцията, от която се нуждаем, с необходимия параметър и това е всичко, това значително ще намали VBA кода и ще подобри възприемането на нашата програма.

Това е всичко за днес. Късмет!

Примери за SQL заявки могат да се използват за научаване и практикуване на писане на SQL заявки в MS Access.

Една SQL заявка може да бъде вложена в друга. Подзаявката не е нищо повече от заявка в заявка. Обикновено подзаявката се използва в клауза WHERE. Но има и други начини за използване на подзаявки.

Заявка Q011.Показва се информация за продукти от таблицата m_product, чиито кодове също са в таблицата m_income:

ИЗБЕРЕТЕ *
ОТ m_product
WHERE id IN (ИЗБЕРЕТЕ product_id ОТ m_income);

Заявка Q012.Показва се списъкът с продукти от таблицата m_product, чиито кодове не са в таблицата m_outcome:

ИЗБЕРЕТЕ *
ОТ m_product
WHERE id NOT IN (ИЗБЕРЕТЕ product_id ОТ m_outcome);

Заявка Q013.Тази SQL заявка показва уникален списък с кодове и имена на продукти, които имат кодове в таблицата m_income, но не и в таблицата m_outcome:

ИЗБЕРЕТЕ DISTINCT идентификатор на продукт, заглавие
ОТ m_income INNER JOIN m_product
ВКЛЮЧЕНО m_income.product_id = m_product.id
WHERE product_id НЕ В (ИЗБЕРЕТЕ product_id FROM m_outcome);

Заявка Q014.От таблицата m_category се показва уникален списък с категории, чиито имена започват с буквата M:

ИЗБЕРЕТЕ РАЗДЕЛЕНО заглавие
ОТ m_product
КЪДЕ заглавие КАТО "М *";

Заявка Q015.Пример за извършване на аритметични операции върху полета в заявка и преименуване на полета в заявка (псевдоним). Този пример изчислява сумата на разходите = количество * цена и марж на печалба за всеки запис на разходите за артикул, като се приеме, че печалбата е 7 процента от сумата на продажбите:

сума * цена / 100 * 7 AS печалба
ОТ m_outcome;

Заявка Q016.Чрез анализиране и опростяване на аритметичните операции можете да увеличите скоростта на изпълнение на заявката:

ИЗБЕРЕТЕ dt, product_id, сума, цена, сума * цена КАТО сума_изход,
outcome_sum * 0,07 AS печалба
ОТ m_outcome;

Заявка Q017.Използвайки оператора INNER JOIN, можете да обедините данни от множество таблици. В следващия пример, в зависимост от стойността на ctgry_id, всеки запис от таблицата m_income се съпоставя с името на категорията от таблицата m_category, към която принадлежи продуктът:

ИЗБЕРЕТЕ c.title, b.title, dt, сума, цена, сума * цена КАТО сума_доход
FROM (m_income AS A INNER JOIN m_product AS b ON a.product_id = b.id)
INNER JOIN m_category AS c ON b.ctgry_id = c.id
ПОРЪЧАЙ ПО в.заглавие, б.заглавие;

Заявка Q018.Функции като SUM - сума, COUNT - количество, AVG - средноаритметично, MAX - максимална стойност, MIN - минимална стойност се наричат ​​агрегатни функции. Те приемат множество стойности и след като бъдат обработени, връщат една стойност. Пример за изчисляване на сумата от произведението на полетата за сума и цена, които се използват агрегатна функцияСУМ.

Лабораторна работа No1

SQL: ИЗВЛЕЧВАНЕ НА ДАННИ - командаИЗБЕРЕТЕ

Обективен:

• да се запознаят със SQL изрази;
• научете как да създавате основни SQL заявки в Access, използвайки SELECT команди;

· Използването на оператори IN, BETWEEN, LIKE, IS NULL.

Упражнение№1. Създайте заявка, за да изберете в режим SQL всички стойности на полетата FIRST NAME и FIRST NAME от таблицата STUDENTS.

ИЗБЕРЕТЕ ИМЕ, ФАМИЛА

ОТ СТУДЕНТИТЕ;

Упражнение№2 ... Създайте заявка за избор в режим SQL на всички колони на таблицата STUDENTS.

ИЗБЕРЕТЕ *

ОТ СТУДЕНТИТЕ;

Задача номер 3.Създайте заявка, за да изберете в SQL режим имената на градовете, в които живеят учениците, информация за които е в таблицата ЛИЧНИ ДАННИ.

ИЗБЕРЕТЕ РАЗЛИЧЕН ГРАД

ОТ [ЛИЧНИ ДАННИ];

Задача номер 4.Създайте заявка за избор в режим на SQL, която избира имената на всички студенти с фамилно име Иванов, изброени в таблицата СТУДЕНТИ.

ИЗБЕРЕТЕ ФАМИЛИЯ, ИМЕ

ОТ СТУДЕНТИТЕ

КЪДЕ ФАМИЛИЯ = "Иванов";

Задача номер 5... Създайте заявка за избор в режим SQL, за да получите имената и фамилните имена на студенти, записани в групата UIT-22 на бюджетната форма на обучение.

ИЗБЕРЕТЕ ФАМИЛИЯ, ИМЕ

ОТ СТУДЕНТИТЕ

КЪДЕ ГРУПА = "WHIT-22" И БЮДЖЕТ = вярно;

Задача номер 6... Създайте заявка в режим SQL. на извадка от таблицата ИЗПИТИ ИЗПИТ информация за ученици само с 4 и 5 оценки.

ИЗБЕРЕТЕ *

ОТ [ПРОМЯНАИЗПИТИ]

КЪДЕТООЦЕНКАIN (4.5);

Задача номер 7.Създайте zanpoc и SQL режим за селекция от информация за студенти, които имат изпитна оценка 3 по предмета IOSU.

ИЗБЕРЕТЕ *

ОТ [ПРОМЯНАИЗПИТИ]

КЪДЕТОНЕЩО=" IOSU"ИОЦЕНКАНе в (4.5);

Задача номер 8.Създайте заявка в режим SQL, за да изберете записи за субекти, чиито часове са между 100 и 130.

ИЗБЕРЕТЕ *

ОТАРТИКУЛИ

КЪДЕТОЧАСОВНИКМЕЖДУ 100 И 130;

Задача номер 9.Създайте заявка в режим SQL, за да изберете от таблицата STUDENTS информация за ученици, чиито фамилни имена започват например с буквата "C".

ИЗБЕРЕТЕ *

ОТСТУДЕНТИТЕ

КЪДЕТОФАМИЛИЯКАТО "С*";

заключение:В хода на лабораторната работа се запознахме с SQL изрази, научихме как да създаваме най-простите SQL заявки в Access с помощта на командата SELECT с помощта на операторите IN, BETWEEN, LIKE.

SQL - Урок 4. Извличане на данни - Инструкция SELECT

ИЗБЕРЕТЕ какво_ да изберете ОТ откъде да изберете;

Нека първо да разгледаме всички колони от таблицата с потребители:

ИЗБЕРЕТЕ * ОТ потребители;

Това са всички наши данни, които въведохме в тази таблица. Но да предположим, че искаме да видим само колоната id_user (например в последния урок трябваше да знаем какво е id_user в таблицата с потребители, за да попълним таблицата с теми). За да направите това, ще посочим името на тази колона в заявката:

ИЗБЕРЕТЕ id_user ОТ потребители;

Е, ако искаме да видим например имената и имейлите на нашите потребители, тогава ще изброим колоните, които ни интересуват, разделени със запетаи:

ИЗБЕРЕТЕ име, имейл ОТ потребители;

По същия начин можете да видите какви данни съдържат другите ни таблици. Да видим какви теми имаме:

ИЗБЕРЕТЕ * ОТ теми;

Сега имаме само 4 теми, но какво ще стане, ако има 100? Бих искал те да се показват, например, по азбучен ред. Има ключова дума за това в SQL ПОДРЕДЕНИ ПОслед което името на колоната, по която ще се извърши сортирането. Синтаксисът е както следва:

SELECT име_на_колона ОТ име_на_таблица ORDER BY сорт_име_колона;

По подразбиране сортирането е във възходящ ред, но това може да се промени чрез добавяне на ключовата дума DESC

Сега нашите данни са сортирани в низходящ ред.

Сортирането може да се извърши по няколко колони наведнъж. Например следната заявка ще сортира данните по колоната topic_name и ако тази колона съдържа няколко еднакви реда, тогава колоната id_author ще бъде сортирана в низходящ ред:

Сравнете резултата с резултата от предишната заявка.

Много често не се нуждаем от цялата информация от таблицата. Например, искаме да знаем кои теми са създадени от потребителя sveta (id = 4). Има ключова дума за това в SQL КЪДЕТО, синтаксисът за такава заявка е както следва:

За нашия пример условието е потребителският идентификатор, т.е. трябват ни само редове с 4 в колоната id_author (user ID sveta):

Или искаме да знаем кой е създал темата за велосипедите:

Разбира се, би било по-удобно да се показва името на автора вместо идентификатора на автора, но имената се съхраняват в друга таблица. В следващите уроци ще научим как да избираме данни от множество таблици. Засега нека да разберем какви условия могат да бъдат зададени с помощта на ключовата дума WHERE.

LIKE Оператор Метазнаци

Най-често срещаният метазнак е % ... Това означава всякакви символи. Например, ако трябва да намерим думи, започващи с буквите "led", тогава пишем КАТО "led%", а ако искаме да намерим думи, които съдържат знаците "club", тогава пишем КАТО "% club%" . Например:

Друг често използван метасимвол е _ ... За разлика от%, който обозначава няколко или никакви знака, долните черти означават точно един знак. Например:

Обърнете внимание на пространството между метасимвола и "риба", ако го пропуснете, заявката няма да работи, т.к. метазнак _ означава точно един знак, а интервалът също е знак.

Достатъчно е за днес. В следващия урок ще научим как да правим заявки за две или повече таблици. Дотогава опитайте сами да направите заявка в таблицата с публикации.

Една SQL заявка може да бъде вложена в друга. Подзаявката не е нищо повече от заявка в заявка. Обикновено подзаявката се използва в клауза WHERE. Но има и други начини за използване на подзаявки.

Заявка Q011.Показва се информация за продукти от таблицата m_product, чиито кодове също са в таблицата m_income:

ИЗБЕРЕТЕ *
ОТ m_product
WHERE id IN (ИЗБЕРЕТЕ product_id ОТ m_income);

Заявка Q012.Показва се списъкът с продукти от таблицата m_product, чиито кодове не са в таблицата m_outcome:

ИЗБЕРЕТЕ *
ОТ m_product
WHERE id NOT IN (ИЗБЕРЕТЕ product_id ОТ m_outcome);

Заявка Q013.Тази SQL заявка показва уникален списък с кодове и имена на продукти, които имат кодове в таблицата m_income, но не и в таблицата m_outcome:

ИЗБЕРЕТЕ DISTINCT идентификатор на продукт, заглавие
ОТ m_income INNER JOIN m_product
ВКЛЮЧЕНО m_income.product_id = m_product.id
WHERE product_id НЕ В (ИЗБЕРЕТЕ product_id FROM m_outcome);

Заявка Q014.От таблицата m_category се показва уникален списък с категории, чиито имена започват с буквата M:

ИЗБЕРЕТЕ РАЗДЕЛЕНО заглавие
ОТ m_product
КЪДЕ заглавие КАТО "М *";

Заявка Q015.Пример за извършване на аритметични операции върху полета в заявка и преименуване на полета в заявка (псевдоним). Този пример изчислява сумата на разходите = количество * цена и марж на печалба за всеки запис на разходите за артикул, като се приеме, че печалбата е 7 процента от сумата на продажбите:

Цена, сума * цена като сума_сума,
сума * цена / 100 * 7 AS печалба
ОТ m_outcome;

Заявка Q016.Чрез анализиране и опростяване на аритметичните операции можете да увеличите скоростта на изпълнение на заявката:

outcome_sum * 0,07 AS печалба
ОТ m_outcome;

Заявка Q017.Използвайки оператора INNER JOIN, можете да обедините данни от множество таблици. В следващия пример, в зависимост от стойността на ctgry_id, всеки запис от таблицата m_income се съпоставя с името на категорията от таблицата m_category, към която принадлежи продуктът:

ИЗБЕРЕТЕ c.title, b.title, dt, сума, цена, сума * цена КАТО сума_доход
FROM (m_income AS A INNER JOIN m_product AS b ON a.product_id = b.id)
INNER JOIN m_category AS c ON b.ctgry_id = c.id
ПОРЪЧАЙ ПО в.заглавие, б.заглавие;

Заявка Q018.Функции като SUM - сума, COUNT - количество, AVG - средноаритметично, MAX - максимална стойност, MIN - минимална стойност се наричат ​​агрегатни функции. Те приемат множество стойности и след като бъдат обработени, връщат една стойност. Пример за изчисляване на сумата от произведението на полетата за сума и цена с помощта на агрегатната функция SUM:

ИЗБЕРЕТЕ СУМА (сума * цена) КАТО Обща_сума
ОТ m_доход;

Заявка Q019.Пример за използване на множество агрегатни функции:

ИЗБЕРЕТЕ Сума (сума) КАТО Сума_сума, AVG (сума) КАТО Сума_AVG,
MAX (количество) AS Amount_Max, Мин. (количество) AS Amount_Min,
Брой (*) като общ_ брой
ОТ m_доход;

Заявка Q020.Този пример изчислява сумата на всички елементи с код 1, изписани с главни букви през юни 2011 г.:

ИЗБЕРЕТЕ
ОТ m_доход
КЪДЕ product_id = 1 И dt МЕЖДУ # 6/1/2011 # И # 6/30/2011 # ;.

Заявка Q021.Следната SQL заявка изчислява количеството продадени продукти с код 4 или 6:

ИЗБЕРЕТЕ
ОТ m_outcome
КЪДЕ product_id = 4 ИЛИ product_id = 6;

Заявка Q022.Изчислява се за каква сума е продадена на 12 юни 2011 г. на стоки с код 4 или 6:

SELECT Сума (сума * цена) КАТО сума_сума
ОТ m_outcome
КЪДЕ (product_id = 4 ИЛИ product_id = 6) И dt = # 6/12/2011 #;

Заявка Q023.Задачата е следната. Изчислете общото количество стоки в категорията "Хлебни", които са били капитализирани.

За да разрешите този проблем, трябва да работите с три таблици: m_income, m_product и m_category, защото:
- количеството и цената на капитализираните стоки се съхраняват в таблицата m_income;
- кодът на категорията на всеки продукт се съхранява в таблицата m_product;
- името на заглавието на категорията се съхранява в таблицата m_category.

За да разрешим този проблем, ще използваме следния алгоритъм:
- определяне на кода на категорията "Хлебни изделия" от таблицата m_category чрез подзаявка;
- съединяване на таблици m_income и m_product за определяне на категорията на всеки капитализиран продукт;
- изчисляване на сумата за получаване (= количество * цена) за стоки, чийто код на категорията е равен на кода, определен от горната подзаявка.

FROM m_product AS a INNER JOIN m_income AS b ON a.id = b.product_id
WHERE ctgry_id = (ИЗБЕРЕТЕ идентификатор ОТ m_category WHERE title = "(! LANG: Bakery"); !}

Заявка Q024.Ще решим проблема с изчисляването на общото количество капитализирани стоки в категорията "Хлебни продукти", използвайки следния алгоритъм:
- за всеки запис от таблицата m_income, в зависимост от стойността на неговия product_id, от таблицата m_category, съвпада с името на категорията;
- изберете записи, за които категорията е равна на "Хлебни изделия";
- изчисляване на сумата на касовата бележка = количество * цена.

SELECT Сума (сума * цена) КАТО сума_доход
FROM (m_product AS a INNER JOIN m_income AS b ON a.id = b.product_id)
КЪДЕ c.title = "(! LANG: Пекарна"; !}

Заявка Q025.Този пример изчислява колко артикула са били консумирани:

SELECT COUNT (product_id) КАТО product_cnt
ОТ (ИЗБЕРЕТЕ РАЗЛИЧЕН продукт_id ОТ m_outcome) AS t;

Заявка Q026.Клаузата GROUP BY се използва за групиране на записи. Обикновено записите се групират по стойността на едно или повече полета и към всяка група се прилага някаква обобщена операция. Например следната заявка генерира отчет за продажба на стоки. Тоест се генерира таблица, която съдържа имената на стоките и сумата, за която са продадени:

ИЗБЕРЕТЕ заглавие, SUM (сума * цена) КАТО сума_сума
ОТ m_product КАТО ВЪТРЕШНО ПРИЕДИНЕНИЕ m_outcome КАТО b
ON a.id = b.product_id
GROUP BY заглавие;

Заявка Q027.Отчет за продажбите по категории. Тоест се генерира таблица, която съдържа имената на продуктовите категории, общата сума, за която са продадени продуктите от тези категории, и средната сума на продажбите. Функцията ROUND се използва за закръгляне на средната стойност до стотна (втората цифра след десетичния разделител):

ИЗБЕРЕТЕ c.title, SUM (сума * цена) КАТО outcome_sum,
КРЪГЛО (СР. (сума * цена), 2) КАТО сума_ср
FROM (m_product AS A INNER JOIN m_outcome AS b ON a.id = b.product_id)
INNER JOIN m_category AS c ON a.ctgry_id = c.id
ГРУПА ПО в.заглавие;

Заявка Q028.За всеки продукт се изчислява общият и средният брой на неговите постъпления и се показва информация за стоките, чийто общ брой постъпления е не по-малко от 500:

ИЗБЕРЕТЕ product_id, SUM (сума) КАТО сума_сума,
Кръгла (Ср. (сума), 2) ASколичество_ср
ОТ m_доход
GROUP BY product_id
ИМАТЕ Сума (сума)> = 500;

Заявка Q029.Тази заявка изчислява за всеки артикул сумата и средната стойност на постъпленията през второто тримесечие на 2011 г. Ако общото количество получени стоки е не по-малко от 1000, тогава се показва информация за този продукт:

ИЗБЕРЕТЕ заглавие, SUM (сума * цена) КАТО сума_доход
ОТ m_income a INNER JOIN m_product b ON a.product_id = b.id
КЪДЕ dt МЕЖДУ # 4/1/2011 # И # 6/30/2011 #
ГРУПА ПО заглавие
ИМАТЕ СУМА (сума * цена)> = 1000;

Заявка Q030.В някои случаи трябва да съпоставите всеки запис от определена таблица към всеки запис от друга таблица; това, което се нарича декартово произведение. Таблицата, получена в резултат на такава връзка, се нарича таблица на Декарт. Например, ако някаква таблица А има 100 записа, а таблица Б има 15 записа, тогава тяхната таблица на Декарт ще се състои от 100 * 15 = 150 записа. Следната заявка свързва всеки запис в таблицата m_income с всеки запис в таблицата m_outcome:

SELECT * FROM m_income, m_outcome;

Заявка Q031.Пример за групиране на записи по две полета. Следната SQL заявка изчислява количеството и количеството стоки, получени от всеки доставчик за всеки доставчик:

SUM (сума * цена) КАТО сума_доход

Заявка Q032.Пример за групиране на записи по две полета. Следната заявка изчислява за всеки доставчик количеството и количеството на продадените от нас продукти:

ИЗБЕРЕТЕ идентификатор_на_доставчик, идентификатор_на_продукта, SUM (сума) КАТО сума_сума,
SUM (сума * цена) КАТО сума_сума
GROUP BY идентификатор_на_доставчик, идентификатор_ на продукт;

Заявка Q033.В този пример двете по-горе заявки (q031 и q032) се използват като подзаявки. Резултатите от тези заявки, използващи метода LEFT JOIN, се комбинират в един отчет. Следното запитване отчита количеството и количеството на получените и продадените продукти за всеки доставчик. Моля, имайте предвид, че ако някой продукт вече е пристигнал, но все още не е продаден, тогава клетката outcome_sum за този запис ще бъде празна. Трябва също да се отбележи, че тази заявка е само пример за използване на относително сложни заявки като подзаявка. производителност на това искане SQL с голямо количество данни е под въпрос:

ИЗБЕРЕТЕ *
ОТ
SUM (сума * цена) КАТО сума_доход
НА a.product_id = b.id GROUP BY supplier_id, product_id) КАТО
ЛЯВО ПРИСЪЕДИНЯВАНЕ
(ИЗБЕРЕТЕ идентификатор_на_доставчика, идентификатор_на_продукта, SUM (сума) КАТО сума_сума,
SUM (сума * цена) КАТО сума_сума
ОТ m_outcome КАТО ВЪТРЕШНО ПРИСЪЕДИНЕНИЕ m_product AS b
НА a.product_id = b.id GROUP BY supplier_id, product_id) AS b
ON (a.product_id = b.product_id) AND (a.supplier_id = b.supplier_id);

Заявка Q034.В този пример двете по-горе заявки (q031 и q032) се използват като подзаявки. Резултатите от тези заявки, използващи метода RIGTH JOIN, се комбинират в един отчет. Следващата заявка показва отчет за размера на плащанията от всеки клиент за използваните от него платежни системи и размера на инвестициите му. Следното запитване отчита количеството и количеството на получените и продадените продукти за всеки доставчик. Трябва да се отбележи, че ако някой продукт вече е продаден, но все още не е пристигнал, тогава клетката за сума на дохода за този запис ще бъде празна. Наличието на такива празни клетки е индикатор за грешка в отчитането на продажбите, тъй като преди продажбата е необходимо първо да пристигне съответния продукт:

ИЗБЕРЕТЕ *
ОТ
(ИЗБЕРЕТЕ идентификатор_на_доставчика, идентификатор_на_продукта, SUM (сума) КАТО сума_сума,
SUM (сума * цена) КАТО сума_доход
ОТ m_income КАТО INNER JOIN m_product AS b ON a.product_id = b.id
GROUP BY идентификатор_на_доставчик, идентификатор_ на продукт) КАТО a
ДЯСНО ПРИСЪЕДИНЯВАНЕ
(ИЗБЕРЕТЕ идентификатор_на_доставчика, идентификатор_на_продукта, SUM (сума) КАТО сума_сума,
SUM (сума * цена) КАТО сума_сума
ОТ m_outcome КАТО ВЪТРЕШНО ПРИЕДИНЯВАНЕ m_product AS b ON a.product_id = b.id
GROUP BY идентификатор_на_доставчик, идентификатор_ на продукт) КАТО b
ВКЛ. (a.supplier_id = b.supplier_id) И (a.product_id = b.product_id);

Заявка Q035.Показва справка за размера на приходите и разходите по продукти. За да направите това, се създава списък с продукти според таблиците m_income и m_outcome, след което за всеки продукт от този списък се изчислява сумата от неговите постъпления според таблицата m_income и сумата от неговите разходи според таблицата m_outcome:

SELECT product_id, SUM (in_amount) КАТО сума_приход,
SUM (out_amount) AS сума_изход
ОТ
(ИЗБЕРЕТЕ идентификатор на продукт, сума КАТО в_сума, 0 АС изходяща_сума
ОТ m_доход
СЪЮЗ ВСИЧКИ
SELECT product_id, 0 AS in_amount, сума AS out_amount
ОТ m_outcome) КАТО t
ГРУПА ПО product_id;

Заявка Q036. EXISTS връща TRUE, ако наборът, прехвърлен към него, съдържа елементи. Функцията EXISTS връща FALSE, ако наборът, предаден към нея, е празен, тоест не съдържа елементи. Следната заявка показва продуктовите кодове, които се съдържат както в таблиците m_income, така и в m_outcome:

ИЗБЕРЕТЕ РАЗЛИЧЕН идентификатор на продукт
ОТ m_income КАТО a
КЪДЕ СЪЩЕСТВУВА (ИЗБЕРЕТЕ product_id ОТ m_outcome AS b

Заявка Q037.Показват се продуктови кодове, които се съдържат както в таблицата m_income, така и в таблицата m_outcome:

ИЗБЕРЕТЕ РАЗЛИЧЕН идентификатор на продукт
ОТ m_income КАТО a
WHERE product_id IN (ИЗБЕРЕТЕ product_id FROM m_outcome)

Заявка Q038.Показват се продуктови кодове, които се съдържат както в таблицата m_income, но не се съдържат в таблицата m_outcome:

ИЗБЕРЕТЕ РАЗЛИЧЕН идентификатор на продукт
ОТ m_income КАТО a
КЪДЕ НЕ СЪЩЕСТВУВА (ИЗБЕРЕТЕ product_id ОТ m_outcome AS b
КЪДЕ b.product_id = a.product_id);

Заявка Q039.Показва се списък с продукти с максимални продажби. Алгоритъмът е следният. За всеки продукт се изчислява сумата на продажбите му. След това се определя максималната от тези суми. След това за всеки продукт отново се изчислява общата сума на неговите продажби и се извеждат кодът и общата сума на продажбите на продуктите, чийто сбор е равен на максимума:

SELECT product_id, SUM (сума * цена) КАТО сума_сума
ОТ m_outcome
GROUP BY product_id
ИМАТЕ СУМА (сума * цена) = (ИЗБЕРЕТЕ МАКС (s_amount)
ОТ (ИЗБЕРЕТЕ СУМА (сума * цена) КАТО s_сума FROM m_outcome GROUP BY product_id));

Заявка Q040. IIF запазена дума ( условен оператор) се използва за оценка булев изрази извършване на едно или друго действие в зависимост от резултата (ВЯРНО или НЕПРАВНО). В следващия пример доставката на артикул се счита за „малка“, ако количеството е по-малко от 500. В противен случай, т.е. количеството на разписката е по-голямо или равно на 500, доставката се счита за „голяма“:

ИЗБЕРЕТЕ dt, product_id, сума,
IIF (сума<500,"малая","большая") AS mark
ОТ m_доход;

SQL заявка Q041.В случай, че операторът IIF се използва няколко пъти, е по-удобно да го замените с оператор SWITCH. Операторът SWITCH (оператор с множествен избор) се използва за оценка на булев израз и извършване на действие въз основа на резултата. В следващия пример доставената партида се счита за „малка“, ако количеството стоки в партидата е по-малко от 500. В противен случай, тоест, ако количеството стоки е по-голямо или равно на 500, партидата се счита за „голяма ":

ИЗБЕРЕТЕ dt, product_id, сума,
ПРЕКЛЮЧВАТЕ (сума<500,"малая",amount>= 500, "голям") AS знак
ОТ m_доход;

Заявка Q042. <300 не выполняется, то проверяется является ли количество товаров в партии меньше 500. Если размер партии меньше 500, то она считается «средней». В противном случае партия считается «большой»:

ИЗБЕРЕТЕ dt, product_id, сума,
IIF (сума<300,"малая",
IIF (сума<1000,"средняя","большая")) AS mark
ОТ m_доход;

SQL заявка Q043.При следваща заявка, ако количеството стоки в получената партида е по-малко от 300, тогава партидата се счита за „малка“. В противен случай, това е, ако количеството условие<300 не выполняется, то проверяется является ли количество товаров в партии меньше 500. Если размер партии меньше 500, то она считается «средней». В противном случае партия считается «большой»:

ИЗБЕРЕТЕ dt, product_id, сума,
ПРЕКЛЮЧВАТЕ (сума<300,"малая",
количество<1000,"средняя",
сума> = 1000, "голям") AS знак
ОТ m_доход;

SQL заявка Q044.В следващата заявка продажбите са разделени на три групи: малки (до 150), средни (от 150 до 300), големи (300 или повече). След това за всяка група се изчислява общата сума:

ИЗБЕРЕТЕ Категория, SUM (сума_изход) КАТО Ctgry_Total
ОТ (ИЗБЕРЕТЕ сума * цена КАТО сума_сума,
IIf (сума * цена<150,"малая",
IIf (сума * цена<300,"средняя","большая")) AS Category
ОТ m_outcome) КАТО t
ГРУПА ПО КАТЕГОРИЯ;

SQL заявка Q045.Функцията DateAdd се използва за добавяне на дни, месеци или години към дадена дата и получаване на нова дата. Следваща заявка:
1) добавя 30 дни към датата от полето dt и показва новата дата в полето dt_plus_30d;
2) добавя 1 месец към датата от полето dt и показва новата дата в полето dt_plus_1m:

ИЗБЕРЕТЕ dt, dateadd ("d", 30, dt) AS dt_plus_30d, dateadd ("m", 1, dt) AS dt_plus_1m
ОТ m_доход;

SQL заявка Q046.Функцията DateDiff изчислява разликата между две дати в различни единици (дни, месеци или години). Следната заявка изчислява разликата между датата в полето dt и текущата дата в дни, месеци и години:

ИЗБЕРЕТЕ dt, DateDiff ("d", dt, Дата ()) КАТО последния_ден,
DateDiff ("m", dt, Дата ()) КАТО последните_месеци,
DateDiff ("гггг", dt, Дата ()) КАТО последните_години
ОТ m_доход;

SQL заявка Q047.Броят дни от датата на получаване на стоките (таблица m_income) до текущата дата се изчислява с помощта на функцията DateDiff и се сравнява датата на изтичане (таблица m_product):

DateDiff ("d", dt, Дата ()) КАТО последните_дни
ОТ m_income КАТО ВЪТРЕШНО ПРИСЪЕДИНЕНИЕ m_product AS b
ON a.product_id = b.id;

SQL заявка Q048.Изчислява се броят на дните от датата на получаване на стоката до текущата дата, след което се проверява дали този брой надвишава срока на годност:

ИЗБЕРЕТЕ a.id, product_id, dt, lifeday,
DateDiff ("d", dt, Date ()) AS last_days, IIf (last_days> lifeday, "Yes", "No") AS date_expire
ОТ m_income a INNER JOIN m_product b
ON a.product_id = b.id;

SQL заявка Q049.Изчислява се броят на месеците от датата на получаване на стоката до текущата дата. Колоната month_last1 изчислява абсолютния брой месеци, колоната month_last2 изчислява броя на пълните месеци:

ИЗБЕРЕТЕ dt, DateDiff ("m", dt, Дата ()) КАТО месец_последен1,
DateDiff ("m", dt, Date ()) - iif (day (dt)> day (date ()), 1,0) AS month_last2
ОТ m_доход;

SQL заявка Q050.Показва се тримесечен отчет за количеството и количеството капитализирани стоки за 2011 г.:

ИЗБЕРЕТЕ квартал, SUM (outcome_sum) КАТО Общо
ОТ (ИЗБЕРЕТЕ сума * цена КАТО сума_изход, месец (dt) AS m,
ПРЕКЛЮЧВАТЕЛ (м<4,1,m<7,2,m<10,3,m>= 10.4) AS квартал
ОТ m_доход КЪДЕ година (dt) = 2011) КАТО t
ГРУПА ПО квартал;

Заявка Q051.Следната заявка помага да разберете дали потребителите са успели да въведат информация в системата за потреблението на артикул в количество, по-голямо от сумата на получаването на артикула:

SELECT product_id, SUM (в_сума) КАТО сума_на_приход, SUM (изход_сума) КАТО сума_изход
FROM (ИЗБЕРЕТЕ product_id, сума * цена като сума_на_сума, 0 като сума_изход
от m_доход
СЪЮЗ ВСИЧКИ
ИЗБЕРЕТЕ product_id, 0 като сума_сума, сума * цена като изходяща_сума
от m_outcome) КАТО t
GROUP BY product_id
ИМАТЕ СУМА (в_сума)

Заявка Q052.Номерирането на редовете, върнати от заявка, се изпълнява по различни начини. Например, можете да преномерирате редовете на отчет, изготвен в MS Access, като използвате самия MS Access. Можете също да преномерирате, като използвате езици за програмиране като VBA или PHP. Понякога обаче това трябва да се направи в самата SQL заявка. Така че следната заявка ще номерира редовете на таблицата m_income според нарастващия ред на стойностите на полето за ID:

ИЗБЕРЕТЕ БРОЯ (*) като N, b.id, b.product_id, b.amount, b.price
ОТ m_income a INNER JOIN m_income b ON a.id<= b.id
GROUP BY b.id, b.product_id, b.amount, b.price;

Заявка Q053.Първите пет сред продуктите се показват по размер на продажбите. Първите пет записа се показват с помощта на оператора TOP:

ИЗБЕРЕТЕ ТОП 5, product_id, sum (сума * цена) AS suma
ОТ m_outcome
GROUP BY product_id
ПОРЪЧАЙ ПО сума (сума * цена) DESC;

Заявка Q054.Показват се петте лидери сред продуктите по размер на продажбите и в резултат редовете са номерирани:

ИЗБЕРЕТЕ БРОЯ (*) КАТО N, b.product_id, b.summa
ОТ

FROM m_outcome GROUP BY product_id) AS a
ВЪТРЕШНО ПРИСЪЕДИНЕНИЕ
(ИЗБЕРЕТЕ product_id, сума (сума * цена) AS suma,
summa * 10000000 + product_id AS id
ОТ m_outcome GROUP BY product_id) AS b
ON a.id> = b.id
ГРУПА ПО b.product_id, b.summa
КАТО БРОЙ (*)<=5
ПОРЪЧАЙТЕ ПО БРОЙ (*);

Заявка Q055.Следната SQL заявка показва използването на математическите функции COS, SIN, TAN, SQRT, ^ и ABS в MS Access SQL:

SELECT (изберете брой (*) от m_income) като N, 3,1415926 като pi, k,
2 * pi * (k-1) / N като x, COS (x) като COS_, SIN (x) като SIN_, TAN (x) като TAN_,
SQR (x) като SQRT_, x ^ 3 като "x ^ 3", ABS (x) като ABS_
ОТ (ИЗБЕРЕТЕ БРОЙ (*) КАТО k
ОТ m_income КАТО ВЪТРЕШНО ПРИСЪЕДИНЕНЕ m_income КАТО b ON a.id<=b.id
ГРУПА ПО b.id) t;