Create table ywxx([spbm] char(10),[nsjg] numeric(18,4))
Insert ywxx
select '0101000100',2.1 union all
select '0101000200',2.1 union all
select '0101000300',2.1 union all
select '0101000400',2.1 union all
select '0101000500',2.1
Godeclare @zje numeric(18,4)
declare @spbm char(10)
declare @nsjg numeric(18,4)
select @zje=100
select top 0 spbm,nsjg into resultB from ywxx
set nocount on
while @zje>=0
begin
set @spbm = null --这里变量赋个初始值
select top 1 @spbm=spbm,@nsjg=nsjg from ywxx yw where not exists(select 1 from resultB where spbm=yw.spbm)
if @spbm is not null
begin
insert into resultB values(@spbm,@nsjg)
select @zje=@zje-@nsjg
print @zje
end
else
begin
print '金额不够'
goto xx
end
end
xx:
drop table ywxx
select * from resultB a
drop table resultB
/*
spbm nsjg
---------- --------------------
0101000100 2.1000
0101000200 2.1000
0101000300 2.1000
0101000400 2.1000
0101000500 2.1000
*/
Insert ywxx
select '0101000100',2.1 union all
select '0101000200',2.1 union all
select '0101000300',2.1 union all
select '0101000400',2.1 union all
select '0101000500',2.1
Godeclare @zje numeric(18,4)
declare @spbm char(10)
declare @nsjg numeric(18,4)
select @zje=100
select top 0 spbm,nsjg into resultB from ywxx
set nocount on
while @zje>=0
begin
set @spbm = null --这里变量赋个初始值
select top 1 @spbm=spbm,@nsjg=nsjg from ywxx yw where not exists(select 1 from resultB where spbm=yw.spbm)
if @spbm is not null
begin
insert into resultB values(@spbm,@nsjg)
select @zje=@zje-@nsjg
print @zje
end
else
begin
print '金额不够'
goto xx
end
end
xx:
drop table ywxx
select * from resultB a
drop table resultB
/*
spbm nsjg
---------- --------------------
0101000100 2.1000
0101000200 2.1000
0101000300 2.1000
0101000400 2.1000
0101000500 2.1000
*/
解决方案 »
- 最后50分,从未遇过的一个问题,请高手进。急。
- sql语句,怎么实现,在线等
- 帮忙看看这个存储过程怎么改?
- 为什么通过企业管理器添加int属性的字段设置默认值为0时,如果添加为最后一个字段则已存在记录中该字段都显示为null,如果插入中间才显示
- 怎么从visio导入到sql server
- 如何根据 timestamp 字段查询数据
- select into 的问题(在线等)
- 请问如何检测及显示SQL SERVER 2000 的Northwind数据库下Empolyees表中的Photo字段数据 (image型).
- 如果一个人有操作多个模块或多个对象的权限,但他在同一时刻只能操作一个,请问,有什么好的办法来控制???
- 如何插入如下的结果集???
- 使用了not exists 为什么#resultB结果表中还有重复行
- 数据库查询
Insert ywxx
select '0101000100',2.1 union all
select '0101000200',2.1 union all
select '0101000300',2.1 union all
select '0101000400',2.1 union all
select '0101000500',2.1
Go declare @zje numeric(18,4)
declare @spbm char(10)
declare @nsjg numeric(18,4)
select @zje=100
select top 0 spbm,nsjg into resultB from ywxx
set nocount on
while @zje>=0
begin select top 1 @spbm=spbm,@nsjg=nsjg from ywxx yw where not exists(select 1 from resultB where spbm=yw.spbm)
if @@rowcount > 0 -- is not null
begin
insert into resultB values(@spbm,@nsjg)
select @zje=@zje-@nsjg
print @zje
end
else
begin
print '金额不够'
goto xx
end end
xx:
drop table ywxx
select * from resultB a
drop table resultB
97.9000
95.8000
93.7000
91.6000
89.5000
金额不够
spbm nsjg
---------- ---------------------------------------
0101000100 2.1000
0101000200 2.1000
0101000300 2.1000
0101000400 2.1000
原来我没把select @xx=xx from xx 中如果行不存在的返回值搞明白,
如果行存在就改写@xx的值,如果不存在,@xx是保存原来值的,我以为不存在会使@xx为null