修改语句问题，逻辑错误？大家运行看看
if object_id( 'tempdb..#tmp ') is not null
drop table #tmp
GO
----创建测试数据
declare @t table(S_DATA int,S_TIME int,S_ID int)
insert @t
select 3, 12, null union all
select 2, 13, null union all
select 2, 14, null union all
select 1, 15, null union all
select 4, 16, null union all
select 5, 17, null union all
select 4, 16, null union all
select 4, 16, null union all
select 3, 18, null union all
select 3, 19, null union all
select 2, 20, null union all
select 4, 21, null union all
select 1, 22, null union all
select 2, 23, null union all
select 3, 24, null union all
select 5, 25, null union all
select 4, 16, null union all
select 4, 16, null union all
select 4, 16, null union all
select 3, 26, null union all
select 2, 27, null union all
select 6, 28, null union all
select 2, 29, null union all
select 3, 30, null union all
select 4, 31, null union all
select 1, 32, null union all
select 1, 33, null union all
select 2, 34, null union all
select 5, 35, null union all
select 2, 36, null union all
select 3, 37, null union all
select 4, 38, null union all
select 1, 39, null union all
select 2, 40, null union all
select 3, 41, null
----从上至下更新
declare @data int,@flag int
set @flag = 1
UPDATE @t SET
/*使用@data作为判断S_DATA连续为4,5,6无效数据的标志*/
@data = case
when @data between 4 and 6 and S_DATA between 4 and 6 then 888
else S_DATA end,
@flag =
case
when S_DATA between 1 and 3 then @flag
when S_DATA between 4 and 6 and @data <> 888 then @flag + 1
when S_DATA between 4 and 6 and @data = 888 then @flag
else 1
end,
S_ID =
case
when S_DATA between 4 and 6
then null
else @flag
end
----生成用于从下至上更新的临时表
select * into #tmp from @t order by S_TIME DESC
----从上至下更新临时表(相当于从下至上更新原表)
declare @mark bit
/*更新S_ID时使用的判断标志*/
set @flag = 1
UPDATE #tmp SET @mark =
case
when @mark = 1 then 1
else
case when S_DATA = 6 then 1 end
/*如果遇到S_DATA = 6的行,则之后的行禁止被更新*/
end,
/*使用@data作为判断S_DATA连续为4,5,6无效数据的标志*/
@data =
case
when @data between 4 and 6 and S_DATA between 4 and 6 then 888 else S_DATA end,
@flag =
case
when S_DATA between 1 and 3 then @flag
when S_DATA between 4 and 6 and @data <> 888 then @flag + 1
when S_DATA between 4 and 6 and @data = 888 then @flag
else 1
end,
S_ID = case
when @mark = 1 then S_ID
/*第一次更新时S_DATA = 6之前的行的S_ID不再更新,保持原值*/
else
case
when S_DATA between 4 and 6 then null
else @flag
end
end
----更新原表的S_ID
update a set S_ID = b.S_ID from @t as a
inner join #tmp as b on a.S_DATA = b.S_DATA and a.S_TIME = b.S_TIME
----查看更新
select * from @t
----清除测试环境
drop table #tmp
[解决办法]
如果仍要保持S_ID:
if object_id( 'tempdb..#tmp ') is not null
drop table #tmp
GO
----创建测试数据
declare @t table(S_DATA int,S_TIME int,S_ID int)
insert @t
select 3, 12, null union all
select 2, 13, null union all
select 2, 14, null union all
select 1, 15, null union all
select 4, 16, null union all
select 5, 17, null union all
select 4, 16, null union all
select 4, 16, null union all
select 3, 18, null union all
select 3, 19, null union all
select 2, 20, null union all
select 4, 21, null union all
select 1, 22, null union all
select 2, 23, null union all
select 3, 24, null union all
select 5, 25, null union all
select 4, 16, null union all
select 4, 16, null union all
select 4, 16, null union all
select 3, 26, null union all
select 2, 27, null union all
select 6, 28, null union all
select 2, 29, null union all
select 3, 30, null union all
select 4, 31, null union all
select 1, 32, null union all
select 1, 33, null union all
select 2, 34, null union all
select 5, 35, null union all
select 2, 36, null union all
select 3, 37, null union all
select 4, 38, null union all
select 1, 39, null union all
select 2, 40, null union all
select 3, 41, null
----从上至下更新(更新到S_DATA=6时停止更新)
declare @data int,@flag int,@isend bit
set @flag = 1
set @isend = 0
update @t set
@isend = case when S_DATA = 6 then 1 else @isend end,
@flag = case when @data in(4,5,6) and S_DATA not in(4,5,6) then @flag + 1 else @flag end,
S_ID = case when @isend = 1 then S_ID else case when S_DATA in(4,5,6) then null else @flag end end,
@data = S_DATA
----生成临时表,用于实现从下至上更新(更新到S_DATA=6时停止更新)
select * into #tmp from @t order by S_TIME DESC /*按S_TIME降序*/
set @flag = 1
set @isend = 0
update #tmp set
@isend = case when S_DATA = 6 then 1 else @isend end,
@flag = case when @data in(4,5,6) and S_DATA not in(4,5,6) then @flag + 1 else @flag end,
S_ID = case when @isend = 1 then S_ID else case when S_DATA in(4,5,6) then null else @flag end end,
@data = S_DATA
----更新原表的S_ID
update a set S_ID = b.S_ID from @t as a
inner join #tmp as b on a.S_DATA = b.S_DATA and a.S_TIME = b.S_TIME
----按标志位和方向汇总求S_DATA平均值
select S_DATA = avg(S_DATA),S_TIME = max(S_TIME),S_ID
from @t where S_ID is not null and S_TIME < (select S_TIME from @t where S_DATA = 6)
group by S_ID
UNION ALL
select S_DATA = avg(S_DATA),S_TIME = max(S_TIME),S_ID
from @t where S_ID is not null and S_TIME > (select S_TIME from @t where S_DATA = 6)
group by S_ID order by S_TIME
----清除测试环境
drop table #tmp
/*结果
S_DATA S_TIME S_ID
----------- ----------- -----------
2 15 1
2 20 2
2 24 3
2 27 4
2 30 4
1 34 3
2 37 2
2 41 1
*/
[解决办法]
--第一部分按照如下修改可以精簡不少代碼，也可以達到效果。
----创建测试数据
declare @t table(S_DATA int,S_TIME int,S_ID int)
insert @t
select 3, 12, null union all
select 2, 13, null union all
select 2, 14, null union all
select 1, 15, null union all
select 4, 16, null union all
select 5, 17, null union all
select 4, 16, null union all
select 4, 16, null union all
select 3, 18, null union all
select 3, 19, null union all
select 2, 20, null union all
select 4, 21, null union all
select 1, 22, null union all
select 2, 23, null union all
select 3, 24, null union all
select 5, 25, null union all
select 4, 16, null union all
select 4, 16, null union all
select 4, 16, null union all
select 3, 26, null union all
select 2, 27, null union all
select 6, 28, null union all
select 2, 29, null union all
select 3, 30, null union all
select 4, 31, null union all
select 1, 32, null union all
select 1, 33, null union all
select 2, 34, null union all
select 5, 35, null union all
select 2, 36, null union all
select 3, 37, null union all
select 4, 38, null union all
select 1, 39, null union all
select 2, 40, null union all
select 3, 41, null
----从上至下更新
declare @data int,@flag int, @mark bit
select @flag = 1, @data = 0, @mark = 0
UPDATE @t SET
@mark = (Case S_DATA When 6 Then 1 Else @mark End),
@flag = (Case When (@data Between 4 And 5) And (S_DATA Not Between 4 And 5) Then (Case @mark When 0 Then @flag + 1 Else @flag- 1 End) Else @flag End),
S_ID = (Case When (S_DATA Between 4 And 6) Then Null Else @flag End),
@data = S_DATA
----查看更新
select * from @t
