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

Откуда: Железнодорожный
Сообщений: 1842
Блог
Всем доброго дня.
Перейду раз к сути. Понимаю что чудес не бывает, т.ч. прошу подскажите где глюк или что не правильно делаю.

краткое описание: в процедуре dbo.spTest1 удаляю rnum > 1 и ожидаю в таблице dbo.DataLog увидеть rnum > 1, но получаю rnum = 1. помогите понять почему. Если заменить delete на select rnum как и ожидается > 1

InsTime rnum a b c
2016-03-15 10:46:24.1046511 1 1 2 3

Microsoft SQL Server 2014 - 12.0.4422.0 (X64) 
Jul 27 2015 16:56:19
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

+ репо

USE [tempdb]
GO

create table dbo.DataLog(
InsTime datetime2 not null default(sysdatetime()),
rnum int null,
a int null,
b int null,
c int null
)
GO

CREATE TYPE dbo.TestTableType AS TABLE 
(
a int null,
b int null,
c int null
)
GO

create proc dbo.spTest1 (@TestTT dbo.TestTableType readonly)
as begin
	
declare @InnerTest1 table (
	a int null,
	b int null,
	c int null
)

insert @InnerTest1
select * from @TestTT

if exists (select 1 
			from @InnerTest1 
			group by b,c 
			having COUNT(*) > 1) begin

	delete d
output SYSDATETIME(),deleted.* into dbo.DataLog
from(
	select ROW_NUMBER() over (partition by b,c order by a desc) rnum,*
			from @InnerTest1
	)d
where d.rnum > 1

end
end
GO

declare @tt dbo.TestTableType
insert @tt values (1,2,3),(2,2,4),(3,2,3)

exec dbo.spTest1 @TestTT = @tt

select *
from dbo.DataLog

15 мар 16, 10:54    [18932131]     Ответить | Цитировать Сообщить модератору
 Re: непонятка с запросом. Табличные переменные.  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
gds,

если поменять процедуру на
alter  proc dbo.spTest1 (@TestTT dbo.TestTableType readonly)
as begin
	
declare @InnerTest1 table (
	rnum int null,
	a int null,
	b int null,
	c int null
)

insert @InnerTest1
select ROW_NUMBER() over (partition by b,c order by a desc) rnum,* from @TestTT

if exists (select 1 
			from @InnerTest1 
			group by b,c 
			having COUNT(*) > 1) begin

	delete d
output SYSDATETIME(),deleted.* into dbo.DataLog
from @InnerTest1 d
where d.rnum > 1

end
end
GO


то все ок. в DataLog нужные значения.
15 мар 16, 10:59    [18932182]     Ответить | Цитировать Сообщить модератору
 Re: непонятка с запросом. Табличные переменные.  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
declare @t1 table ( id int identity, a int )
insert into @t1
values(1),(1),(1),(2),(3),(3)

--Ранжирование перед удалением
select row_number() over (partition by a order by a) rn, * from @t1;

declare @t2 table ( rn int, id int, a int );
with cte
as
(
select row_number() over (partition by a order by a) rn, * from @t1
)
delete from cte
output deleted.* into @t2
where rn > 1

--Что мы удалили через OUTPUT
select * from @t2




Удалили мы всё верно, условие выполнили, а вот через OUTPUT row_number() на 1 меньше

К сообщению приложен файл. Размер - 9Kb
15 мар 16, 11:05    [18932224]     Ответить | Цитировать Сообщить модератору
 Re: непонятка с запросом. Табличные переменные.  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Видимо, в deleted поле rnum вычисляемое как ROW_NUMBER(). Вычисляется перед выдачей в output.
Поскольку в каждой группе удаляется одна запись, то она всегда получает номер 1.
15 мар 16, 11:10    [18932255]     Ответить | Цитировать Сообщить модератору
 Re: непонятка с запросом. Табличные переменные.  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
iap
Видимо, в deleted поле rnum вычисляемое как ROW_NUMBER(). Вычисляется перед выдачей в output.
Поскольку в каждой группе удаляется одна запись, то она всегда получает номер 1.


Именно, "ранжирование на лету", т.е. условие мы выполняли по ранжированию на SELECT, а в OUTPUT применилось новое ранжирование уже к удаляемому рекордсету
15 мар 16, 11:13    [18932277]     Ответить | Цитировать Сообщить модератору
 Re: непонятка с запросом. Табличные переменные.  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
iap,

Видимо так и есть, несколько нелогично, но придется с этим жить. По запросу более логично брать rnum как поле, а не как функцию.
15 мар 16, 11:15    [18932288]     Ответить | Цитировать Сообщить модератору
 Re: непонятка с запросом. Табличные переменные.  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог


К сообщению приложен файл. Размер - 36Kb
15 мар 16, 11:34    [18932423]     Ответить | Цитировать Сообщить модератору
 Re: непонятка с запросом. Табличные переменные.  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
gds
придется с этим жить
Почему бы не написать
output SYSDATETIME(), deleted.rnum + 1, deleted.a, deleted.b, deleted.c into dbo.DataLog
?
15 мар 16, 22:16    [18935765]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить