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

SQL: CyberFleet: Занятие №3 Объединение таблиц Часть 1/2 (теория, inner join)
 
Занятие №3 из цикла "SQL - Учимся писать скрипты" более подробно посвящено объединению связанных таблиц в одно целое в одном запросе.
Часть 1/2 - теория, использование inner join

На прошлых занятиях мы научились составлять простые запросы, их сортировать, использовать расчетные таблицы и псевдонимы. Теперь научимся как связанные таблицы объединять в единое целое.

И так, имеем две-три-четыре (не очень важно сколько) связанных по каким-то полям таблиц. Таблицы будут для нас связаны, в случае если значение в каком-либо столбце указывает (как гиперссылка в интернете) на значение в другой таблице (самый простой случай)
Так как сайт посвящен продуктам компании М2М Телематика, то и рассматривать наши связи будем на примерах базы данных CyberFleet.

В CyberFleet есть две базовые таблицы:
dbo.FLEET_Object - список транспорта, причем в колонке Device_ID содержится ссылка на запись в таблице SYS_DEV_Device
dbo.SYS_DEV_Device - список абонентских терминалов

Для связи двух таблиц они должны быть объеденные в запросе посредством оператора join. Так же серверу следует сказать, как именно данные таблицы связаны (по значению каких колонок), указать метод их объединения/соединения.
При объединении таблиц используется такой принцип синтаксиса:

select ...
from <Таблица1>
<join> <Таблица2> on <УсловияОбъединения>

где: <join>  - тип соединения ("inner join", "left/right join", "full join", "cross join"). Если тип не указан (стоит просто join), то используется inner join
<УсловияОбъединения> - условия, по которым должны объединяться таблицы
Например: 
  • равенство значений в 1 или больше колонках (Таблица1.Колонка1=Таблица2.Колонка1 and  Таблица1.Колонка2=Таблица2.Колонка2)
  • Различные сочетания условий

Описание типов соединения:
inner join Соединение, при котором парная строка (подходящая под условие) должна быть в обоих таблицах. Т.е. если для какой-либо строки в таблице не находится подходящей по условиям ей пары в другой таблице, то данная строка будет удалена из результата!
left joinСоединение, при котором будут включены в результат все строки "левой" таблицы (той что перед left join), и только парные строки из "правой" таблицы. Т.е. левая таблица является более приоритетной! Если для правой таблицы не будет найдено "пары", то значения колонок в результирующей таблице будут заменены на null
right join тоже самое что и left join, за исключением того, что приоритетной таблицей является "правая" (та, что после right join по тексту запроса), а не левая. Два этих соединения могут легко заменят друг друга, если в запросе поменять порядок следования таблиц
full join Соединение, при котором будут включены в результат все строки "левой" таблицы (той что перед left join), и и все строки правой таблицы. При этом, если для какой-то строки не найдена по условия пара, то значения в соответствующих колонках будут заменены на null. Данный типа как бы является "объединение" двух предыдущих методов (т.е. приоритет между левой и правой таблицей отсутствует), и антиподом метода Inner join, при котором строки без пары будут исключены! 
cross join  Соединение, при котором результатом будет "произведение" таблиц (для каждой строки таблицы 1 в качестве пары будет все строки таблицы 2). При использовании условия равенства значений в колонках таблице друг другу (в стиле where Таблица1.Колонка=Таблица2.Колонка) соединение идентично Inner join (является устаревшей формой). Так же устаревшей формой является просто перечисление таблиц после from (from Таблица1, Таблица2). при использовании данного типа указание  on <УсловияОбъединения>  не требуется.

Наиболее "жестким" в плане попадающих в набор таблиц является тип соединения inner join, так как в этом случае каждой строке из таблицы должна соответствовать минимум одна строка из другой таблицы. И если для строки парной ей не найдено, то в результирующий список она не будет включена. Данный вид соединения (имхо) так же является и наиболее распространенным.

