Наложение ограничений на группировку записей

Группировка записей

Когда требуется получать агрегированные (итоговые) данные не по всему результирующему набору данных (НД), а по каждой из входящих в него групп записей, которые характеризуются одинаковым значением какого-либо столбца, после предложения WHERE вводится предложение GROUP BY

GROUP столбец1, столбец2…

При этом необходимо, чтобы один из столбцов возвращаемых оператором SELECT был представлен агрегатной функцией.

 

#1 Найти сред цену каждого товара

 

Select Name_tovar, avg (price)//сред значение столбца цена

From Tovar

Group by name_tovar

 

#2 Выдать общую стоимость каждого товара

 

Select Name_tovar, sum (price*kolvo) as TotalCost//столбец с общей стоимотью будет иметь имя TotalCost

From Tovar

Group by name_tovar

 

#3 Выдать общую стоимость товаров, поступивших на каждую дату, цена которых превышает 1000

Пусть в таблице товар Tovar есть поле дата поступления Data_in

 

Select data_in, sum (price*kolvo) as TotalCost

From Tovar

Where price >1000

Group by Data_in

 

Наложение ограничений на группировку записей

Если необходимо выдавать итоговые данные не по всем группам записей, а только по тем из них, которые удовлетворяют некоторому условию, то после GROUP BY указывают

 

HAVING <агрегатная функция – count,sum,max,min,avg> <отношение – это <=,>=,!= и т.д.> <значение – выражение вычисляемое или константа или вложенный оператор select который вернет одно значение>

 

В предложении HAVING обязательно должна присутствовать агрегатная функция, которую нельзя использовать в предложении WHERE

 

#4 Показать даты поступления товаров, на которые (на дату) количество поступившего товара было не меньше 500. В результирующий набор данных включить только те группы записей, по которым число таких поступлений было больше 1.

 

Select Data_in, count(*)//все записи считаем count – это функция– сколько всего товара, количество поступивших

From tovar

Where kolvo>500

Group by Data_in

Having count(*)>1

 

Name_tovar	Data_in	Kolvo
	23.10.16
	25.10.16
	23.10.16
	25.10.16

В результате запроса получим

 

Name_tovar	Data_in	Kolvo
	23.10.16
2	25.10.16	300
	23.10.16
	25.10.16

На 23.10.16 – 2 записи

На 5.10.16 - 1 запись

Учитываем только те, где число больше 1.

 

Задание сложных условий поиска

IS NULL

Если требуется выдавать записи столбец которых имеет либо не имеет пустое значение, применяется предложение

<значение-столбец> IS [NOT] NULL

 

#5 Выдать наименование товаров для которых не указано количество

Select nametovar from Tovar

Where kolvo is null

Использование логических выражений

OR, AND, NOT

 

#6

Select * from Tovar

Where kolvo>100 and kolvo<=1000 and price=1000

 

Пусть есть таблица «Расход товара» она связана с таблицей «Товар» по внешнему ключу nametoval. Хранит название товара и его кол-во

Rashod

ID_rashod

Kolvo_R Nametovar(fk) Data_R

 

 

Сравнение столбца с результатом вычисления выражения

Выдать из таблице расход дату расхода, наименование товара, стоимость отпущенного товара, при этом учитывать только те записи, в которых стоимость отпущенного товара больше 10

 

Select Rashod.Data_R, Rashod.nametovar,(Rashod.kolvo * Tovar.price) as t from Tovar, Rashod

Where Rashod.nametovar=Tovar.nametovar//соединяем две таблицы между собой

And t>10

 

Если мы не свяжем таблиц по nametovar то получим просто декартово произведение таблиц

 

Использование Between (между)

Если требуется чтобы значение находилось в определенном интервале, то применяется between

<значение> [not] between <значение1> and <значение2>

 

#

Select * from Tovar

Where kolvo between 10 and 100

Первое должно быть меньше

(kolvo<=10 and >=)

 

Использование IN

Входит ли значение в набор, во множество значений

<значение > [NOT] IN (<знач1>,<знач2>,…)

 

#

Select * from Tovar

Where price (10,100,1000)

Где price=10 или price=100 и т.д.

Использование containing

Чтобы строковое поле содержало подстроку

<значение> [NOT] containing <подстрока>

 

#

Таблица покупателей а в ней столбец адрес, найти тех покупалей где в адресе есть подстрока «Маршала»

