Объединить две таблицы, но из второй таблицы использовать только одну запись
От: SeLo  
Дата: 07.05.15 08:49
Оценка:
MS SQL. Есть две таблицы, связанных внешним ключом. Одной записи таблицы T1 могут соответствовать несколько записей из T2.

Нужно объединить две таблицы по внешнему ключу таким образом, что бы одной записи T1 соответствовала лишь одна запись из T2 (записи в T2 определенным образом выбираются и сортируются).
Как это сделать, чего-то не соображу сейчас. Спасибо
Re: Объединить две таблицы, но из второй таблицы использовать только одну запись
От: Dym On Россия  
Дата: 07.05.15 08:54
Оценка:
SL>MS SQL. Есть две таблицы, связанных внешним ключом. Одной записи таблицы T1 могут соответствовать несколько записей из T2.
SL>Нужно объединить две таблицы по внешнему ключу таким образом, что бы одной записи T1 соответствовала лишь одна запись из T2 (записи в T2 определенным образом выбираются и сортируются).
SL>Как это сделать, чего-то не соображу сейчас. Спасибо
Предположу, что надо сначала выбрать и отсортировать записи из таблицы Т2, а потом соединить с таблицей Т1:
 select ... from T1 inner join (select FK, ... from T2 where <определенный образ>) T3 on (T1.PK=T3.FK)
Счастье — это Glück!
Re[2]: Объединить две таблицы, но из второй таблицы использовать только одну зап
От: SeLo  
Дата: 07.05.15 09:07
Оценка:
Здравствуйте, Dym On, Вы писали:

SL>>MS SQL. Есть две таблицы, связанных внешним ключом. Одной записи таблицы T1 могут соответствовать несколько записей из T2.

SL>>Нужно объединить две таблицы по внешнему ключу таким образом, что бы одной записи T1 соответствовала лишь одна запись из T2 (записи в T2 определенным образом выбираются и сортируются).
SL>>Как это сделать, чего-то не соображу сейчас. Спасибо
DO>Предположу, что надо сначала выбрать и отсортировать записи из таблицы Т2, а потом соединить с таблицей Т1:
DO>
 select ... from T1 inner join (select FK, ... from T2 where <определенный образ>) T3 on (T1.PK=T3.FK)


Во внутреннем запросе я имею отсортированный нужным мне образом список. Нужно его ограничит до 1 записи на FK. Как? Иначе предложенным способок объединяются все записи из T3
Re[3]: Объединить две таблицы, но из второй таблицы использовать только одну зап
От: Dym On Россия  
Дата: 07.05.15 09:13
Оценка:
SL>Во внутреннем запросе я имею отсортированный нужным мне образом список. Нужно его ограничит до 1 записи на FK. Как? Иначе предложенным способок объединяются все записи из T3
Отсортированный как? Какую запись надо брать? Первую отсортированную? Тогда воспользоваться агрегатом max:
 select ... from T1 inner join (select FK, max(поле) as поле, ... from T2 where <определенный образ> group by ...) T3 on (T1.PK=T3.FK)
Счастье — это Glück!
Re: Объединить две таблицы, но из второй таблицы использовать только одну запись
От: Olaf Россия  
Дата: 07.05.15 09:19
Оценка:
Здравствуйте, SeLo, Вы писали:

SL>MS SQL. Есть две таблицы, связанных внешним ключом. Одной записи таблицы T1 могут соответствовать несколько записей из T2.


SL>Нужно объединить две таблицы по внешнему ключу таким образом, что бы одной записи T1 соответствовала лишь одна запись из T2 (записи в T2 определенным образом выбираются и сортируются).

SL>Как это сделать, чего-то не соображу сейчас. Спасибо

Для версии 2005+ можно сделать так...

1 вариант — одна сортировка
select *
from dbo.T1 t1
join
(
    select *, row_number() over(partition by t2.T1Id order by t2.Name) as n
    from dbo.T2 t2
) t2 on t1.T1Id = t2.T1Id
where t2.n = 1


2 вариант — две сортировки
select top 1 with ties * 
from dbo.T1 t1
join dbo.T2 t2 on t1.T1Id = t2.T1Id
order by row_number() over(partition by t2.T1Id order by t2.Name)


Необходимо конечно знать описание таблиц, но первый вариант производительней будет, на мой взгляд.
  Схема
create table dbo.T1(T1Id int identity(1, 1), name varchar(50), constraint PK_T1 primary key(T1Id))
go
create table dbo.T2(T2Id int identity(1, 1), T1Id int, name varchar(50))
go
alter table dbo.T2 add constraint FK_T2_T1 foreign key(T1Id) references dbo.T1(T1Id)
go

insert into dbo.T1(Name)
select 'T1' union all
select 'T2' union all
select 'T3'
go

insert into dbo.T2(T1Id, Name)
select 1, 'T1_1' union all
select 1, 'T1_2' union all
select 1, 'T1_3' union all
select 2, 'T2_1' union all
select 3, 'T3_1' union all
select 3, 'T3_2'
go
Re[4]: Объединить две таблицы, но из второй таблицы использовать только одну зап
От: SeLo  
Дата: 07.05.15 10:04
Оценка:
Здравствуйте, Dym On, Вы писали:

SL>>Во внутреннем запросе я имею отсортированный нужным мне образом список. Нужно его ограничит до 1 записи на FK. Как? Иначе предложенным способок объединяются все записи из T3

DO>Отсортированный как? Какую запись надо брать? Первую отсортированную? Тогда воспользоваться агрегатом max:
DO>
 select ... from T1 inner join (select FK, max(поле) as поле, ... from T2 where <определенный образ> group by ...) T3 on (T1.PK=T3.FK)


Извиняюсь, с группировками тут никак не выходило. Получилось как описанно в посте от Олаф, вариант 1
Re[2]: Объединить две таблицы, но из второй таблицы использовать только одну зап
От: SeLo  
Дата: 07.05.15 10:09
Оценка:
O>Для версии 2005+ можно сделать так...

O>1 вариант — одна сортировка

O>
O>select *
O>from dbo.T1 t1
O>join
O>(
O>    select *, row_number() over(partition by t2.T1Id order by t2.Name) as n
O>    from dbo.T2 t2
O>) t2 on t1.T1Id = t2.T1Id
O>where t2.n = 1
O>


O>2 вариант — две сортировки

O>
O>select top 1 with ties * 
O>from dbo.T1 t1
O>join dbo.T2 t2 on t1.T1Id = t2.T1Id
O>order by row_number() over(partition by t2.T1Id order by t2.Name)
O>


Использовал первый вариант, спасибо. Правда запрос такой нечитаемый получился (у меня сортировака пользовательская через Order By CASE WHEN и очень громоздкая).
А так — работает!
Re[3]: Объединить две таблицы, но из второй таблицы использовать только одну зап
От: Tigor Россия  
Дата: 07.05.15 16:57
Оценка:
Еще можно как-то так. Но не факт, что будет лучше по скорости.

select 
    *
from
    t1
cross apply
(
    select top 1
        *
    from
        t2 
    where
        t2.T1Id = t1.T1Id
    order by
        /* нужная сортировка */
) t
К сожалению, в действительности все выглядит иначе, чем на самом деле.
Re[4]: Объединить две таблицы, но из второй таблицы использовать только одну зап
От: Olaf Россия  
Дата: 08.05.15 04:39
Оценка: 48 (3)
Здравствуйте, Tigor, Вы писали:

T>Еще можно как-то так. Но не факт, что будет лучше по скорости.


T>
T>select 
T>    *
T>from
T>    t1
T>cross apply
T>(
T>    select top 1
T>        *
T>    from
T>        t2 
T>    where
T>        t2.T1Id = t1.T1Id
T>    order by
T>        /* нужная сортировка */
T>) t
T>


Тоже думал над этим вариантом, но засомневался, а зря. Cross apply будет давать сканирование/поиск + сортировку для каждой записи из dbo.T1. На первый взгляд схема обращения к данным не очень эффективная, но по факту все зависит от распределения данных в таблицах.

Рассмотрим две ситуации и три способа решения задачи.
1. Маленькое количество разделов (dbo.T1) и большое количество записей в каждом разделе (dbo.T2)
2. Большое количество разделов (dbo.T1) и маленькое количество записей в каждом разделе (dbo.T2)

  Три способа решения
/*1*/
select top 1 with ties * 
from dbo.T1 t1
join dbo.T2 t2 on t1.T1Id = t2.T1Id
order by row_number() over(partition by t2.T1Id order by t2.Name)

/*2*/
select *
from dbo.T1 t1
join
(
    select *, row_number() over(partition by t2.T1Id order by t2.Name) as n
    from dbo.T2 t2
) t2 on t1.T1Id = t2.T1Id
where t2.n = 1

/*3*/
select *
from dbo.T1 t1
cross apply
(
    select top 1 *
    from dbo.T2 t2
    where t1.T1Id = t2.T1Id
    order by t2.Name
) a


Первая ситуация
dbo.T1 = 100 записей, dbo.T2 = 200000 записей. В каждом из 100 разделов по 2000 записей

  План запроса


  Ввод-вывод
