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

↑

▷ Содержание

Стр 1 из 3Следующая ⇒

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

Когда требуется получать агрегированные (итоговые) данные не по всему результирующему набору данных (НД), а по каждой из входящих в него групп записей, которые характеризуются одинаковым значением какого-либо столбца, после предложения 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

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

На 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. Хранит название товара и его кол-во

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

Выдать из таблице расход дату расхода, наименование товара, стоимость отпущенного товара, при этом учитывать только те записи, в которых стоимость отпущенного товара больше 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

B

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

Когда требуется получать агрегированные (итоговые) данные не по всему результирующему набору данных (НД), а по каждой из входящих в него групп записей, которые характеризуются одинаковым значением какого-либо столбца, после предложения 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

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

На 23.10.16 – 2 записи

На 5.10.16 - 1 запись

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

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

IS NULL

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

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

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

Select nametovar from Tovar

Where kolvo is null

Познавательные статьи:



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

Типичные ошибки при выполнении бросков в баскетболе

Принятие христианства на Руси и его значение

Средства массовой информации США