Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 соединение таблиц не по уникальным полям  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
В базе две таблицы
table1 содержит записи платежей по договорам
table2 содержит записи по выплатам по этим же договорам
В table2 содержатся записи не на каждый договор с table1
В table1 содержатся все записи по договорам, указанным в table2
В каждой из таблиц может быть по несколько записей (проводок) для одного договора

Отловил случай, когда по определенному договору две записи в table1 и две записи в table2

Первоначально написал запрос
SELECT t1.contract, t1.sales, isnull(cast(t2.compens as nvarchar(10)),'') as 'Выплаты'
 FROM table1 t1 left join table2  t2 on t1.contract=t2.contract 

который вернул удвоенную сумму t1.sales

Потом исправил запрос и получил верный результат через вложенный запрос
SELECT t1.contract, t1.sales, (SELECT t2.compens FROM table2 t2 WHERE t1.contract=t2.contract)  as 'Выплаты'
 FROM table1 t1 left join table2  t2 on t1.contract=t2.contract 


В чем логика алгоритма действия сервера, которая приводит в задвоению данных? Не логичней было бы до сгруппировать по полю до соединения?
9 июл 15, 14:40    [17872511]     Ответить | Цитировать Сообщить модератору
 Re: соединение таблиц не по уникальным полям  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4902
2viper2viper
В чем логика алгоритма действия сервера, которая приводит в задвоению данных? Не логичней было бы до сгруппировать по полю до соединения?


Если вам надо сгруппировать, то логика состоит в том, чтобы использовать GROUP BY и тогда не будет задвоения.

В остальном любой A JOIN B по теории это это декартово произведение А x B на которое наложен фильтр.
9 июл 15, 15:05    [17872744]     Ответить | Цитировать Сообщить модератору
 Re: соединение таблиц не по уникальным полям  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21254
2viper2viper
В чем логика алгоритма действия сервера, которая приводит в задвоению данных? Не логичней было бы до сгруппировать по полю до соединения?

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

В данном случае надо группировать в подзапросах, и только потом, когда для каждого значения совокупности полей связывания останутся только уникальные записи, связывать. Не группировать в подзапросе, оставив это основному запросу, при этом можно только одну (желательно самую "левую") таблицу.
9 июл 15, 15:16    [17872870]     Ответить | Цитировать Сообщить модератору
 Re: соединение таблиц не по уникальным полям  [new]
_djХомяГ
Guest
Вообще ситуация непонятная
Сначала пишется что запрос вернул удвоенную сумму t1.sales
Затем во втором запросе (в отличии от первого) сделан subquery на другом поле as 'Выплаты'
И в итоге говорится что получен верный результат
9 июл 15, 15:31    [17873017]     Ответить | Цитировать Сообщить модератору
 Re: соединение таблиц не по уникальным полям  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
a_voronin,Akina
Правильно понял, что получить нужный мне результат (без задвоения в табл 1) можно только через вложенный запрос (второй вариант)?
9 июл 15, 15:48    [17873153]     Ответить | Цитировать Сообщить модератору
 Re: соединение таблиц не по уникальным полям  [new]
_djХомяГ
Guest
2viper2viper
a_voronin,Akina
Правильно понял, что получить нужный мне результат (без задвоения в табл 1) можно только через вложенный запрос (второй вариант)?

Ну во втором варианте можно тоже нарваться на ошибку - subquery возвращает более 1 значения
9 июл 15, 15:50    [17873167]     Ответить | Цитировать Сообщить модератору
 Re: соединение таблиц не по уникальным полям  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21254
2viper2viper
Правильно понял, что получить нужный мне результат (без задвоения в табл 1) можно только через вложенный запрос (второй вариант)?
Полуправильно. Ты не всё прочитал.
Akina
В данном случае надо группировать в подзапросах
9 июл 15, 16:00    [17873233]     Ответить | Цитировать Сообщить модератору
 Re: соединение таблиц не по уникальным полям  [new]
o-o
Guest
_djХомяГ
Вообще ситуация непонятная
Сначала пишется что запрос вернул удвоенную сумму t1.sales
Затем во втором запросе (в отличии от первого) сделан subquery на другом поле as 'Выплаты'
И в итоге говорится что получен верный результат

вот именно.
у ТС тоже жара и каша в голове.
если верить заявлению
"Отловил случай, когда по определенному договору две записи в table1 и две записи в table2",
то там не то что "верный", там никакой результат не получится:
declare @t1 table  (contract int,  sales int );
declare @t2 table  (contract int,  compens int );

insert into @t1 values (1, 10), (1, 20);
insert into @t2 values (1, 100), (1, 200);

SELECT t1.contract, t1.sales, (SELECT t2.compens FROM @t2 t2 WHERE t1.contract=t2.contract)  as 'Выплаты'
 FROM @t1 t1 left join @t2 t2 on t1.contract=t2.contract 

Msg 512, Level 16, State 1, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


а теперь про первый запрос.
он вот такое вернет:

contract sales Выплаты
1 10 100
1 10 200
1 20 100
1 20 200

а что, вы ему задали, какое именно надо?
может так?
1 10 100
1 20 200

или вот так?
1 10 200
1 20 100

надо сначало самому себе ответить, что надо и каков принцип связывания, а потом уже на сервер переваливать.
он вывалил все, что мог, выбирайте подходящее или задавайте четкое условие связывания строк
9 июл 15, 16:13    [17873316]     Ответить | Цитировать Сообщить модератору
 Re: соединение таблиц не по уникальным полям  [new]
o-o
Guest
Akina
2viper2viper
Правильно понял, что получить нужный мне результат (без задвоения в табл 1) можно только через вложенный запрос (второй вариант)?
Полуправильно. Ты не всё прочитал.
Akina
В данном случае надо группировать в подзапросах

про группировку у него в запросах ни слова.
а "до сгруппировать" -- это вообще не пойми что.
это "сгруппировать до"? или "досгруппировать"?
во втором случае ТС не группировать желает, а дубли выкинуть,
ну т.е. не надо ему сумму, а просто рядышком вывести платежи/выплаты.
и вот когда он сам поймет, какой платеж с какой выплатой надо "рядом" поместить,
может перестанет на зеркало пенять
9 июл 15, 16:18    [17873352]     Ответить | Цитировать Сообщить модератору
 Re: соединение таблиц не по уникальным полям  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21254
o-o
не надо ему сумму, а просто рядышком вывести платежи/выплаты

А он говорит - сумму... правда, сам получает фиг знает что.

Чтобы вывести, как ты говоришь, "платежи рядышком", нужно пронумеровать записи в обеих таблицах с группировкой по договору (ROW_NUMBER() OVER (PARTITION BY Order)), и по номеру договора плюс полученный номер связывать FULL OUTER JOIN.

Хотя как по мне, то этой ерундой должен заниматься клиент - отобразить итоги двух запросов так, как нужно.
9 июл 15, 16:36    [17873440]     Ответить | Цитировать Сообщить модератору
 Re: соединение таблиц не по уникальным полям  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
Возможно с примерными запросами перемудрил. Пытался объяснить логику ошибки

Вот реальный первый запрос, который вернул не верные значения
 SELECT 
  t.DateContract as'Дата_договора',
  t.lb3 as'ЦОК',
  t.ContractNumber as 'Договор',
  t.Client as 'Клиент',
  isnull(cast(sum(t.[76]) as nvarchar(20)),'') as'Начисленные СП',
  isnull(cast(sum(l.SumCompensation) as nvarchar(20)),'') as 'Возмещения',
  isnull(rtrim ( round(sum(l.SumCompensation)/ sum(t.[76])*100,1)) + '%','') as 'Коэф убыточности',
  isnull(cast(sum(k.SumKB) as nvarchar(20)),'') as 'КВ',
  isnull(rtrim (round(sum(k.SumKB)/ sum(t.[76])*100,1)) + '%','') as 'Коэф КВ',
  isnull( rtrim( round(sum(l.SumCompensation)/ sum(t.[76])*100+sum(k.SumKB)/ sum(t.[76])*100,1)) + '%','') as 'Коэф КВ+КУ'

     FROM [data].[dbo].[76] t
	 left join [data].[dbo].[loss_opl] l on t.ContractNumber=l.ContractNumber
	 left join [data].[dbo].[KB] k on t.ContractNumber=k.ContractNumber
  
)		
  GROUP BY  t.DateContract,t.lb3,t.ContractNumber,t.Client 


В результирующей таблице поле "Начисленные СП" получило по некоторым договорам удвоенную и утроенную сумму (в зависимости от количества записей (проводок) в таблице [data].[dbo].[76]

А вот второй, которой выгрузил ожидаемый результат
SELECT 
  t.DateContract as'Дата_договора',
  t.lb3 as'ЦОК',
  t.ContractNumber as 'Договор',
  t.Client as 'Клиент',
  sum(t.[76])as'Начисленные СП',
  (select sum(l.SumCompensation) from [data].[dbo].[loss_opl] l where  t.ContractNumber=l.ContractNumber) as 'Возмещения',
  (select sum(k.SumKB) from [data].[dbo].[KB] k where  t.ContractNumber=k.ContractNumber) as 'КВ'
 FROM [data].[dbo].[76] t
  GROUP BY  t.DateContract,t.lb3,t.ContractNumber,t.Client 
9 июл 15, 16:45    [17873505]     Ответить | Цитировать Сообщить модератору
 Re: соединение таблиц не по уникальным полям  [new]
o-o
Guest
2viper2viper
Возможно с примерными запросами перемудрил

ну да, агрегат выкинул, а мы гадаем, нужен он или нет.
а тогда уже все объяснено:
Akina
В данном случае надо группировать в подзапросах, и только потом, когда для каждого значения совокупности полей связывания останутся только уникальные записи, связывать. Не группировать в подзапросе, оставив это основному запросу, при этом можно только одну (желательно самую "левую") таблицу.
9 июл 15, 16:48    [17873528]     Ответить | Цитировать Сообщить модератору
 Re: соединение таблиц не по уникальным полям  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
Ага, спасибо всем за помощь
Прошу прощения что завел в заблуждение не точным примером
9 июл 15, 17:32    [17873780]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить