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

Откуда:
Сообщений: 4
Здравствуйте ! Появилась задача по работе сделать отчет по вкладам, запросы и таблицы какие надо дернул в режиме дебага софта.
Запрос почти готов, но вот незадача, если договор продлялся, то по этому запросу будет выдано 2 одинаковых договора, с разной датой открытия и закрытия. При этом необходимо чтоб договор выводился только один, но при этом дата открытия должна быть у более старого.
С датой открытия решил, но теперь не могу сообразить, как сделать чтоб у меня в выборку попал лишь один договор. С этим прошу помощи, заранее спасибо.
В колонку lol записал дату открытия вклада которая мне необходима, в колонке opendate дата открытия вклада и дата его как бы "переоткрытия" при пролонгации.
select cu.Number                                   as Valuta,
       Prolong = case when (ROW_NUMBER() OVER(PARTITION BY d.number  ORDER BY d.dealdate ASC)) > 1 then (select min(fixdate) from tdeal  where d.Number = Number) else 0 end,
	   lol = case when (ROW_NUMBER() OVER(PARTITION BY d.number  ORDER BY d.dealdate ASC)) > 1 then (select min(dealdate) from tdeal where d.Number = Number) else 0 end,
       inst.Name+' '+inst.Name1+' '+inst.Name2     as Name,
	   tal.BriefAccount                            as AccNumber,
       d.dealdate                                  as Opendate,
	   d.fixdate                                   as CloseDate,
	   round(rl.Rest * sign(rl.Rest),2)            as Rest,
	   round(rl.RestBs * sign(rl.RestBs),2)        as RestBs,
	   d.course                                    as Stavka,
	   isnull(d.RegistPayType, 0)                  as SrokVklada,
	   isnull(d.Number, isnull(d.Number, ''))      as dlDealNumber,
	   i.Brief                                     as InstrumentBrief,
	   NesnOstVal = case 
when cu.Number = '840' then case when ltrim(rtrim(ul.Vid)) = 'ТвНаграда' then ul.Qty*@usd else ul.NesnOstVal*@usd end
when cu.Number = '978' then case when ltrim(rtrim(ul.Vid)) = 'ТвНаграда' then ul.Qty*@eur else ul.NesnOstVal*@eur end
when cu.Number = '810' then case when ltrim(rtrim(ul.Vid)) = 'ТвНаграда' then ul.Qty else ul.NesnOstVal end
else case when ltrim(rtrim(ul.Vid)) = 'ТвНаграда' then ul.Qty else ul.NesnOstVal end
end,
       NesnOst = case when ltrim(rtrim(ul.Vid)) = 'ТвНаграда' then ul.FirstQtyRur else ul.NesnOst end
  from tDPTAccountLink tal WITH (NOLOCK index=XIE1tDPTAccountLink)
 inner join tDPT_RuleAcc_Sync il WITH (NOLOCK index=XPKtDPT_RuleAcc_Sync)
         on tal.RuleAccID = il.RuleAccID
        and il.ObjectType = 1
 inner join tDPT_FinOper_Sync i WITH (NOLOCK index=XPKtDPT_FinOper_Sync)
         on i.FinOperID = il.ObjectID
 left outer join tDeal d  WITH (NOLOCK index=XPKtDeal)
         on d.DealID       = tal.ObjectID
        and d.InstrumentID = il.ObjectID
		left join tInstitution inst
		on d.InstitutionID = inst.InstitutionID
		 left join tDPT_Currency_Sync cu WITH (NOLOCK index=XPKtDPT_Currency_Sync)
           on cu.CurrencyID = d.FundID
		   left join pResList  rl  WITH (NOLOCK index=XPKpResList)
		   on rl.SPID      = @@SPID
		   left join tResource r   WITH (NOLOCK index=XPKtResource)
		   on r.ResourceID = rl.ResourceID
		   left join tCurrency  c  WITH (NOLOCK index=XPKtCurrency)
		   on c.CurrencyID = r.FundID
		   left join tUser         WITH (NOLOCK index=XAK0tUser)
		   on tUser.Brief  = convert(char(30),suser_sname())
		   left join #result ul
		   on ul.Number = d.Number
		   /*left join tDeal d1 WITH (NOLOCK index=XPKtDeal)
		   on  d1.dealdate > d.dealdate and d.number =d1.number*/
		   where RL.SPID = @@SPID and
tal.AccountID = RL.ResourceID
and ul.brief != 'NULL'
and il.Brief='Депозит'
and d.fixdate >= '20161014'

На рисунке результат запроса. Отметил красным дубли по номеру договора и их даты, получается дубль с более старой датой мне надо исключить, при этом более старую дату открытия мне необходимо будет указать в оставшейся строке, что я сделал в поле lol.

К сообщению приложен файл. Размер - 109Kb
12 янв 17, 05:55    [20096508]     Ответить | Цитировать Сообщить модератору
 Re: Удаление дубля из выборки  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20600
ibank5
как сделать чтоб у меня в выборку попал лишь один договор.
GROUP BY по договору и MIN() по дате. Или перенумеровать записи по договорам при сортировке по дате и взять те, что с первыми номерами.
12 янв 17, 07:49    [20096545]     Ответить | Цитировать Сообщить модератору
 Re: Удаление дубля из выборки  [new]
aleks2
Guest
Akina
ibank5
как сделать чтоб у меня в выборку попал лишь один договор.
GROUP BY по договору и MIN() по дате. Или перенумеровать записи по договорам при сортировке по дате и взять те, что с первыми номерами.

Ответ неверный.

Верный ответ: разучить таки outer apply и top(1).
12 янв 17, 08:12    [20096581]     Ответить | Цитировать Сообщить модератору
 Re: Удаление дубля из выборки  [new]
ibank5
Member

Откуда:
Сообщений: 4
Alkina, группировку от меня требуют по всем полям в селекте ( при таком раскладе результат не изменится.
Про сортировку пока что не догнал, как взять с первыми номерами.

aleks2,

В дебри sql лезть не хочется, не знаток, может можно попроще или пример использования аутер эпплай в моем случае ?
12 янв 17, 08:39    [20096657]     Ответить | Цитировать Сообщить модератору
 Re: Удаление дубля из выборки  [new]
aleks2
Guest
left outer join left outer join tDeal d  WITH (NOLOCK index=XPKtDeal)
         on d.DealID       = tal.ObjectID
        and d.InstrumentID = il.ObjectID


outer apply ( select min(dealdate) as Opendate,  max(fixdate ) as CloseDate from tDeal WITH (NOLOCK index=XPKtDeal) where DealID = tal.ObjectID   and InstrumentID = il.ObjectID ) as d
12 янв 17, 09:00    [20096726]     Ответить | Цитировать Сообщить модератору
 Re: Удаление дубля из выборки  [new]
o-o
Guest
Alkina = Akina после праздников?
-----
Если у вас строки одинаковые по всем полям, кроме даты, то группировать достаточно по номеру счета, по остальным полям взять любой агрегат, по дате min. Только на картинке еще и ставка разная, вам какую из них надо выводить?
12 янв 17, 09:29    [20096808]     Ответить | Цитировать Сообщить модератору
 Re: Удаление дубля из выборки  [new]
aleks2
Guest
o-o
Alkina = Akina после праздников?
-----
Если у вас строки одинаковые по всем полям, кроме даты, то группировать достаточно по номеру счета, по остальным полям взять любой агрегат, по дате min. Только на картинке еще и ставка разная, вам какую из них надо выводить?


Как задолбали эти горе-советчики.

Запомни:
1. если у тя есть выбор - НЕ НАДО группировать.
2. если у тя нет выбора - группировке надо подвергать минимальное соединение таблиц, а лучше - одну таблицу.
12 янв 17, 10:34    [20097099]     Ответить | Цитировать Сообщить модератору
 Re: Удаление дубля из выборки  [new]
ibank5
Member

Откуда:
Сообщений: 4
aleks2,

The correlation name 'd' is specified multiple times in a FROM clause.
на аутер эпплай
12 янв 17, 11:32    [20097492]     Ответить | Цитировать Сообщить модератору
 Re: Удаление дубля из выборки  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
aleks2
Как задолбали эти горе-советчики.
Ну, обычно, ты у нас в этой роли выступаешь. Так что, наверное, задолбал сам себя :)
aleks2
Запомни:
1. если у тя есть выбор - НЕ НАДО группировать.
2. если у тя нет выбора - группировке надо подвергать минимальное соединение таблиц, а лучше - одну таблицу.
Запомни: то, что ты себе навыдумывал не есть абсолютная истина
+
use tempdb;
go

create table dbo.t1 (id int identity primary key, name varchar(50) unique);
create table dbo.t2 (id int identity primary key, t1_id int, v int);

insert into dbo.t1
select top (100000)
 newid()
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

insert into dbo.t2
select top (3000000)
 rand(checksum(newid())) * 100000 + 1,
 rand(checksum(newid())) * 10000
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

create index IX_t2__t1_id on dbo.t2 (t1_id) include (v);
go

declare @n varchar(50), @v1 int, @v2 int;

set statistics /*xml, */time, io on;

select
 @n = t1.name, @v1 = min(t2.v), @v2 = max(t2.v)
from
 dbo.t1 join
 dbo.t2 on t2.t1_id = t1.id
group by
 t1.name
option
 (maxdop 1);

select
 @n = t1.name, @v1 = a.min_v, @v2 = a.max_v
from
 dbo.t1 cross apply
 (select min(v), max(v) from dbo.t2 where t1_id = t1.id) a(min_v, max_v)
option
 (maxdop 1);

/*
Таблица "t2". Число просмотров 1, логических чтений 6712, физических чтений 0, упреждающих чтений 24, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "t1". Число просмотров 1, логических чтений 838, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 Время работы SQL Server:
   Время ЦП = 889 мс, затраченное время = 878 мс.

Таблица "t2". Число просмотров 100000, логических чтений 325696, физических чтений 0, упреждающих чтений 18, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "t1". Число просмотров 1, логических чтений 806, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 Время работы SQL Server:
   Время ЦП = 1217 мс, затраченное время = 1252 мс.
*/

set statistics /*xml, */time, io off;
go

delete from dbo.t1 where id > 1000;
truncate table dbo.t2;

insert into dbo.t2
select top (3000000)
 rand(checksum(newid())) * 1000 + 1,
 rand(checksum(newid())) * 10000
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
go

declare @n varchar(50), @v1 int, @v2 int;

set statistics /*xml, */time, io on;

select
 @n = t1.name, @v1 = min(t2.v), @v2 = max(t2.v)
from
 dbo.t1 join
 dbo.t2 on t2.t1_id = t1.id
group by
 t1.name
option
 (maxdop 1);

select
 @n = t1.name, @v1 = a.min_v, @v2 = a.max_v
from
 dbo.t1 cross apply
 (select min(v), max(v) from dbo.t2 where t1_id = t1.id) a(min_v, max_v)
option
 (maxdop 1);

/*
Таблица "t1". Число просмотров 1, логических чтений 13, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "t2". Число просмотров 1, логических чтений 11924, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 Время работы SQL Server:
   Время ЦП = 811 мс, затраченное время = 811 мс.

Таблица "t2". Число просмотров 1000, логических чтений 16143, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "t1". Число просмотров 1, логических чтений 13, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 Время работы SQL Server:
   Время ЦП = 687 мс, затраченное время = 682 мс.
*/

set statistics /*xml, */time, io off;
go

drop table dbo.t1, dbo.t2;
go
12 янв 17, 11:36    [20097526]     Ответить | Цитировать Сообщить модератору
 Re: Удаление дубля из выборки  [new]
ibank5
Member

