SQL 怎么利用游标做加法

发布网友发布时间：2022-04-11 17:21

共2个回答

热心网友时间：2022-04-11 18:50

create table table2
(
id_2 int identity primary key,
name_2 varchar(20) not null,
counts int
一定要游标么。。。

insert into table2 values('张三',10)
insert into table2 values('张三',20)
insert into table2 values('李四',1)
insert into table2 values('李四',2)
insert into table2 values('李四',3)

select name_2 as'姓名',sum(counts)as '次数' from table2 group by name_2

（等下我帮你写一个。。）

我用游标做了。。。这次没错了！

//这是第一个表。。
create table table2
(
id_2 int identity primary key,
name_2 varchar(20) not null,
counts int
)

insert into table2 values('张三',10)
insert into table2 values('张三',20)
insert into table2 values('李四',1)
insert into table2 values('李四',2)
insert into table2 values('李四',3)

//这是结果表
create table newTable
(
id_ int identity primary key,
name_ varchar(20) not null,
counts int
)
//游标
declare cursor_table cursor for select name_2 as'姓名',sum(counts)as '次数' from table2 group by name_2
open cursor_table
declare @name varchar(20),@count int
fetch next from cursor_table into @name,@count
while @@fetch_status=0
begin
begin
insert into newTable values(@name,@count)
end
fetch next from cursor_table into @name,@count
end

close cursor_table

//查看结果表。
select * from newTable

热心网友时间：2022-04-11 20:08

假定你的表叫table1,两个字段分别是name,val name是varchar类型,val是reall类型,要插入的新表叫tablenew，有相同的表结构
declare @name varchar(255);
declare @val real;
declare @tempname varchar(255);
declare @tempval real;
DECLARE cur_add CURSOR
FOR SELECT name,val from table1 order by name;
OPEN cur_add ;
FETCH NEXT FROM cur_add INTO @name,@val;
set @tempname=@name;
set @tempval=@val;
WHILE (@@FETCH_STATUS=0)
BEGIN
FETCH NEXT FROM cur_add INTO @name,@val;
if @name<>@tempname
begin
insert into tablenew(name,val) values(@tempname,@tempval);
set @tempname=@name;
set @tempval=@val;
end
else
begin
set @tempval=@tempval+@val;
end;
END;
CLOSE cur_add;
DEALLOCATE cur_add;