sql server 遍历 sql遍历数据

步骤：

1：先定义一个临时表，把需要用的数据放入临时表中，如果数据不连续，则在临时表中定义一个自增长键

 

DECLARE @temp table(
Id INT IDENTITY(1, 1) ,
ShopCode UNIQUEIDENTIFIER,
CustCode UNIQUEIDENTIFIER,
CardMoney DECIMAL,
CardGiftMoney DECIMAL,
CreateTime DATETIME
)
INSERT INTO @temp 
select ShopCode,CustCode,CardMoney,CardGiftMoney,CreateTime from HL11.dbo.t_ShopSerLog 
where CreateTime>='2017-07-01' and CreateTime<'2017-07-02' and [Status]=1 and DeleFlag=1 
and ShopCode
in
(
'EE034CB5-EF4B-4E5B-928D-2D3F733D5B4A',
'667557E2-D5A9-43FE-A18A-21DD68E19207'
)
and (CardGiftMoney>0 or CardMoney>0)

 

2：定义两个int类型的字段，一个用于判断当前运行的行数，一个用于存放临时表中需要遍历的总行数

 

DECLARE @rowcount INT 
DECLARE @allcount INT 
SET @rowcount=1
SET @allcount=(select COUNT(1) from HL11.dbo.t_ShopSerLog 
where CreateTime>='2017-07-01' and CreateTime<'2017-07-02' and [Status]=1 and DeleFlag=1 
and ShopCode
in
(
'EE034CB5-EF4B-4E5B-928D-2D3F733D5B4A',
'667557E2-D5A9-43FE-A18A-21DD68E19207'
)
and (CardGiftMoney>0 or CardMoney>0))

 

 

3：根据自增长键以及2里面定义的两个字段，对数据进行遍历处理

DECLARE @Id INT 
DECLARE @ShopCode UNIQUEIDENTIFIER
DECLARE @CustCode UNIQUEIDENTIFIER
DECLARE	@CardMoney DECIMAL
DECLARE	@CardGiftMoney DECIMAL
DECLARE	@CreateTime DATETIME
SET @Id=1
WHILE(@rowcount<=@allcount)
BEGIN 
select TOP 1 @ShopCode=ShopCode,@CustCode=CustCode,@CardMoney=CardMoney,@CardGiftMoney=CardGiftMoney,@CreateTime=CreateTime from @temp where Id=@Idupdate HL10.dbo.t_CustVCard set PaidAmount=PaidAmount-@CardMoney,GiftAmount=GiftAmount-@CardGiftMoney,
LastUsedTime=@CreateTime where ShopCode=@ShopCode and CustUniqCode=@CustCodeSET @Id=@Id+1
SET @rowcount=@rowcount+1END

 

4：总的代码如下

DECLARE @temp table(
    Id INT IDENTITY(1, 1) ,
    ShopCode  UNIQUEIDENTIFIER,
    CustCode UNIQUEIDENTIFIER,
	CardMoney DECIMAL,
	CardGiftMoney DECIMAL,
	CreateTime DATETIME
)
INSERT INTO @temp 
select ShopCode,CustCode,CardMoney,CardGiftMoney,CreateTime from HL11.dbo.t_ShopSerLog 
where CreateTime>='2017-07-01' and CreateTime<'2017-07-02' and [Status]=1 and DeleFlag=1 
and ShopCode
in
(
'EE034CB5-EF4B-4E5B-928D-2D3F733D5B4A',
'667557E2-D5A9-43FE-A18A-21DD68E19207'
)
and (CardGiftMoney>0 or CardMoney>0)DECLARE @rowcount INT 
DECLARE @allcount INT 
SET @rowcount=1
SET @allcount=(select COUNT(1) from HL11.dbo.t_ShopSerLog 
where CreateTime>='2017-07-01' and CreateTime<'2017-07-02' and [Status]=1 and DeleFlag=1 
and ShopCode
in
(
'EE034CB5-EF4B-4E5B-928D-2D3F733D5B4A',
'667557E2-D5A9-43FE-A18A-21DD68E19207'
)
and (CardGiftMoney>0 or CardMoney>0))
DECLARE @Id INT 
DECLARE    @ShopCode  UNIQUEIDENTIFIER
 DECLARE   @CustCode UNIQUEIDENTIFIER
DECLARE	@CardMoney DECIMAL
DECLARE	@CardGiftMoney DECIMAL
DECLARE	@CreateTime DATETIME
SET @Id=1
 WHILE(@rowcount<=@allcount)
 BEGIN 
     select TOP 1 @ShopCode=ShopCode,@CustCode=CustCode,@CardMoney=CardMoney,@CardGiftMoney=CardGiftMoney,@CreateTime=CreateTime from @temp where Id=@Id update HL10.dbo.t_CustVCard set PaidAmount=PaidAmount-@CardMoney,GiftAmount=GiftAmount-@CardGiftMoney,
 LastUsedTime=@CreateTime where ShopCode=@ShopCode and CustUniqCode=@CustCode SET @Id=@Id+1
 SET @rowcount=@rowcount+1 END