(100 row(s) affected)
Table 'T2'. Scan count 1, logical reads 533, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T1'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(100 row(s) affected)
Table 'T2'. Scan count 1, logical reads 533, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T1'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(100 row(s) affected)
Table 'T2'. Scan count 100, logical reads 930, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T1'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


  Время выполнения
(100 row(s) affected)

SQL Server Execution Times:
CPU time = 328 ms, elapsed time = 342 ms.

(100 row(s) affected)

SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 953 ms.

(100 row(s) affected)

SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 106 ms.


Вторая ситуация
dbo.T1 = 2000 записей, dbo.T2 = 200000 записей. В каждом из 2000 разделов по 100 записей

  План запроса


  Ввод-вывод
(2000 row(s) affected)
Table 'T2'. Scan count 1, logical reads 578, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T1'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2000 row(s) affected)
Table 'T2'. Scan count 1, logical reads 578, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T1'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2000 row(s) affected)
Table 'T2'. Scan count 2000, logical reads 6577, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T1'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


  Время выполнения
(2000 row(s) affected)

SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 472 ms.

(2000 row(s) affected)

SQL Server Execution Times:
CPU time = 234 ms, elapsed time = 968 ms.

(2000 row(s) affected)

SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 172 ms.


Итого

CROSS APPLY показал свою эффективность на обработке маленького количества разделов с большим содержанием записей, причем как по плану так и по времени выполнения, несмотря на наибольшее из трех вариантов количество операций ввода-вывода.

ROW_NUMBER() продемонстрировал себя лучше во второй ситуации, когда необходимо выбрать большое количество разделов с меньшим содержанием записей. В то время как у CROSS APPLY катастрофически выросли операции ввода-вывода.
Re: Объединить две таблицы, но из второй таблицы использовать только одну запись
От: MasterMind Россия  
Дата: 12.05.15 12:22
Оценка:
Здравствуйте, SeLo, Вы писали:

SL>MS SQL. Есть две таблицы, связанных внешним ключом. Одной записи таблицы T1 могут соответствовать несколько записей из T2.


SL>Нужно объединить две таблицы по внешнему ключу таким образом, что бы одной записи T1 соответствовала лишь одна запись из T2 (записи в T2 определенным образом выбираются и сортируются).

SL>Как это сделать, чего-то не соображу сейчас. Спасибо

Аналогичная проблема есть и у меня в MySQL — top-а нету.
Re[2]: Объединить две таблицы, но из второй таблицы использовать только одну зап
От: Olaf Россия  
Дата: 13.05.15 07:18
Оценка: 4 (1)
Здравствуйте, MasterMind, Вы писали:

MM>Аналогичная проблема есть и у меня в MySQL — top-а нету.


Вы можете в MySQL воспользоваться «искусственным» rownumber , например:
select *
from t1
join 
(
    select t2.*, 
        @rownumber := case when @T1Id = T1Id then @rownumber + 1 else 1 end as n,
        @T1Id := T1Id
    from T2 t2
    order by T1Id, Name
) t2 on t1.T1Id = t2.T1Id
where n = 1
Re[3]: Объединить две таблицы, но из второй таблицы использовать только одну зап
От: MasterMind Россия  
Дата: 13.05.15 09:30
Оценка:
Здравствуйте, Olaf, Вы писали:

O>Здравствуйте, MasterMind, Вы писали:


MM>>Аналогичная проблема есть и у меня в MySQL — top-а нету.


O>Вы можете в MySQL воспользоваться «искусственным» rownumber , например:

O>
O>select *
O>from t1
O>join 
O>(
O>    select t2.*, 
O>        @rownumber := case when @T1Id = T1Id then @rownumber + 1 else 1 end as n,
O>        @T1Id := T1Id
O>    from T2 t2
O>    order by T1Id, Name
O>) t2 on t1.T1Id = t2.T1Id
O>where n = 1
O>


Решение работает,но производительность запроса аналогична как если использовать distinct
Re: Объединить две таблицы, но из второй таблицы использовать только одну запись
От: MasterZiv СССР  
Дата: 14.05.15 16:58
Оценка: -1
Здравствуйте, SeLo, Вы писали:

SL>MS SQL. Есть две таблицы, связанных внешним ключом. Одной записи таблицы T1 могут соответствовать несколько записей из T2.


SL>Нужно объединить две таблицы по внешнему ключу таким образом, что бы одной записи T1 соответствовала лишь одна запись из T2 (записи в T2 определенным образом выбираются и сортируются).

SL>Как это сделать, чего-то не соображу сейчас. Спасибо

Это можно сделать путём написания SQL-запроса.
В JOIN таблиц T1 и T2 добавь условие выбора "лишь одной записи из T2" -- и всё.
 
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.