好像是装箱问题，就是寻找到装满即可

[cnwolfs]

代码要求效率较高，最不要用游标

CREATE TABLE #Temp(
	ID int,
	Price numeric(18,4),
)

Insert Into #Temp(ID,Price)
Select 1 as ID, 20 as Price
Union All
Select 2 as ID, 15 as Price
Union All
Select 3 as ID, 10 as Price
Union All
Select 4 as ID, 20 as Price
Union All
Select 5 as ID, 25 as Price
Union All
Select 6 as ID, 50 as Price
Union All
Select 7 as ID, 40 as Price
Union All
Select 8 as ID, 20 as Price

--想取出指定Price汇总值达到某个数字后的记录，记录可以是随机的，
--例如金额达到100之后的记录，记录只要按照排序规则取前面的若干条记录即可，
--例如本案例按照ID排列，取前面的6条即可满足我的要求
--目的是取出最后一条记录我要用来拆分出不同的支付方式


Select * From #Temp
Drop Table #Temp

[铁歌]

CREATE TABLE #Temp(
    ID int,
    Price numeric(18,4),
)
 
Insert Into #Temp(ID,Price)
Select 1 as ID, 20 as Price
Union All
Select 2 as ID, 15 as Price
Union All
Select 3 as ID, 10 as Price
Union All
Select 4 as ID, 20 as Price
Union All
Select 5 as ID, 25 as Price
Union All
Select 6 as ID, 50 as Price
Union All
Select 7 as ID, 40 as Price
Union All
Select 8 as ID, 20 as Price
 
--想取出指定Price汇总值达到某个数字后的记录，记录可以是随机的，
--例如金额达到100之后的记录，记录只要按照排序规则取前面的若干条记录即可，
--例如本案例按照ID排列，取前面的6条即可满足我的要求
--目的是取出最后一条记录我要用来拆分出不同的支付方式
 
 
Select * From #Temp

declare @cmoney numeric(18,4),@pricesum numeric(18,4)

declare @i int, --loop counter;
		@topN int, --returns top n numbers 
		@max int --returns loops max value
		
		
select  @i=1, @topN=0, @max=0,@cmoney =100,@pricesum=0

while @i<  (select MAX(id) from #Temp)
begin

	select @pricesum =@pricesum+price from #Temp where ID = @i
		IF @cmoney <@pricesum
		begin
			set @topN = @i
			break
		end 

	SET @i= @i+1
end

select * from #Temp where ID <= @topN




Drop Table #Temp

ID Price 1 20.0000 2 15.0000 3 10.0000 4 20.0000 5 25.0000 6 50.0000

[524929657雯]

[huangjing81]

CREATE TABLE #Temp(
    ID int,
    Price int,
)
  
Insert Into #Temp(ID,Price)
Select 1 as ID, 20 as Price
Union All
Select 2 as ID, 15 as Price
Union All
Select 3 as ID, 10 as Price
Union All
Select 4 as ID, 20 as Price
Union All
Select 5 as ID, 25 as Price
Union All
Select 6 as ID, 50 as Price
Union All
Select 7 as ID, 40 as Price
Union All
Select 8 as ID, 20 as Price

;
with cte as 
(
select ID,price,Price as Total from #Temp
	where ID=1
union all
select t.ID,t.price,t.Price+cte.Total    from #Temp t join cte on t.ID=cte.ID+1
)

select * from cte where ID<=(select top 1 ID from cte where Total>100)

drop table #Temp

[cnwolfs]

多谢多谢，下班后测试下，前段时间出差了很久

[cnwolfs]

二楼的使用while了， 四楼的 t.ID=cte.ID+1这条语句貌似不符合我的要求，因为ID有可能不连续。我回去测试测试

[huangjing81]

那就用下边这个

select ID,price,(select SUM(Price) from #Temp where ID<=t.ID) as total
	from #Temp t

[cnwolfs]

引用 6 楼 huangjing81 的回复:

那就用下边这个

select ID,price,(select SUM(Price) from #Temp where ID<=t.ID) as total
	from #Temp t

结果是： 1 20.0000 20.0000 2 15.0000 35.0000 3 10.0000 45.0000 4 20.0000 65.0000 5 25.0000 90.0000 6 50.0000 140.0000 7 40.0000 180.0000 8 20.0000 200.0000 还是没能取出指定金额达到100所涉及的记录 例如我就需要这些记录 1 20.0000 20.0000 2 15.0000 35.0000 3 10.0000 45.0000 4 20.0000 65.0000 5 25.0000 90.0000 6 50.0000 140.0000

[huangjing81]

引用 7 楼 cnwolfs 的回复:

[quote=引用 6 楼 huangjing81 的回复:] 那就用下边这个

select ID,price,(select SUM(Price) from #Temp where ID<=t.ID) as total
	from #Temp t

结果是： 1 20.0000 20.0000 2 15.0000 35.0000 3 10.0000 45.0000 4 20.0000 65.0000 5 25.0000 90.0000 6 50.0000 140.0000 7 40.0000 180.0000 8 20.0000 200.0000 还是没能取出指定金额达到100所涉及的记录 例如我就需要这些记录 1 20.0000 20.0000 2 15.0000 35.0000 3 10.0000 45.0000 4 20.0000 65.0000 5 25.0000 90.0000 6 50.0000 140.0000 [/quote] 你自己加条件啊，最后那个字段的值就是前面所有记录的总和

[cnwolfs]

total 这个字段是算出来的，怎么加条件？而且我要的是当total刚刚大于100时候的所有记录，问题<100是不正确的，这个例子来说，应该是<=140，这样的条件该怎么写？

[huangjing81]

把140的ID找出来，然后小于此ID

[huangjing81]

小于等于此ID