select * from table1结果如下en_pro_id en_equ_id en_type_id
----------- ----------- -------------
113 67 058
114 67 070
115 71 071
116 69 072现在定义了后面两列的变量,想从上至下进行循环赋值为了大家能更好理解我的意思,伪代码:
declare @en_equ_id,@en_type_idfor(int i = 0;i < table1.行数;i++)
{
@en_equ_id = table1[en_equ_id]
@en_type_id = table1[en_type_id]
} 请问存储过程该怎么写
----------- ----------- -------------
113 67 058
114 67 070
115 71 071
116 69 072现在定义了后面两列的变量,想从上至下进行循环赋值为了大家能更好理解我的意思,伪代码:
declare @en_equ_id,@en_type_idfor(int i = 0;i < table1.行数;i++)
{
@en_equ_id = table1[en_equ_id]
@en_type_id = table1[en_type_id]
} 请问存储过程该怎么写
if not object_id('tb') is null
drop table tb
Go
Create table tb([en_pro_id] int,[en_equ_id] int,[en_type_id] nvarchar(3))
Insert tb
select 113,67,N'058' union all
select 114,67,N'070' union all
select 115,71,N'071' union all
select 116,69,N'072'
Go
declare @i int
select @i=0
update tb set [en_equ_id]=@i,[en_type_id]=@i,@i=@i+1
select * from tb
/*
en_pro_id en_equ_id en_type_id
----------- ----------- ----------
113 1 1
114 2 2
115 3 3
116 4 4(4 個資料列受到影響)
*/
declare @en_pro_id int ;
declare @en_equ_id int , @en_type_id int;while 2 > 1
begin
set @en_pro_id = NULL;
set @en_equ_id = NULL;
set @en_type_id = NULL; select top 1 @en_pro_id=en_pro_id, @en_equ_id=en_equ_id, @en_type_id=en_type_id from table1 where en_pro_id>isnull(@en_pro_id,0); if @en_pro_id is null
begin
break;
end
else
begin
print @en_equ_id;
print @en_type_id;
end
end
declare @en_equ_id int , @en_type_id int;while 2 > 1
begin
--set @en_pro_id = NULL;
set @en_equ_id = NULL;
set @en_type_id = NULL; select top 1 @en_pro_id=en_pro_id, @en_equ_id=en_equ_id, @en_type_id=en_type_id from table1 where en_pro_id>isnull(@en_pro_id,0);
if @en_equ_id is null
begin
break;
end
else
begin
print @en_equ_id;
print @en_type_id;
end
end
如果是全部数据,简单
declare @en_equ_id,@en_type_id
select @en_equ_id=sum(en_equ_id),@en_type_id=sum(en_type_id)