Соединения left join, right join друг другу абсолютно аналогичны, если изменить порядок следования таблиц.
т.е. запросы:
SELECT o.*,d.*
  FROM [BN].[dbo].[FLEET_Object] as O
  left join bn.dbo.SYS_DEV_Device as D on d.id=o.ObjectID
GO

и

SELECT o.*,d.*
  FROM bn.dbo.SYS_DEV_Device as D
  right join [BN].[dbo].[FLEET_Object] as O on d.id=o.ObjectID
GO

дадут абсолютно идентичный друг-другу результат, так как в обоих случая приоритетной таблицей будет являться [BN].[dbo].[FLEET_Object]

В свою очередь, использование типа cross join, совместно с указанием в условии равенства (или других условий) значений в колонках таблиц будет сервером интерпретировано как тип Inner join

например, запрос:
SELECT o.*,d.*
  FROM bn.dbo.SYS_DEV_Device as D
  cross join [BN].[dbo].[FLEET_Object] as O 
  where d.id=o.ObjectID
GO

или, тоже самое (устаревшая форма):

SELECT o.*,d.*
  FROM bn.dbo.SYS_DEV_Device as D, [BN].[dbo].[FLEET_Object] as O 
  where d.id=o.ObjectID
GO

идентичны запросу:

SELECT o.*,d.*
  FROM bn.dbo.SYS_DEV_Device as D
  inner join [BN].[dbo].[FLEET_Object] as O on d.id=o.ObjectID
GO

Замечу, что использование формы cross join, вместо перечисления списка таблиц после from является более понятной формой записи. Сравнивая же формы cross join и inner join, отметим, что inner join читается проще, так как способ соединения таблиц (условия после on) явно указан после самой таблицы, что позволяет быстрее разбираться в коде.

После объединения таблиц, они уже составляют новую виртуальную таблицу, с которой так же можно связывать еще одну таблицу, указывая в качестве условий значения из уже существующих таблиц, и так далее. Каждый раз новая таблица связывается со всеми прочими (или одной из них).

В качестве практики начнем с простого запроса и объединения трех таблиц: список ТС, список АТ, данные о последних координатах (sys_Dev_ArchiveData) (добавляемые в запрос строки буду выделять цветом). Целью запроса будет получение данных о гос номере, номере прибора, телефоне, и дате последней координаты

что делаемтекст запросаВыполнить
Любой запрос начинается с оператора select, далее укажем все столбы (звездочка)SELECT *
далее укажем первую таблицу  - список транспортных средств, с псевдонимом O. Далее можно будет использовать ссылка на колонки данной таблицы через этот псевдонимSELECT *
FROM [BN].[dbo].[FLEET_Object] as O
Выполнить! 
В результате на экране полный список всех данных в таблице

Теперь объединим наши ТС со списком Приборов. начнем с Inner join и названия таблицы приборовSELECT * 
FROM [BN].[dbo].[FLEET_Object] as O 
inner join [BN].dbo.SYS_DEV_Device as d

далее укажем как именно следует объединить наши две таблицы (равенство значений в колонках), после оператора onSELECT * 
FROM [BN].[dbo].[FLEET_Object] as O 
inner join [BN].dbo.SYS_DEV_Device as d
on o.Device_id=D.id
Выполнить! 
В результате на экране список всех строк, для которых нашлась пара по указанному нам условию поиска пар строк o.Device_id=d.id

Теперь добавим третью таблицу sys_Dev_Currentdata, при этом, в качестве условия связи укажем две колонки сразу (т.е. через and) SELECT * 
FROM [BN].[dbo].[FLEET_Object] as O 
inner join [BN].dbo.SYS_DEV_Device as d
on o.Device_id=D.id
inner join BN.dbo.Sys_Dev_CurrentData as CD
on CD.ObjectID=O.ObjectID and CD.Device_ID=O.Device_ID
Выполнить! 
Оставим только нужные нам столбцы, заменив звездочку после select на нужный нам список столбцов (не забыв указать перед именем столбца псевдоним таблицы)SELECT O.ObjectGosNum, D.code,D.tel_number,CD.tele_time 
FROM [BN].[dbo].[FLEET_Object] as O 
inner join [BN].dbo.SYS_DEV_Device as d
on o.Device_id=D.id
inner join BN.dbo.Sys_Dev_CurrentData as CD
on CD.ObjectID=O.ObjectID and CD.Device_ID=O.Device_ID

Выполнить! 

И так, у нас получилось соединить три таблицы в "единый организм". К выдаваемым данным уже можно применять фильтры, или группировки.

Попробуем еще решить другую задачу - получить для конкретной группы (фильтр по названию группы "тест" - замените "тест" на ваше название) список ТС (гос номеров, серийных номеров приборов), с указанием для каждого времени последних координат. Группу будет искать по имени.

Сначала нужно определиться с таблицами:
1. гос номер хранится в таблице FLEET_Object, колонка ObjectGosNum
2. Группа хранится в таблице FLEET_ObjectGroupe, в колонке ObjectGroupeName - имя группы
3. сам состав группы хранится в таблице FLEET_ObjectRelation (ссылка на ObjectID)
4. данные для каждого ТС о последнем времени связи хранятся в таблице Sys_Dev_CurrentData, в колонке tele_time
5. Ну а номер прибора хранится в таблице SYS_DEV_Device, в колонке code

и так, нам придется соединить 5 таблиц


что делаемтекст запросаВыполнить
Любой запрос начинается с оператора select, список колонок пропустим, и сразу перейдем к from. Что бы каждый раз не писать название базы данных используем use bnUSE BN
SELECT 
FROM

так как мы для связи мы будем использовать тип inner join, то последовательность таблиц не важна, главное не ссылаться на еще не описанные таблицы. Сначала сделаем их общий список, а потом уже последовательно привяжем друг к другуUSE BN
SELECT 
FROM [FLEET_Object] as O
inner join SYS_DEV_Device as d 
inner join Sys_Dev_CurrentData as CD
Inner join 
FLEET_ObjectRelation as ORel
inner join
FLEET_ObjectGroupe as OG

Теперь для каждой таблицы укажем после оператора on как она будет связана с другими таблицами (в нашем случае это равенство значений в соответствующих столбцах)USE BN 
SELECT 
FROM [FLEET_Object] as O
inner join SYS_DEV_Device as d on O.Device_id=d.id
inner join Sys_Dev_CurrentData as CD
on O.ObjectID=CD.ObjectID
Inner join FLEET_ObjectRelation as ORel 
on ORel.ObjectID=O.ObjectID
inner join FLEET_ObjectGroupe as OG 
on OG.ObjectGroupeID=ORel.ObjectGroupeID

Теперь определимся со список столбцовUSE BN 
SELECT O.
ObjectGosNum, d.code,cd.tele_time
FROM [FLEET_Object] as O
inner join SYS_DEV_Device as d on O.Device_id=d.id
inner join Sys_Dev_CurrentData as CD 
on O.ObjectID=CD.ObjectID
Inner join FLEET_ObjectRelation as ORel 
on ORel.ObjectID=O.ObjectID
inner join FLEET_ObjectGroupe as OG 
on OG.ObjectGroupeID=ORel.ObjectGroupeID
Выполнить 
И завершим скрипт фильтрации по названию группы "тест"USE BN 
SELECT O.
ObjectGosNum, d.code,cd.tele_time
FROM [FLEET_Object] as O
inner join SYS_DEV_Device as d on O.Device_id=d.id
inner join Sys_Dev_CurrentData as CD 
on O.ObjectID=CD.ObjectID
Inner join FLEET_ObjectRelation as ORel 
on ORel.ObjectID=O.ObjectID
inner join FLEET_ObjectGroupe as OG 
on OG.ObjectGroupeID=ORel.ObjectGroupeID
where OG.ObjectGroupeName='тест'
Выполнить 


Так как мы использовали тип соединения Inner join, то порядок описания таблиц можно поменять, без потери результата.
Так, например, таблицы SYS_DEV_Device, Sys_Dev_CurrentData,FLEET_ObjectRelation связываются с FLEET_Object, потому их порядок описания можно поменять без ущерба для скрипта. 
Общий принцип - ссылка на колонку таблицы УЖЕ должна быть описана, до ее использования (т.е. стоять раньше в описании скрипта). Потому нельзя поменять последние две таблицы местами

Inner join FLEET_ObjectRelation as ORel on ORel.ObjectID=O.ObjectID
inner join FLEET_ObjectGroupe as OG on OG.ObjectGroupeID=ORel.ObjectGroupeID

, так как  FLEET_ObjectGroupe as OG не может быть напрямую связана с FLEET_Object as O, кроме как через FLEET_ObjectRelation as ORel
С другой стороны, ничего не мешает нам изменить порядок следования таблиц на обратный (выбранный тип соединения inner join это позволяет сделать):

USE BN 
SELECT O.
ObjectGosNum, d.code,cd.tele_time
FROM [FLEET_Object] as O
inner join SYS_DEV_Device as d on O.Device_id=d.id
inner join Sys_Dev_CurrentData as CD 
on O.ObjectID=CD.ObjectID
Inner join FLEET_ObjectRelation as ORel 
on ORel.ObjectID=O.ObjectID
inner join FLEET_ObjectGroupe as OG 
on OG.ObjectGroupeID=ORel.ObjectGroupeID
where OG.ObjectGroupeName='тест'
USE BN 
SELECT O.ObjectGosNum, d.code,cd.tele_time
FROM FLEET_ObjectGroupe as OG 
inner join FLEET_ObjectRelation as ORel
on OG.ObjectGroupeID=ORel.ObjectGroupeID
inner join Sys_Dev_CurrentData as CD 
on ORel.ObjectID=CD.ObjectID
Inner join  [FLEET_Object] as O
on ORel.ObjectID=O.ObjectID
inner join SYS_DEV_Device as d on O.Device_id=d.id
where OG.ObjectGroupeName='тест'

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






Источник: SQL, объединение таблиц, join, скрипты SQL, Уроки, CyberFleet
Похожие материалы
Категория: SQL - учимся работать | Добавил: logoff (13.04.2012) | Автор: Бондарь Михаил W
Просмотров: 7643 | Теги: SqL, SQL-Учимся работать, объединение таблиц, join table, скрипты, Уроки, CyberFleet | Рейтинг: 0.0/0
Всего комментариев: 0
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Поиск
TOP 10 Популярное
1 Оборудование> Гранит-Навигатор 4.14
2 Оборудование> Абонентский терминал M2M Cyber GLX
3 Оборудование> Гранит-Навигатор 4.14: Настройка терминала
4 Прочее> "Красные" против "Синих" - статистка космических запусков России (СССР) и США
5 CyberFleet, CrossPoint> CyberFleet: Переустановка CyberFleet
6 CyberFleet, CrossPoint> КиберФлит: Учет топлива при помощи датчиков
7 Оборудование> M2M Cyber GLX: отправка команд
8 Прочее> SQL: CyberFleet: Занятие №3 Объединение таблиц Часть 1/2 (теория, inner join)
9 CyberFleet, CrossPoint> КиберФлит: Причины расхождения пробега по данным одометра и карте
10 CyberFleet, CrossPoint> КиберФлит: Подключаем карту Google
Наш опрос
Что вы используете чаще всего для удаленного доступа к рабочему столу?
Всего ответов: 20
LogOff © 2017
Сайт создан в системе uCoz Рейтинг GPS Клуба. GPS навигаторы. GPS мониториг. GPS трекеры. ГЛОНАСС