第一种是通过游标过每一行
--创建一个游标
DECLARE cursor_HECM_ROMM CURSOR SCROLL
FOR
SELECT ID
FROM TABLE
WHERE XX=XX
--打开游标
OPEN cursor_HECM_ROMM;
--存储读取的值
DECLARE @ROOMID VARCHAR(50);
--读取第一条记录
FETCH FIRST FROM cursor_HECM_ROMM INTO @ROOMID;
--循环读取游标记录
--PRINT '读取的数据如下:';
--全局变量
WHILE ( @@fetch_status = 0 )
BEGIN
PRINT 'ID:' + @ROOMID;
UPDATE dbo.TABLE
SET XX = XX
WHERE XX = @ROOMID;
--继续读取下一条记录
FETCH NEXT FROM cursor_HECM_ROMM INTO @ROOMID;
END;
--关闭游标
CLOSE cursor_HECM_ROMM;
--删除游标
DEALLOCATE cursor_HECM_ROMM;
第二种是通过存储过程来遍历但是这里有BUG,有的会漏掉,还是推荐第一种。
USE ;
--指定数据库
DECLARE @min_id INT;
DECLARE @UpOutTemp INT;
DECLARE @LowOutTemp INT;
DECLARE @NoPayInTemp INT;
DECLARE @NoPayOutTemp INT;
DECLARE @PayedInTemp INT;
DECLARE @PayedOutTemp INT;
--0 未缴费;1已缴费
DECLARE @PaymentState INT;
--设定温度
DECLARE @SetTemp INT;
DECLARE @UpRoomTemp INT;
DECLARE @LowRoomTemp INT;
DECLARE @RoomTemp INT;
DECLARE @SetBoltStatus INT;
--声明整数变量@x
SET @min_id = ( SELECT MIN(LXTID)
FROM dbo.t_LXT
);
SET @LowOutTemp = 30;
SET @UpOutTemp = 46;
SET @LowRoomTemp = 18;
SET @UpRoomTemp = 23;
--给变量@x赋初值为当前最小的Id值
WHILE @min_id > 0
BEGIN
SELECT @SetTemp = ( SELECT setTemp
FROM dbo.t_LXT
WHERE LXTID = @min_id
);
SELECT @PaymentState = ( SELECT paymentState
FROM dbo.t_LXT
WHERE LXTID = @min_id
);
SELECT @PayedOutTemp = ROUND(( ( @UpOutTemp - @LowOutTemp - 1 )
* RAND() + @LowOutTemp ), 0);
SELECT @PayedInTemp = @PayedOutTemp + ROUND(( ( 11 - 7 ) * RAND() + 7 ),
0);
SELECT @NoPayInTemp = ROUND(( ( 23 - 18 - 1 ) * RAND() + 18 ), 0);
SELECT @NoPayOutTemp = ROUND(( ( 18 - 13 - 1 ) * RAND() + 13 ), 0);
SELECT @RoomTemp = ROUND(( ( @UpRoomTemp - @LowRoomTemp - 1 )
* RAND() + @LowRoomTemp ), 0);
SELECT @SetTemp = ROUND(( ( @UpRoomTemp - @LowRoomTemp - 1 ) * RAND()
+ @LowRoomTemp ), 0);
UPDATE dbo.t_LXT
--SET productRoomID = ROUND(RAND() * 100, 0)
SET roomTemp = @RoomTemp ,
setTemp = @SetTemp ,
tempStatus = '1' ,
outTemp = ( CASE @PaymentState
WHEN '1' THEN @PayedOutTemp
WHEN '0' THEN @NoPayOutTemp
END ) ,
inTemp = ( CASE @PaymentState
WHEN '1' THEN @PayedInTemp
WHEN '0' THEN @NoPayInTemp
END ) ,
--if 已缴费paymentState==1 供热setBoltStatus==0 开启 else setBoltStatus==1
setBoltStatus = ( CASE @PaymentState
WHEN '1' THEN '0'
ELSE '1'
END ) ,
--if 设定setTemp>实际roomTemp 阀门开启boltStatus==0 else 阀门关闭boltStatus==1
boltStatus = ( CASE WHEN @SetTemp < @RoomTemp THEN '1'
WHEN @SetBoltStatus = '1' THEN '1'
WHEN @PaymentState = '0' THEN '1'
ELSE '0'
END )
WHERE LXTID = @min_id; --round()四舍五入把原值转化为指定小数位数
SELECT @min_id = ( SELECT MIN(LXTID)
FROM dbo.t_LXT
WHERE LXTID > @min_id
);--rand()取得是随机数 默认范围为(0·1) rand()*100范围是0~100
END;
其他的需求都可以通过这两种进行改造。