Откуда:
Сообщений: 4
Сделал по тупому, но работает однако
[b]select * from ([/b]
select cu.Number                                   as Valuta,
	   --case when min(d1.dealdate)!=null then min(d1.dealdate) else min(d.dealdate) end,
	   --min(d1.dealdate),
	  -- case when d1.number=d.number then '1' end,
       Prolong = case when (ROW_NUMBER() OVER(PARTITION BY d.number  ORDER BY d.dealdate ASC)) > 1 then (select min(fixdate) from tdeal  where d.Number = Number) else 0 end,
	   lol = case when (ROW_NUMBER() OVER(PARTITION BY d.number  ORDER BY d.dealdate ASC)) > 1 then (select min(dealdate) from tdeal where d.Number = Number) else 0 end,
	   [b]ROW_NUMBER() OVER(PARTITION BY d.number  ORDER BY d.dealdate DESC) as rn,[/b]
       inst.Name+' '+inst.Name1+' '+inst.Name2     as Name,
	   tal.BriefAccount                            as AccNumber,
       d.dealdate                                  as Opendate,
	   d.fixdate                                   as CloseDate,
	   round(rl.Rest * sign(rl.Rest),2)            as Rest,
	   round(rl.RestBs * sign(rl.RestBs),2)        as RestBs,
	   d.course                                    as Stavka,
	   isnull(d.RegistPayType, 0)                  as SrokVklada,
	   isnull(d.Number, isnull(d.Number, ''))      as dlDealNumber,
	   i.Brief                                     as InstrumentBrief,
   	   /*cast(case when ltrim(rtrim(ul.Vid)) = 'ТвНаграда' then (case when ul.Qty = 0.00 then '' else ul.Qty end) else (case when ul.NesnOstVal = 0.00 then 'p' else ul.NesnOstVal end) end as varchar(10)),*/
	   NesnOstVal = case 
when cu.Number = '840' then case when ltrim(rtrim(ul.Vid)) = 'ТвНаграда' then ul.Qty*@usd else ul.NesnOstVal*@usd end
when cu.Number = '978' then case when ltrim(rtrim(ul.Vid)) = 'ТвНаграда' then ul.Qty*@eur else ul.NesnOstVal*@eur end
when cu.Number = '810' then case when ltrim(rtrim(ul.Vid)) = 'ТвНаграда' then ul.Qty else ul.NesnOstVal end
else case when ltrim(rtrim(ul.Vid)) = 'ТвНаграда' then ul.Qty else ul.NesnOstVal end
end,
       NesnOst = case when ltrim(rtrim(ul.Vid)) = 'ТвНаграда' then ul.FirstQtyRur else ul.NesnOst end
  from tDPTAccountLink tal WITH (NOLOCK index=XIE1tDPTAccountLink)
 inner join tDPT_RuleAcc_Sync il WITH (NOLOCK index=XPKtDPT_RuleAcc_Sync)
         on tal.RuleAccID = il.RuleAccID
        and il.ObjectType = 1
 inner join tDPT_FinOper_Sync i WITH (NOLOCK index=XPKtDPT_FinOper_Sync)
         on i.FinOperID = il.ObjectID
 left outer join tDeal d  WITH (NOLOCK index=XPKtDeal)
         on d.DealID       = tal.ObjectID
        and d.InstrumentID = il.ObjectID
		--outer apply ( select min(dealdate) as Opendate,  max(fixdate ) as CloseDate from tDeal WITH (NOLOCK index=XPKtDeal) where DealID = tal.ObjectID   and InstrumentID = il.ObjectID ) as d
		left join tInstitution inst
		on d.InstitutionID = inst.InstitutionID
		 left join tDPT_Currency_Sync cu WITH (NOLOCK index=XPKtDPT_Currency_Sync)
           on cu.CurrencyID = d.FundID
		   left join pResList  rl  WITH (NOLOCK index=XPKpResList)
		   on rl.SPID      = @@SPID
		   left join tResource r   WITH (NOLOCK index=XPKtResource)
		   on r.ResourceID = rl.ResourceID
		   left join tCurrency  c  WITH (NOLOCK index=XPKtCurrency)
		   on c.CurrencyID = r.FundID
		   left join tUser         WITH (NOLOCK index=XAK0tUser)
		   on tUser.Brief  = convert(char(30),suser_sname())
		   left join #result ul
		   on ul.Number = d.Number
		   /*left join tDeal d1 WITH (NOLOCK index=XPKtDeal)
		   on  d1.dealdate > d.dealdate and d.number =d1.number*/
		   where RL.SPID = @@SPID and
tal.AccountID = RL.ResourceID
and ul.brief != 'NULL'
and il.Brief='Депозит'
and d.fixdate >= '20161014'
[b]) as tbl
where tbl.rn=1[/b]

--and tal.datelast = '1900-01-01 00:00:00'

--group by d.number, d.fixdate, tal.datelast, tal.ondate, d.course, c.Number, d.RegistPayType, il.Brief, il.Name, il.RuleAccID, d.dealdate, tal.DPTAccountLinkID, tal.ObjectID, i.Brief, tal.BriefAccount, inst.Name, inst.Name1, inst.Name2, inst.InstitutionID,cu.Number, rl.rest, rl.restbs,ul.vid,ul.Qty,ul.NesnOst,ul.NesnOstVal,ul.FirstQtyRur--, d1.number, d1.dealdate, d1.fixdate, d1.course
--having min(d.dealdate) <= max(d1.FixDate)
/*T*/
/*W*/
/*W*/
/*O*/
/*O*/
/***/
         
option (FORCE ORDER, LOOP JOIN, KEEPFIXED PLAN)
/***/         

всем спасибо
12 янв 17, 12:34    [20097871]     Ответить | Цитировать Сообщить модератору
 Re: Удаление дубля из выборки  [new]
aleks2
Guest
invm
aleks2
Как задолбали эти горе-советчики.
Ну, обычно, ты у нас в этой роли выступаешь. Так что, наверное, задолбал сам себя :)
aleks2
Запомни:
1. если у тя есть выбор - НЕ НАДО группировать.
2. если у тя нет выбора - группировке надо подвергать минимальное соединение таблиц, а лучше - одну таблицу.
Запомни: то, что ты себе навыдумывал не есть абсолютная истина
+
use tempdb;
go

create table dbo.t1 (id int identity primary key, name varchar(50) unique);
create table dbo.t2 (id int identity primary key, t1_id int, v int);

insert into dbo.t1
select top (100000)
 newid()
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

insert into dbo.t2
select top (3000000)
 rand(checksum(newid())) * 100000 + 1,
 rand(checksum(newid())) * 10000
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

create index IX_t2__t1_id on dbo.t2 (t1_id) include (v);
go

declare @n varchar(50), @v1 int, @v2 int;

set statistics /*xml, */time, io on;

select
 @n = t1.name, @v1 = min(t2.v), @v2 = max(t2.v)
from
 dbo.t1 join
 dbo.t2 on t2.t1_id = t1.id
group by
 t1.name
option
 (maxdop 1);

select
 @n = t1.name, @v1 = a.min_v, @v2 = a.max_v
from
 dbo.t1 cross apply
 (select min(v), max(v) from dbo.t2 where t1_id = t1.id) a(min_v, max_v)
option
 (maxdop 1);

/*
Таблица "t2". Число просмотров 1, логических чтений 6712, физических чтений 0, упреждающих чтений 24, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "t1". Число просмотров 1, логических чтений 838, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 Время работы SQL Server:
   Время ЦП = 889 мс, затраченное время = 878 мс.

Таблица "t2". Число просмотров 100000, логических чтений 325696, физических чтений 0, упреждающих чтений 18, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "t1". Число просмотров 1, логических чтений 806, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 Время работы SQL Server:
   Время ЦП = 1217 мс, затраченное время = 1252 мс.
*/

set statistics /*xml, */time, io off;
go

delete from dbo.t1 where id > 1000;
truncate table dbo.t2;

insert into dbo.t2
select top (3000000)
 rand(checksum(newid())) * 1000 + 1,
 rand(checksum(newid())) * 10000
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
go

declare @n varchar(50), @v1 int, @v2 int;

set statistics /*xml, */time, io on;

select
 @n = t1.name, @v1 = min(t2.v), @v2 = max(t2.v)
from
 dbo.t1 join
 dbo.t2 on t2.t1_id = t1.id
group by
 t1.name
option
 (maxdop 1);

select
 @n = t1.name, @v1 = a.min_v, @v2 = a.max_v
from
 dbo.t1 cross apply
 (select min(v), max(v) from dbo.t2 where t1_id = t1.id) a(min_v, max_v)
option
 (maxdop 1);

/*
Таблица "t1". Число просмотров 1, логических чтений 13, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "t2". Число просмотров 1, логических чтений 11924, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 Время работы SQL Server:
   Время ЦП = 811 мс, затраченное время = 811 мс.

Таблица "t2". Число просмотров 1000, логических чтений 16143, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "t1". Число просмотров 1, логических чтений 13, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 Время работы SQL Server:
   Время ЦП = 687 мс, затраченное время = 682 мс.
*/

set statistics /*xml, */time, io off;
go

drop table dbo.t1, dbo.t2;
go


Все истины относительны.
Абсолютна тока глупость и сферические примеры в вакууме.
12 янв 17, 12:53    [20098019]     Ответить | Цитировать Сообщить модератору
 Re: Удаление дубля из выборки  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
aleks2
Абсолютна тока глупость и сферические примеры в вакууме.
Ну "дарагуля" (с), если ты так и не начнешь изучать предлагаемые примеры, то как раз и продолжишь демонстрировать абсолютную глупость и быть горе-советчиком :)
12 янв 17, 13:13    [20098129]     Ответить | Цитировать Сообщить модератору
 Re: Удаление дубля из выборки  [new]
MSSQLAndDotNet
Member

Откуда:
Сообщений: 10398
а эт зачем?
isnull(d.Number, isnull(d.Number, ''))
12 янв 17, 14:19    [20098486]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить