29.03.2024
Эффективная Навигация М2М
Меню сайта
Категории раздела
Учет топлива [6]
Статьи посвященные учету топлива
Прочие статьи [8]
SQL - учимся работать [5]
Форма входа
Вход через Google
Вход через Вконтакте
Вход через Facebook
Партнеры
Реклама

SQL: CyberFLeet: Занятие №2 сортировка, псевдонимы, фильтры часть 1/2

Занятие №2. Сортировка данных, фильтрация, расчетные значения. Часть 1/2

На прошлом занятии было рассказано, как с помощью меню и мышки создать простейший скрипт, сегодня рассмотрим как сортировать данные, более подробно рассмотрим фильтрацию.


Вы уже должны уметь запускать Management Studio, соединяться с SQL, пользоваться Обозревателем объектов, создавать простейший скрипт для получения записей из таблицы.

Попробуем, зная название таблицы написать поэтапно простеший скрипт получения данных из таблицы.
Для примера снова возьмем таблицу со списком приборов dbo.SYS_DEV_Device. При этом, данная таблица расположена в базе данных с названием BN. 

Соединимся с SQL с базой данных КиберФлита, и нажмем кнопку Создать запрос (или через меню Файл-Создать-Запрос в текущем соединении)

Описание - что делаемЧто написано в окне запроса
1. Раз любой запрос начинается со слова SELECT, то сначала пишем его SELECT
2. Так как мы не особо знакомы со списком столбцов, то ставим * (умножить) - это означает что нужно получить ВСЕ колонка из таблиц.SELECT *
3. теперь нужно указать из какой таблицы мы читаем данные, но сначала слово FROMSELECT * FROM
4. После FROM уже название таблицы, да еще и не забудем про название базы данных BNSELECT * 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. Вспомним, что таблица содержит не только видимые пользователем строки, но и уже удаленные. Для их удаления следует использовать фильтрацию. Напишем после запроса whereSelect * 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 bySelect * 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
Выполнить

SELECT TOP 1000 
       [num]
      ,[sensor_type_id]
      ,[IsAlarm]
      ,[description]
      ,code
      ,ObjectGosNum
      
  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

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

SELECT TOP 1000 
       [num]
      ,[sensor_type_id]
      ,[IsAlarm]
      ,[description]
      ,code
      ,ObjectGosNum
  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

Так как использованные столбцы имеют уникальные имена, то перед ними не обязательно писать указание имени или псевдонима таблицы. Если бы мы использовали псевдонимы, то было бы указано так:

SELECT TOP 1000 
      S.[num]
      ,S.[sensor_type_id]
      ,S.[IsAlarm]
      ,S.[description]
      ,D.code
      ,O.ObjectGosNum
  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. Начнем новый скрипт как всегда с SelectSelect
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 (более подробно описано тут)
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, уроки, обучение
Похожие материалы
Категория: SQL - учимся работать | Добавил: logoff (10.03.2012) | Автор: Бондарь Михаил W
Просмотров: 3585 | Теги: скрипты, Уроки, CyberFleet, SqL, SQL-Учимся работать | Рейтинг: 5.0/1
Всего комментариев: 0
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Поиск
TOP 10 Популярное
1 Оборудование> Гранит-Навигатор 4.14
2 Оборудование> Гранит-Навигатор 4.14: Настройка терминала
3 Оборудование> Абонентский терминал M2M Cyber GLX
4 Прочее> "Красные" против "Синих" - статистка космических запусков России (СССР) и США
5 Оборудование> M2M Cyber GLX: отправка команд
6 CyberFleet, CrossPoint> CyberFleet: Переустановка CyberFleet
7 CyberFleet, CrossPoint> КиберФлит: Учет топлива при помощи датчиков
8 Оборудование> M2M Cyber GLX: использование терминальных программ для снятия логов работы терминала
9 Прочее> SQL: CyberFleet: Занятие №3 Объединение таблиц Часть 1/2 (теория, inner join)
10 CyberFleet, CrossPoint> КиберФлит: Причины расхождения пробега по данным одометра и карте
Наш опрос
Ваши затраты на оборудование 1-го ТС тахографом составили
Всего ответов: 13
LogOff © 2024
Сайт создан в системе uCoz Рейтинг GPS Клуба. GPS навигаторы. GPS мониториг. GPS трекеры. ГЛОНАСС