Занятие №2. Сортировка данных, фильтрация, расчетные значения. Часть 1/2
На прошлом занятии было рассказано, как с помощью меню и мышки создать простейший скрипт, сегодня рассмотрим как сортировать данные, более подробно рассмотрим фильтрацию.
Вы уже должны уметь запускать Management Studio, соединяться с SQL, пользоваться Обозревателем объектов, создавать простейший скрипт для получения записей из таблицы.
Попробуем, зная название таблицы написать поэтапно простеший скрипт получения данных из таблицы. Для примера снова возьмем таблицу со списком приборов dbo.SYS_DEV_Device. При этом, данная таблица расположена в базе данных с названием BN.
Соединимся с SQL с базой данных КиберФлита, и нажмем кнопку Создать запрос (или через меню Файл-Создать-Запрос в текущем соединении)
| Описание - что делаем | Что написано в окне запроса | | 1. Раз любой запрос начинается со слова SELECT, то сначала пишем его
| SELECT | | 2. Так как мы не особо знакомы со списком столбцов, то ставим * (умножить) - это означает что нужно получить ВСЕ колонка из таблиц. | SELECT * | | 3. теперь нужно указать из какой таблицы мы читаем данные, но сначала слово FROM | SELECT * FROM | | 4. После FROM уже название таблицы, да еще и не забудем про название базы данных BN | SELECT * FROM BN.DBO.SYS_DEV_DEVICE | | 5. Регистр букв (большие/малые) при написании запросов не важен, главное что бы такие названия были в базе данных, а слова известны SQL. например такой скрипт выдаст точно тоже самое что и предыдущий. При дальнейшей работе, конечно гораздо удобнее писать навания таблиц, столбцов с выделением регистром первых букв "слов" в названии. | Select * from BN.dbo.Sys_Dev_Device | | Так же названия объектов из базы данных могут как быть в квадратных скобках, так и не быть (однако, в большинстве случаев лучше использовать скобки). | Select * from [BN].[dbo].[Sys_Dev_Device] | | Скрипт можно как писать в одну строку, так и в несколько. Чаще удобнее писать в несколько строк, так как человеку так проще ориентироваться. | Select
*
from [BN].[dbo].[Sys_Dev_Device]
|
Теперь запустим скрипт на выполнение (нажмите Выполнить, или F5 на клавиатуре).
В результате появятся все строки, содержащиеся в таблице со списком ваших абонентских терминалов.
| 6. Вспомним, что таблица содержит не только видимые пользователем строки, но и уже удаленные. Для их удаления следует использовать фильтрацию. Напишем после запроса where | Select * from BN.dbo.Sys_Dev_Device
where |
| | 7. После слова where следует указать одно или более условий, связав при этом их через логические операторы. Пока укажем фильтр только по видимым пользователем приборам (колонка DeviceActive должна быть равна 1). | Select * from BN.dbo.Sys_Dev_Device
where DeviceActive=1 | Выполнить | | 8. Если присмотреться к списку, то видно, что список идет в порядке возрастания номеров ID. Обычно это не очень удобно. Давайте отсортируем данные по колонке type_id (содержит ссылку на тип прибора), а внутри уже по code. Любое указание сортировки начинается с order by | Select * from BN.dbo.Sys_Dev_Device
where DevicActive=1
order by |
| | 9. После этого указывается одна или несколько колонок, по которым следует сортировать таблицу (разделяя через запятые). | Select * from BN.dbo.Sys_Dev_Device
where DeviceActive=1
order by type_id, code | Выполнить | | 10. Присмотревшись к таблице, увидим, что все строки идут в определенном порядке - сначала по возрастанию type_id, а потом по code. Периодически приходится получать данные в обратном порядке, для этого нужно в операторе order by, после названия колонки дописать desc (перед запятой) | Select * from BN.dbo.Sys_Dev_Device
where DeviceActive=1
order by type_id, code desc | Выполнить |
Наш скрипт выводит таблицу о всех активных приборах, при этом она идет в порядке возрастания значения колонки type_id, и порядке убывания колонки code.
В дальнейшем нам потребуются не все колонки из таблицы, потому изменим скрипт | 11. Укажем конкретные колонки, для этого удалим после Select "звездочку", и вместо нее перечислим колонки через запятую. | Select
type_id, code, tel_number, DTReg
from BN.dbo.Sys_Dev_Device
where DeviceActive=1
order by type_id, code desc | Выполнить | | 12. Приведем наши колонки к более понятному виду, заменим их псевдонимами. Псевдонимы пишутся после названия колонок, через пробел, или через слово as. Если псевдоним состоит из двух раздельных слов, то его берут в квадратные кавычки. Псевдонимы можно использовать в указании сортировки. | Select
type_id as Тип, code Код,
tel_number as [Телефон],
DTReg [Дата регистрации]
from BN.dbo.Sys_Dev_Device
where DeviceActive=1
order by type_id, Код desc
| Выполнить
| | 12. Так же псевдонимы можно использовать для таблиц, при этом обращение к колонкам будет идти через точку после псевдонима. Это очень удобно, когда идет работа с несколькими таблицами в одном запросе. В качестве псевдонимов принято использовать первые буквы слов, составляющих название таблицы. Если название колонки уникально для всех таблиц, то ее можно писать без указания имени таблицы. Так как у нас в запросе только одна таблица, то указание имени таблицы не обязательно. Обращение же к колонкам через псевдонимы не требуется указания уже имени таблицы (или ее псевдонима). | Select
SDD.type_id as Тип, SDD.code Код,
SDD.tel_number as [Телефон],
SDD.DTReg [Дата регистрации]
from BN.dbo.Sys_Dev_Device as SDD
where SDD.DeviceActive=1
order by SDD.type_id, Код desc | Выполнить |
FROM [BN].[dbo].[SYS_DEV_Sensor]
inner join [bn].[dbo].[SYS_DEV_Device] on [BN].[dbo].[SYS_DEV_Sensor].[device_id]=[bn].[dbo].[SYS_DEV_Device].[id]
inner join [bn].[dbo].[FLEET_Object] on [BN].[dbo].[SYS_DEV_Sensor].[device_id]=bn.dbo.FLEET_Object.Device_ID
можно было бы переписать короче, через использование псевдонимов
FROM [BN].[dbo].[SYS_DEV_Sensor] as S
inner join [bn].[dbo].[SYS_DEV_Device] as D on S.[device_id]=D.[id]
inner join [bn].[dbo].[FLEET_Object] as O on S.[device_id]=O.Device_ID
Так как использованные столбцы имеют уникальные имена, то перед ними не обязательно писать указание имени или псевдонима таблицы. Если бы мы использовали псевдонимы, то было бы указано так:
FROM [BN].[dbo].[SYS_DEV_Sensor] as S
inner join [bn].[dbo].[SYS_DEV_Device] as D on S.[device_id]=D.[id]
inner join [bn].[dbo].[FLEET_Object] as O on S.[device_id]=O.Device_ID
В нашем скрипте, мы использовали простой фильтр по активности прибора, давайте попробуем усложнить наш фильтр,так, что бы одновременно добавилась колонка с первыми 4-мя цифрам из номера телефона.
Сначала для этого научимся отделать часть номера. Для этого, нам потребуется функция SubString. Создайте новое пустое окно запроса.
| 1. Начнем новый скрипт как всегда с Select | Select |
| | 2. После напишем '89107511105' | Select '89107511105' | Выполнить |
В результате выполнения у нас будет табличка, с одной колонкой и одной строкой. Попробуем во второй колонке отобразить часть символов.
| 3. добавим в наш запрос еще одну колонку SubString ('89107511105',1,4) - это команда от указанного значения строки '89107511105' "отрезать" 4-е символа, начиная с 1-го | Select '89107511105',
SUBSTRING ('891075111050',1,4) | Выполнить
|
Попробуем теперь с нашим запросом списка приборов сделать тоже самое. Добавим колонку выбора первых 4-ех символом из номера телефона
| 14. Добавляем колонку, вместо прямого указания номера телефона пишем ссылку на колонку. Получается SubString (SSD.tel_number,1,4), с указанием псевдонима "Оператор" | Select
SDD.type_id as Тип, SDD.code Код,
SDD.tel_number as [Телефон],
SDD.DTReg [Дата регистрации],
SubString (SDD.tel_number,1,4) as Оператор
from BN.dbo.Sys_Dev_Device as SDD
where SDD.DeviceActive=1
order by SDD.type_id, Код desc
| Выполнить |
Примерно то, что будите видеть на экране приведено на рисунке: 
| На рисунке видно, как примерно выглядят наши данные. Теперь попробуем добавить информацию о Названии сети (отдельной колонкой). Для этого оператора.
Для этого у нас есть два варианта:
1. Использовать оператор выбора CASE, в стиле Если Оператор=8915, то СЕТЬ=МТС, Если Оператор=8910 то СЕТЬ=МТС, Если Оператор=8915 то СЕТЬ=Билайн
2. Создать временную таблицу с двумя колонками (Код сети, СЕТЬ), заполнить ее данными (8910, МТС; 8915,МТС; 8905, Билайн), и присоединить ее к нашей таблице. Опишем вариант через оператор условного выбора значения. |
Вариант 1: Оператор выбора CASE нам нужно добавить колонку, потому посл "Оператор", ставим запятую, и пишем :
case SubString (SDD.tel_number,1,4) when '8910' then 'МТС' when '8915' then 'МТС' when '8970' then 'Билайн' else null end as Сеть
Должно получиться такое (добавленные строки выделены цветом)
Select SDD.type_id as Тип, SDD.code Код, SDD.tel_number as [Телефон], SDD.DTReg [Дата регистрации], SubString (SDD.tel_number,1,4) as Оператор, case SubString (SDD.tel_number,1,4) when '8910' then 'МТС' when '8915' then 'МТС' when '8970' then 'Билайн' else null end as Сеть from BN.dbo.Sys_Dev_Device as SDD where SDD.DeviceActive=1 order by SDD.type_id, Код desc | В "переводе на русский" это будет значить примерно так:
Выбрать все строки (select) (отсутствует top)
из таблицы (from) dbo.Sys_dev_Device базы данных BN,
для которых (where) в колонке DeviceActive=1,
строки упорядочить (order by) в порядке следования
данных в колонках type_id, Код (по убыванию)
Список колонок:
type_id как Тип, code как Код, tel_number как Телефон, DTReg как Дата регистрации
так же вставить колонку Оператор, содержащую 4 первых символа из колонки tel_number
и колонку Сеть, в которой указать:
если первые 4 символа из телефона = 8910, то МТС
если первые 4 символа из телефона = 8915, то МТС
если первые 4 символа из телефона = 8970, то Билайн
в прочих случаях - null |
case ЧтоПроверяем when условие1 then значение1 when условие2 then значение2 ... when условиеN then значениеN else ЗначениеПрочее end | В переводе на русский:
блок начинается словом case, и завершается end (как скобки, или кавычки в предложении)
между ними пишутся условия и соответствующие им значения
для выбранной простой формы case: после case пишем значение, которое будем проверять
далее when - и значение которое должно быть, потом then и значение которое нужно вывести
для всех не описанных случаев можно вставить else и значение.
Блоков when ... then может быть сколько угодно.
блок else - не обязателен |
Количество блоков when.. then может быть любым, вы сами можете вставить эти блоки, дополнив информацию нужными вам сочетаниями.
Источник: SQL, сортировка, фильтры, псевдонимы, CyberFleet, уроки, обучение |
