循环记录--------可以用游标的给你一个例子,参考参考如何实现以下存储过程:有表A(type, money, allow), B(money, time)
情景:根据A表中type类型进行扣费,将扣费记录存到B中。
过程:读取A各数据,如果type=1则:
1)如果money<=100,则插入money(A中money值),time(取当前时间)到B中,
并更新A中allow=yes;
2)如果money>100,继续。感激得很!
------------生成@A表----------
declare @A table(type varchar(20),money1 money,allow varchar(20))
insert @A(type,money1,allow)
values('1',105,'')
insert @A(type,money1,allow)
values('2',105,'')
insert @A(type,money1,allow)
values('2',105,'')
insert @A(type,money1,allow)
values('1',80,'')
insert @A(type,money1,allow)
values('1',50,'')
select * from @A
-----------------------------------------生成@B表----------
declare @B table(money2 money,time2 datetime)select * from @B
------------------------------------------------------------处理过程-----------------------------
declare @money money
declare @type varchar(20)declare Type_Cursor CURSOR FOR
select type,money1 from @A-- where 条件
open Type_Cursor
fetch next from Type_Cursor into @type,@money
while @@FETCH_STATUS=0
begin
if @type=1
begin
if @money<=100
begin
insert @B(money2,time2) values(@money,getdate())
update @A set allow='yes' where type=@type and money1=@money --条件保持唯一
end
end
fetch next from Type_Cursor into @type,@money
end
close Type_Cursor
deallocate Type_Cursor
-------------------------------------------------
------------结果--------------
select * from @A
select * from @B将表名@A,@B,和对应的字段改好就可以了。
情景:根据A表中type类型进行扣费,将扣费记录存到B中。
过程:读取A各数据,如果type=1则:
1)如果money<=100,则插入money(A中money值),time(取当前时间)到B中,
并更新A中allow=yes;
2)如果money>100,继续。感激得很!
------------生成@A表----------
declare @A table(type varchar(20),money1 money,allow varchar(20))
insert @A(type,money1,allow)
values('1',105,'')
insert @A(type,money1,allow)
values('2',105,'')
insert @A(type,money1,allow)
values('2',105,'')
insert @A(type,money1,allow)
values('1',80,'')
insert @A(type,money1,allow)
values('1',50,'')
select * from @A
-----------------------------------------生成@B表----------
declare @B table(money2 money,time2 datetime)select * from @B
------------------------------------------------------------处理过程-----------------------------
declare @money money
declare @type varchar(20)declare Type_Cursor CURSOR FOR
select type,money1 from @A-- where 条件
open Type_Cursor
fetch next from Type_Cursor into @type,@money
while @@FETCH_STATUS=0
begin
if @type=1
begin
if @money<=100
begin
insert @B(money2,time2) values(@money,getdate())
update @A set allow='yes' where type=@type and money1=@money --条件保持唯一
end
end
fetch next from Type_Cursor into @type,@money
end
close Type_Cursor
deallocate Type_Cursor
-------------------------------------------------
------------结果--------------
select * from @A
select * from @B将表名@A,@B,和对应的字段改好就可以了。
类别表 A
id typename roottypeid(父结点号对吧?) 新闻表 news
id title typeid --代码:
declare @id int,@l int
set @l=0
set @id=0
create table #t (id int,roottypeid,int,l int)
insert into #t select id, roottypeid,@l from 表a where id=@id
set @l=@l+1
while @@rowcount>0
begin
insert into #t select m.id, m.roottypeid,@l from 表a m ,#t n where m.roottypeid=n.id and n.l=@l-1
set @l=@l+1
end--得到结点0下所有子结点的新闻(包括0)
select * from 新闻表news where id in(select id from #t)drop table #t