Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Для MSSQL-2005 есть outer apply, а как для 2000?! (туплю)  [new]
НесчастнаяЖертваБана
Member

Откуда: (((@)))
Сообщений: 27
DECLARE @t table (col1 int, col2 int, col3 int)
INSERT @t (col1) values (1), (2), (3), (4)
DECLARE @n table (c1 int, c2 int, c3 int)
INSERT @n (c1, c2, c3) values (1,1,1),(1,1,2),(1,1,3),(1,2,2),(1,4,1),(1,4,2), 
                              (2,5,0),(2,5,1),(2,1,0),(2,2,0),(2,1,1),(2,3,1),
                              (4,1,3),(4,2,7),(4,3,3),(4,1,3);
select * FROM @t order BY col1;
select * FROM @n order BY c1, c3;


col1 col2 col3
1 NULL NULL
2 NULL NULL
3 NULL NULL
4 NULL NULL


c1 c2 c3
1 4 1
1 1 1
1 1 2
1 2 2
1 4 2
1 1 3
2 5 0
2 1 0
2 2 0
2 1 1
2 3 1
2 5 1
4 1 3
4 3 3
4 1 3
4 2 7


Необходимо в @t в поля col2 и col3 занести значения из c2 и c3 из соответствующей строки таблицы @n (@t.col1 = @n.c1), у которой значение поля c3 минимальное.


В MSsQL2005:
UPDATE @t 
  SET col2 = c2, col3 = c3 
  FROM @t outer apply  (select top 1 * from @n where col1 = c1 order BY c3 asc) sub

после select * FROM @t order BY col1 получаю

col1 col2 col3
1 1 1
2 1 0
3 NULL NULL
4 3 3


Туплю и не могу придумать - как сделать то же самое на MSSQL-2000.

Пока обошёлся криво вот так (ТАБЛИЦЫ МОЖНО РАССМАТРИВАТЬ КАК СТАТИЧЕСКИЕ):
update @t set col2 = (select top 1 c2 from @n where col1=c1 order by c3 asc)
update @t set col3 = (select top 1 c3 from @n where col1=c1 order by c3 asc)
 


Я так понимаю, что это единственная "альтернатива" вот такому коду:
update @t 
  set col2 = tmp_c2, col3 = tmp_c3
  from @t t , (select tmp_c1 = c1, tmp_c2 = c2, tmp_c3 = c3 from @n where c3 = (select min(c3) from @n n where n.c1 = c1)) tmp
  where t.col1 = tmp.tmp_c1


А если мне надо выбрать строчку из @n по минимумам двух полей (c2 и c3, например)? То только предыдущий вариант или мутить курсор?

Заранее огромное спасибо!

PS Я действительно туплю а не стебаюсь.
PPS Сервер сменить не предлагать!
PPPS Задача на самом деле сложнее, но моя цель не устраивать другим вынос мозга а свой поправить (после, мать их, политических г..баталий ;-) ).
14 фев 12, 12:52    [12087830]     Ответить | Цитировать Сообщить модератору
 Re: Для MSSQL-2005 есть outer apply, а как для 2000?! (туплю)  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
update t 
 set
  col2 = x.c2, col3 = x.c3
from
 (
  select
   c1, c2, c3
  from
   @n n
  where
   not exists(select * from @n where c1 = n.c1 and c2 < n.c2 and c3 < n.c3)
 ) x join
 t on t.col1 = x.c1
14 фев 12, 13:08    [12087990]     Ответить | Цитировать Сообщить модератору
 Re: Для MSSQL-2005 есть outer apply, а как для 2000?! (туплю)  [new]
iljy
Member

Откуда:
Сообщений: 8711
НесчастнаяЖертваБана,

DECLARE @t table (col1 int, col2 int, col3 int)
INSERT @t (col1) values (1), (2), (3), (4)
DECLARE @n table (c1 int, c2 int, c3 int)
INSERT @n (c1, c2, c3) values (1,1,1),(1,1,2),(1,1,3),(1,2,2),(1,4,1),(1,4,2), 
                              (2,5,0),(2,5,1),(2,1,0),(2,2,0),(2,1,1),(2,3,1),
                              (4,1,3),(4,2,7),(4,3,3),(4,1,3);
select * FROM @t order BY col1;
select * FROM @n order BY c1, c3;

UPDATE t
  SET col2 = c2, col3 = c3 
  FROM @t t
	left join @n n on exists
		(
			select * from
			(select top 1 nn.c2,nn.c3 from @n nn where t.col1 = nn.c1 order BY nn.c3) ttt
			where ttt.c2 = n.c2 and ttt.c3 = n.c3
		)
			 

select * from @t
14 фев 12, 13:23    [12088133]     Ответить | Цитировать Сообщить модератору
 Re: Для MSSQL-2005 есть outer apply, а как для 2000?! (туплю)  [new]
НесчастнаяЖертваБана
Member

Откуда: (((@)))
Сообщений: 27
invm
update t 
 set
  col2 = x.c2, col3 = x.c3
from
 (
  select
   c1, c2, c3
  from
   @n n
  where
   not exists(select * from @n where c1 = n.c1 and c2 < n.c2 and c3 < n.c3)
 ) x join
 t on t.col1 = x.c1

Спасибо! Понял. Сам бы не догадался... Прикипели мозги к "select top 1 ..."; пора их откипать.
14 фев 12, 15:37    [12089730]     Ответить | Цитировать Сообщить модератору
 Re: Для MSSQL-2005 есть outer apply, а как для 2000?! (туплю)  [new]
НесчастнаяЖертваБана
Member

Откуда: (((@)))
Сообщений: 27
iljy
НесчастнаяЖертваБана,

DECLARE @t table (col1 int, col2 int, col3 int)
INSERT @t (col1) values (1), (2), (3), (4)
DECLARE @n table (c1 int, c2 int, c3 int)
INSERT @n (c1, c2, c3) values (1,1,1),(1,1,2),(1,1,3),(1,2,2),(1,4,1),(1,4,2), 
                              (2,5,0),(2,5,1),(2,1,0),(2,2,0),(2,1,1),(2,3,1),
                              (4,1,3),(4,2,7),(4,3,3),(4,1,3);
select * FROM @t order BY col1;
select * FROM @n order BY c1, c3;

UPDATE t
  SET col2 = c2, col3 = c3 
  FROM @t t
	left join @n n on exists
		(
			select * from
			(select top 1 nn.c2,nn.c3 from @n nn where t.col1 = nn.c1 order BY nn.c3) ttt
			where ttt.c2 = n.c2 and ttt.c3 = n.c3
		)
			 

select * from @t


Спасибо!

Круто! Даже не ожидал что такое в принципе может сработать... Единственное что хочу сказать - ну ни фига не понял... Даже фактический план выполнения посмотрел...- всё равно не понял ... Можно по-подробнее объяснить - что и как происходит?..
14 фев 12, 15:39    [12089770]     Ответить | Цитировать Сообщить модератору
 Re: Для MSSQL-2005 есть outer apply, а как для 2000?! (туплю)  [new]
iljy
Member

Откуда:
Сообщений: 8711
НесчастнаяЖертваБана,

это финт ушами:) Соединение по 2м полям, вытаскиваемым кореллированным запросом. Само условие записывается во where, если условие выполняется - будут записи, что и проверяем с помощью Exists. Можно еще так сделать:
UPDATE t
  SET col2 = CAST(mi & 0xffffffff as int),
	  col3 = CAST(mi / 0x100000000 as int)
FROM @t t
	left join 
	(
		select c1, MIN(CAST(c3 as bigint) * 0x100000000 + c2) mi
		from @n
		group by c1			 
	)tt on t.col1 = tt.c1
14 фев 12, 16:28    [12090223]     Ответить | Цитировать Сообщить модератору
 Re: Для MSSQL-2005 есть outer apply, а как для 2000?! (туплю)  [new]
НесчастнаяЖертваБана
Member

Откуда: (((@)))
Сообщений: 27
iljy
НесчастнаяЖертваБана,

это финт ушами:) Соединение по 2м полям, вытаскиваемым кореллированным запросом. Само условие записывается во where, если условие выполняется - будут записи, что и проверяем с помощью Exists. Можно еще так сделать:
UPDATE t
  SET col2 = CAST(mi & 0xffffffff as int),
	  col3 = CAST(mi / 0x100000000 as int)
FROM @t t
	left join 
	(
		select c1, MIN(CAST(c3 as bigint) * 0x100000000 + c2) mi
		from @n
		group by c1			 
	)tt on t.col1 = tt.c1

Я в общем понял суть trick, но, блин, это действительно круто... (дальнейшие изъ*пы, я так понимаю, имеют чисто академический интерес)... Я просто логику не понял. Ну пока просто постараюсь запомнить. Ещё раз - СПАСИБО!
14 фев 12, 19:03    [12091497]     Ответить | Цитировать Сообщить модератору
 Re: Для MSSQL-2005 есть outer apply, а как для 2000?! (туплю)  [new]
iljy
Member

Откуда:
Сообщений: 8711
НесчастнаяЖертваБана
(дальнейшие изъ*пы, я так понимаю, имеют чисто академический интерес)...

Ну почему, могут иметь чисто практический - производительность разная в разных ситуациях.
14 фев 12, 19:05    [12091512]     Ответить | Цитировать Сообщить модератору
 Re: Для MSSQL-2005 есть outer apply, а как для 2000?! (туплю)  [new]
1
Guest
если UPDATE @t SET col2 = c2, col3 = c3
FROM @t outer apply (select top 1 * from @n where col1 = c1 order BY c3 asc) sub - есть правильно,
то это будет так
UPDATE t
SET col2 = c2, col3 = c3
from t @t left join (select n.c1, c2, n.c3
from (select c1, min(c3) c3 from @n n) n
inner join @n n1 on n.c1=n1.c1 and n.c3=n1.c3) n on t.col1=n.c1
15 фев 12, 20:17    [12098952]     Ответить | Цитировать Сообщить модератору
 Re: Для MSSQL-2005 есть outer apply, а как для 2000?! (туплю)  [new]
1
Guest
а точнее так
UPDATE t
SET col2 = c2, col3 = c3
from t @t left join (select n.c1, c2, n.c3
from (select c1, min(c3) c3 from @n n group by c1) n
inner join @n n1 on n.c1=n1.c1 and n.c3=n1.c3) n on t.col1=n.c1
15 фев 12, 20:19    [12098960]     Ответить | Цитировать Сообщить модератору
 Re: Для MSSQL-2005 есть outer apply, а как для 2000?! (туплю)  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8879
мда...
18 фев 12, 11:33    [12114851]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить