Часть 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 друг другу абсолютно аналогичны, если изменить порядок следования таблиц.
т.е. запросы:
FROM [BN].[dbo].[FLEET_Object] as O
left join bn.dbo.SYS_DEV_Device as D on d.id=o.ObjectID
FROM bn.dbo.SYS_DEV_Device as D
right join [BN].[dbo].[FLEET_Object] as O on d.id=o.ObjectID
дадут абсолютно идентичный друг-другу результат, так как в обоих случая приоритетной таблицей будет являться [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 |
|
| далее укажем как именно следует объединить наши две таблицы (равенство значений в колонках), после оператора on | SELECT *
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 bn | USE 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='тест' |
эти два запроса друг другу идентичны по результатам. Одинаковые таблицы выделены одинаковым цветом для наглядности их порядка.
