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

Откуда:
Сообщений: 30
В БД есть две таблицы.
Table 1
id - int ключ автоинкремент
date - дата
itemNo - код товара
quant - количество
amount - сумма
Комбинация значений полей date и itemNo уникальна. То есть для каждого itemNo на каждую date может быть только одна запись. Это условие никак средствами MS SQL не задано (нет ни ключей ни индексов).


Table2
idd - int ключ автоинкремент
date2 -дата
itemNo2 - код товара
ItemCostAmount - сумма издержек

В Table2 может быть: ни одной, одна или несколько записей с itemNo2=Table1.itemNo
При этом Table2.date2 может быть: меньше, равна или больше Table1.date


Задача: в запросе для каждой записи из Table1 с itemNo и date вывести одну запись из Table2, где Table1.itemNo=Table2.itemNo2 AND Table2.date2<=Table1.date AND Table2.date2 является самой поздней датой из всего набора записей из Table2, удовлетворяющего предыдущим условиям.

Нутром чую, что можно сделать вложенный запрос с ещё одним вложенным запросом. Сначала выбрать все записи из Table2 с датами меньшими или равными Table1.date а потом из полученнного набора выбрать max(Table2.date2)/

Но руками сделать не получается. То ли руки не слишком прямые, то ли голова немного не острая )))

Может есть другие варианты?
17 фев 17, 17:45    [20222339]     Ответить | Цитировать Сообщить модератору
 Re: Select с двойным вложенным запросом или можно иначе?  [new]
r.gribachev
Member

Откуда:
Сообщений: 30
r.gribachev
В БД есть две таблицы.
а потом из полученнного набора выбрать max(Table2.date2)/


Selext top 1 order by date2 desc тоже подойдёт, но как сам набор получить?
17 фев 17, 17:50    [20222373]     Ответить | Цитировать Сообщить модератору
 Re: Select с двойным вложенным запросом или можно иначе?  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
r.gribachev
Selext top 1 order by date2 desc тоже подойдёт, но как сам набор получить?
cross (outer) apply
17 фев 17, 17:56    [20222413]     Ответить | Цитировать Сообщить модератору
 Re: Select с двойным вложенным запросом или можно иначе?  [new]
r.gribachev
Member

Откуда:
Сообщений: 30
invm
r.gribachev
Selext top 1 order by date2 desc тоже подойдёт, но как сам набор получить?
cross (outer) apply


Если не затруднит - подробнее напишите. А то я c Cross, Outer, Union и Join не очень в ладах. В основном графическим конструктором в оснастке пользуюсь.
Заранее спасибо.
17 фев 17, 18:10    [20222463]     Ответить | Цитировать Сообщить модератору
 Re: Select с двойным вложенным запросом или можно иначе?  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
r.gribachev,

select
 o.name, c.name
from
 sys.objects o cross apply
 (select top (1) name from sys.columns where object_id = o.object_id order by column_id) c
17 фев 17, 18:40    [20222569]     Ответить | Цитировать Сообщить модератору
 Re: Select с двойным вложенным запросом или можно иначе?  [new]
r.gribachev
Member

Откуда:
Сообщений: 30
invm
r.gribachev,

select
 o.name, c.name
from
 sys.objects o cross apply
 (select top (1) name from sys.columns where object_id = o.object_id order by column_id) c


Спасибо большое. Буду пробовать.
17 фев 17, 18:48    [20222591]     Ответить | Цитировать Сообщить модератору
 Re: Select с двойным вложенным запросом или можно иначе?  [new]
r.gribachev
Member

Откуда:
Сообщений: 30
r.gribachev,

Всем спасибо за ответы. Задача решена.
20 фев 17, 16:54    [20229799]     Ответить | Цитировать Сообщить модератору
 Re: Select с двойным вложенным запросом или можно иначе?  [new]
west74
Member

Откуда: Челябинск
Сообщений: 76
приветствую

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

select * from
(
select T1.*,T2.date2,
row_number() over (partition by Table1.itemNo order by Table2.date2 desc) nn
from Table1 as T1
left join Table2 as T2 on Table1.itemNo=Table2.itemNo2 AND Table2.date2<=Table1.date
) AA
where nn=1
21 фев 17, 11:46    [20232371]     Ответить | Цитировать Сообщить модератору
 Re: Select с двойным вложенным запросом или можно иначе?  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
west74
можно попробовать решить, подобную задачу оконными функциями, они менее деградируют по производительности под увеличением объема данных
+ О cross apply, оконных функциях и деградации производительности
use tempdb;
go

create table dbo.t1 (id int primary key);
create table dbo.t2 (id int identity primary key, t1_id int not null, v int);

insert into dbo.t1
select top (100000)
 row_number() over (order by (select 1))
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

insert into dbo.t2
select
 t1.id, a.v
from
 dbo.t1 cross apply
 (select top (100) rand(checksum(newid())) * 1000 from master.dbo.spt_values) a(v);

create index IX_t2__t1_id__v on dbo.t2 (t1_id, v);
go

declare @v int;

set statistics xml, time, io on;

with a as
(
 select
  t2.v,
  row_number() over (partition by t2.t1_id order by v) as rn
 from
  dbo.t1 join
  dbo.t2 on t2.t1_id = t1.id
)
select
 @v = v
from
 a
where
 rn = 1
option
 (maxdop 1);
/*
Таблица "t2". Число просмотров 100000, логических чтений 341282, физических чтений 0, упреждающих чтений 79, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "t1". Число просмотров 1, логических чтений 337, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

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

select
 @v = a.v
from
 dbo.t1 cross apply
 (select top (1) v from dbo.t2 where t1_id = t1.id order by v) a(v)
option
 (maxdop 1);
/*
Таблица "t2". Число просмотров 100000, логических чтений 319003, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "t1". Число просмотров 1, логических чтений 337, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

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

set statistics xml, time, io off;
go

drop table dbo.t2, dbo.t1;
go
21 фев 17, 12:22    [20232514]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить