Correct the problem of using the temporary table, thank you all, wait online

long123mn

if object_id ('tempdb .. # tmp') is not null
drop table #tmp GO
 ---- Create test data
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 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 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
---- Update from top to bottom
declare @data int, @ flag int set @flag = 1
UPDATE @t SET
/ * Use @data as a flag for judging that S_DATA is continuously 4, 5, 6 invalid data
@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
else 1
end,
S_ID =
case
when S_DATA between 4 and 6 then null
else @flag end
---- Generate temporary tables for bottom-up updates
select * into #tmp from @t order by S_TIME DESC
---- Update the temporary table from top to bottom (equivalent to updating the original table from bottom to top)
declare @mark bit
/ * Judgment flag used when updating S_ID * /
set @flag = 1
UPDATE #tmp SET
@mark =
case
when @mark = 1 then 1
else
case when S_DATA = 6 then 1 end
/ * If a row with S_DATA = 6 is encountered, subsequent rows are prohibited from being updated * /
end,
/ * Use @data as a flag for judging that S_DATA is continuously 4, 5, 6 invalid data
@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_ID of the row before S_DATA = 6 when updating for the first time is no longer updated, keep the original value * / else
case
when S_DATA between 4 and 6 then null
else @flag
end
end
---- Update the S_ID of the original table
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
 ---- Check for updates
select * from @t
---- Clear test environment
drop table #tmp

long123mn

But I was wrong with this set of data tests:
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

long123mn

Sorry everyone, the first set of data is wrong, it is:
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 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 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

long123mn

The subject requirements are:
 There are 12 pieces of material: 1, 2 ... 12 The data recorded by laser scanning the surface of the material has been stored in the database. The last time for valid data. For example, the effective data range: 1 ~ 3 The data range of the gap between the materials: 4 ~ 5 After the last piece of data is scanned, there is another flag bit "6", and then the material is scanned back. Similarly, the first piece of material has a flag bit after scanning 6 "

fanfan1980

if object_id ('tempdb .. # tmp') is not null
drop table #tmp
GO

Try to put GO alone

long123mn

It ’s a new line for me. I get messed up with verysource. Sorry, I did n’t post a good post.

10118419

Question data results

3 12 1
2 13 1
2 14 1
1 15 1
4 16 NULL
5 17 NULL
3 18 1
3 19 1
2 20 1
4 21 NULL
1 22 2
2 23 2
3 24 2
5 25 NULL
3 26 3
2 27 3
6 28 NULL
2 29 4
3 30 4
4 31 NULL
1 32 3
1 33 3
2 34 3
5 35 NULL
2 36 2
3 37 2
4 38 NULL
1 39 1
2 40 1
3 41 1

10118419

The data on the 2nd floor can be executed
3 12 1
2 13 1
2 14 1
1 15 1
4 16 NULL
5 17 NULL
3 18 1
3 19 1
2 20 1
4 21 NULL
1 22 2
2 23 2
3 24 2
5 25 NULL
3 26 3
2 27 3
6 28 NULL
2 29 4
3 30 4
4 31 NULL
1 32 3
1 33 3
2 34 3
5 35 NULL
2 36 2
3 37 2
4 38 NULL
1 39 1
2 40 1
3 41 1

10118419

1st floor data results
3 12 1
2 13 1
2 14 1
1 15 1
4 16 NULL
5 17 NULL
4 16 NULL
4 16 NULL
3 18 1
3 19 1
2 20 1
4 21 NULL
1 22 2
2 23 2
3 24 2
5 25 NULL
4 16 NULL
4 16 NULL
4 16 NULL
3 26 1
2 27 1
6 28 NULL
2 29 4
3 30 4
4 31 NULL
1 32 3
1 33 3
2 34 3
5 35 NULL
2 36 2
3 37 2
4 38 NULL
1 39 1
2 40 1
3 41 1
  Brother, the query analyzer can be executed here. How can you find me ????

long123mn

Thank you all, the result I want is:

The sign configuration is as follows: 1, 2, 3, 4, ..., 12, 12, 11, ... 1, 1, 2, 3....

The first set of data tests is correct, but the latter set of data tests is not correct.