Select namepokupatel from Pokupateli where address containing “Маршала”

 

Upper/Lower

Преобразует строчные буквы в заглавные. Нижний и верхний регистры

 

# Выдать всех покупателей из города иркутск

Select * from pokupateli

Where upper(adress) containing “ИРКУТСК”

 

9. Использование сцепления строк/конкатенации ||

||

#Выдать имя покупателя и в скобках указать адрес

Select namepokupatel || ‘(‘ || address || ‘)’ from pokupateli

 

#

#ЭВМ-14-1

Select chifr || ‘-‘ || cast (god as char (4)) || ‘-‘ || cast (nomer as char (1)) from GROUSS

 

LIKE

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

 

<значение> [NOT] LIKE <шаблон> [ESCAPE <символ>]

 

Символ «%» в шаблоне означает, что на его месте может быть строка любой длины

Символ «_» в шаблоне указывает любой символ 1

 

Like ‘%-ый’

Like ‘20__’

 

Если требуется не использовать специальные функции и знак % и знак _ включается предложение ESCAPE, который определяет символ, появление которого в шаблоне отменяет специальные функции следующего за ним символа

 

Where proc like ‘_!%’ escape ‘!’

Знак! отменяет специальную функцию для процента, он будет обычным символом

 

# Найти информацию о покупателе в адресе которого содержится набор символов ‘унова’

Select * from pocupatel

Where address like ‘%унова%’

 

Использование функции CAST

Иногда возникает необходимость трактовать значение одного типа как значение другого типа, например символы использовать как число, а число как символы, в этом случае применяется функция CAST

CAST (значение AS <тип данных>)

 

Число символ Дата

Numeric -> character, date

Character->numeric,date

Date->character,numeric

 

#Найти покупателя, который делал закупки толи 105 205 или 305…единиц товара, заканчиваться будет на 05

Select * from pocupateli

Where cast(kolvo as chart (3)) like ‘%05’

 

#Значение типа Date не требуется преобразовывать к строковому типу:

Select ‘Дата расхода’ || DATE_R from Rashod

 

#Выдать ‘ЭВМ-14-1’

Select shifr||’-‘||cast(god as char (4))||’-’||cast (number as chart(1)) from GROUP as Shifrgproup

 

Использование подзапросов

 

Часто не возможно решить задачу путем использования единственного запроса Select/Например в тех случаях когда при использования условия поиска

Where <сравниваемое значение> <оператор> < значение, с которым сравнивать>

Параметр <значение с которым сравнивать> заранее не определен и должен вычисляться в момент выполнения оператора select, или представляет собой не одно а несколько значений, в этом случае применяются под запросы:

 

Select …

From…

Where <сравниваемое значение> <оператор> (select….)

Обязательно в круглых скобках

 

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

 

#Выдать дату, на которую приходится максимальный отпуск товара

Таблица Rashod

Select date_R, kolvo_R

from Rashod

Where kolvo_R = (Select max(kolvo_R from Rashod))

 

#Определить дату когда со склада было отгружено максимальное количество товара и реквизиты покупателя, который этот товар приобрел

Таблицы Pocupateli (P) и Rashod(R) связаны полем ID_pocup

 

Select R.date_R,R.kolvo_R, P.* from Rashod R, Pocupateli P //псевдоним

Where R.ID_pocup=P.ID_pocup

and (R.kolvo_R =(Select max(Kolvo_R) from Rashod)

 

ЗАМЕЧАНИЕ
Часто ошибкой является применение вложенного оператора select, который вместо единственного значения множество значений.

#Покупателей из ИРКУТСКА может быть много

SELECT R.data_R, R.Tovarname,R.kolvo_R, P.*

FROM Rashod R, Pocupateli P

WHERE R.ID_pocup=P.ID_pocup

And R.ID_pocup= (Select P.ID_pocup from Pocupateli P where Upper (P.Gorod)=’ИРКУТСК’)

#ИСПРАВЛЕНО

SELECT R.data_R, R.Tovarname,R.kolvo_R, P.*

FROM Rashod R, Pocupateli P

WHERE R.ID_pocup=P.ID_pocup

And R.ID_pocup in (Select P.ID_pocup from Pocupateli P where Upper (P.Gorod) ’ИРКУТСК’)

 

 

Вложение подзапросов

# Составить список отгрузки товаров покупателю, который преобрел максимальную партию какого-либо товара

 

Select R1.* from Rashod R1

Where R1.pocup_name in

(select R2.pocup_name from Rashod R2

Where R2.kolvo = (select max(R3.kolvo) from Rashod R3))

Внешние соединения

Внешнее соединение определяется в предложении from согласно спецификации:

Select {*|<значение1>[,<значение2>]} from <table1> <вид соединения> join <table2> ON <условие поиска>

 

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

· Left - левое внешнее соединение, когда ведущей является table1

· Right - правое внешнее соединение, когда ведущей является table2

· Full – полное внешнее соединение, когда ведущей являются обе таблицы. В результирующий набор данных включаются все записи обеих таблиц, по следующему алгоритму: если для записи table1 имеются записи table2 которые удовлетворяют условию соединения, то в результирующий набор данных будут включены все комбинации соединения таких записей таблицы table1 и table2, в противном случае, в результирующий набор данных будет включена запись table1 соединенная с пустой записью, тоже относится и к записям table2.

 

#Пусть имеют таблицы А и В со связью по столбцу P1

A

P1	P2	P3
a	x
b	x
C	Y
d

B

P1	P2
X
y
z

 

Select A.P1,A.P2,B.P2 from A leftjoin B on A.P2=B.P1	Select A.P1,A.P2,B.P2 from A right join B on A.P2=B.P1	Select A.P1,A.P2,B.P1,B.P2 from A full join B on A.P2=B.P1
A.P1 A.P2 B.P2 a x   b x   C Y   d - -	A.P1 A.P2 B.P2 a x   b x   C Y   - -	A.P1 A.P2 B.P1 B.P2 a x x   b x x   C Y y   d - - - - z -

 

Группировка записей

Когда требуется получать агрегированные (итоговые) данные не по всему результирующему набору данных (НД), а по каждой из входящих в него групп записей, которые характеризуются одинаковым значением какого-либо столбца, после предложения WHERE вводится предложение GROUP BY

GROUP столбец1, столбец2…

При этом необходимо, чтобы один из столбцов возвращаемых оператором SELECT был представлен агрегатной функцией.

 

#1 Найти сред цену каждого товара

 

Select Name_tovar, avg (price)//сред значение столбца цена

From Tovar

Group by name_tovar

 

#2 Выдать общую стоимость каждого товара

 

Select Name_tovar, sum (price*kolvo) as TotalCost//столбец с общей стоимотью будет иметь имя TotalCost

From Tovar

Group by name_tovar

 

#3 Выдать общую стоимость товаров, поступивших на каждую дату, цена которых превышает 1000

Пусть в таблице товар Tovar есть поле дата поступления Data_in

 

Select data_in, sum (price*kolvo) as TotalCost

From Tovar

Where price >1000

Group by Data_in

 

Наложение ограничений на группировку записей

Если необходимо выдавать итоговые данные не по всем группам записей, а только по тем из них, которые удовлетворяют некоторому условию, то после GROUP BY указывают

 

HAVING <агрегатная функция – count,sum,max,min,avg> <отношение – это <=,>=,!= и т.д.> <значение – выражение вычисляемое или константа или вложенный оператор select который вернет одно значение>

 

В предложении HAVING обязательно должна присутствовать агрегатная функция, которую нельзя использовать в предложении WHERE

 

#4 Показать даты поступления товаров, на которые (на дату) количество поступившего товара было не меньше 500. В результирующий набор данных включить только те группы записей, по которым число таких поступлений было больше 1.

 

Select Data_in, count(*)//все записи считаем count – это функция– сколько всего товара, количество поступивших

From tovar

Where kolvo>500

Group by Data_in

Having count(*)>1

 

Name_tovar	Data_in	Kolvo
	23.10.16
	25.10.16
	23.10.16
	25.10.16

В результате запроса получим

 

Name_tovar	Data_in	Kolvo
	23.10.16
2	25.10.16	300
	23.10.16
	25.10.16

На 23.10.16 – 2 записи

На 5.10.16 - 1 запись

Учитываем только те, где число больше 1.

 

Задание сложных условий поиска

IS NULL

Если требуется выдавать записи столбец которых имеет либо не имеет пустое значение, применяется предложение

<значение-столбец> IS [NOT] NULL

 

#5 Выдать наименование товаров для которых не указано количество

Select nametovar from Tovar

Where kolvo is null