CREATE PROCEDURE [update_mn_1]
(
@d1 float,
@d2 int
)
AS
DECLARE @n integer,
@i int
DECLARE youbiao1 CURSOR SCROLL
FOR
SELECT M_DATA,S_ID from M
SET @n=1
OPEN youbiao1
FETCH NEXT FROM youbiao1 INTO @d1,@d2WHILE @@FETCH_STATUS=0
BEGINIF(@d1=6)
SET @n=@n+1IF(@n%2!=0)
BEGIN
SET @i=1
WHILE(@i<=12)
BEGIN
IF(@d1>=1 and @d1<=3)
BEGIN
UPDATE M
SET S_ID =@i
WHERE M_DATA =@d1
END
IF(@d1<=5 and @d1>=4)
CONTINUE
SET @i=@i+1
END
ENDELSE IF(@n%2=0)
BEGIN
SET @i=12
WHILE(@i>=1)
BEGIN
IF(@d1>=1 and @d1<=3)
BEGIN
UPDATE M
SET S_ID =@i
WHERE M_DATA =@d1
END
IF(@d1<=5 and @d1>=4)
CONTINUE
SET @i=@i-1
END
END
FETCH NEXT FROM youbiao1 INTO @d1,@d2
ENDCLOSE youbiao1
DEALLOCATE youbiao1GO
我的目的是:
例如:数据 时间 标志位
1 10 null
2 11 null
3 12 null
4 14 null
2 16 null
1 17 null
5 19 null
6 24 null
2 26 null
3 27 null
5 28 null
3 29 null
2 35 null 经过这个程序转化为:
数据 时间 标志位
1 10 1
2 11 1
3 12 1
4 14 null
2 16 2
1 17 2
5 19 null
6 24 null
2 26 2
3 27 2
5 28 null
3 29 1
2 35 1
我是想把属于同一块数据加上标志位,由于数据是对材料的来回扫描得来的(1到3有效,4和5无效,遇到6表示材料扫描到尾部)
所以奇数(++)表示从上往下扫描,偶数(--)表示从下往上,来更新标志位麻烦大家花点时间看看,为什么查询分析器执行了,但是表的标志位却没有更新
(
@d1 float,
@d2 int
)
AS
DECLARE @n integer,
@i int
DECLARE youbiao1 CURSOR SCROLL
FOR
SELECT M_DATA,S_ID from M
SET @n=1
OPEN youbiao1
FETCH NEXT FROM youbiao1 INTO @d1,@d2WHILE @@FETCH_STATUS=0
BEGINIF(@d1=6)
SET @n=@n+1IF(@n%2!=0)
BEGIN
SET @i=1
WHILE(@i<=12)
BEGIN
IF(@d1>=1 and @d1<=3)
BEGIN
UPDATE M
SET S_ID =@i
WHERE M_DATA =@d1
END
IF(@d1<=5 and @d1>=4)
CONTINUE
SET @i=@i+1
END
ENDELSE IF(@n%2=0)
BEGIN
SET @i=12
WHILE(@i>=1)
BEGIN
IF(@d1>=1 and @d1<=3)
BEGIN
UPDATE M
SET S_ID =@i
WHERE M_DATA =@d1
END
IF(@d1<=5 and @d1>=4)
CONTINUE
SET @i=@i-1
END
END
FETCH NEXT FROM youbiao1 INTO @d1,@d2
ENDCLOSE youbiao1
DEALLOCATE youbiao1GO
我的目的是:
例如:数据 时间 标志位
1 10 null
2 11 null
3 12 null
4 14 null
2 16 null
1 17 null
5 19 null
6 24 null
2 26 null
3 27 null
5 28 null
3 29 null
2 35 null 经过这个程序转化为:
数据 时间 标志位
1 10 1
2 11 1
3 12 1
4 14 null
2 16 2
1 17 2
5 19 null
6 24 null
2 26 2
3 27 2
5 28 null
3 29 1
2 35 1
我是想把属于同一块数据加上标志位,由于数据是对材料的来回扫描得来的(1到3有效,4和5无效,遇到6表示材料扫描到尾部)
所以奇数(++)表示从上往下扫描,偶数(--)表示从下往上,来更新标志位麻烦大家花点时间看看,为什么查询分析器执行了,但是表的标志位却没有更新
if object_id('tempdb..#tmp') is not null
drop table #tmp
GO
----创建测试数据
declare @t table(data int,时间 int,标志位 int)
insert @t
select 1, 10, null union all
select 2, 11, null union all
select 3, 12, null union all
select 4, 14, null union all
select 2, 16, null union all
select 1, 17, null union all
select 5, 19, null union all
select 6, 24, null union all
select 2, 26, null union all
select 3, 27, null union all
select 5, 28, null union all
select 3, 29, null union all
select 2, 35, null ----从上至下更新
declare @data int,@flag int
set @flag = 1
update @t set
@flag =
case
when @flag is not null and data between 1 and 3 then @flag
when @flag is not null and data between 4 and 6 then @flag + 1
else 1
end,
标志位 =
case
when data between 4 and 6
then null
else @flag
end----生成用于从下至上更新的临时表
select * into #tmp from @t order by 时间 DESC----从上至下更新临时表(相当于从下至上更新原表)
declare @ bit /*更新标志位时使用的判断标志*/
set @flag = 1
update #tmp set
@ =
case
when @ = 1 then 1
else
case when data = 6 then 1 end /*如果遇到data = 6的行,则之后的行禁止被更新*/
end,
@flag =
case
when @flag is not null and data between 1 and 3 then @flag
when @flag is not null and data between 4 and 6 then @flag + 1
else 1
end,
标志位 = case
when @ = 1 then 标志位 /*第一次更新时data = 6之前的行的标志位不再更新,保持原值*/
else
case
when data between 4 and 6 then null
else @flag
end
end----更新原表的标志位
update a set 标志位 = b.标志位 from @t as a
inner join #tmp as b on a.data = b.data and a.时间 = b.时间 ----查看更新
select * from @t----清除测试环境
drop table #tmp/*结果
数据 时间 标志位
----------------------------------------------------------
1 10 1
2 11 1
3 12 1
4 14 null
2 16 2
1 17 2
5 19 null
6 24 null
2 26 2
3 27 2
5 28 null
3 29 1
2 35 1
*/
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 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 ----从上至下更新
declare @data int,@flag int
set @flag = 1UPDATE @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 @ bit /*更新S_ID时使用的判断标志*/
set @flag = 1UPDATE #tmp SET
@ =
case
when @ = 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 @ = 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_DATA S_TIME S_ID
--------------------------------------------
3 12 1
2 13 1
2 14 1
1 15 1
4 16 NULL
5 17 NULL
3 18 2
3 19 2
2 20 2
4 21 NULL
1 22 3
2 23 3
3 24 3
5 25 NULL
3 26 4
2 27 4
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
*/