按最大值参数搜索前n项的最大id

[shunzimm]

declare @t table (id int,amount money)
insert into @t(id,amount) values(1,100)
insert into @t(id,amount) values(2,100)
insert into @t(id,amount) values(3,100)
insert into @t(id,amount) values(4,100)

declare @amount money
set @amount = 250
求当@amount = 250 时 前多少项的amount列合计值不超过@amount
@amount = 250 得到 2
@amount = 359 得到 3

[kisa99]

请教一下：

select count(1) as 项数 from
(select id,amount,sum_amount= (select sum(amount) from @t where id <= t1.id)
from @t t1
)a where sum_amount <= @amount ----a 是什么意思？


谢谢！！

[fcuandy]

给 (select id,amount,sum_amount= (select sum(amount) from @t where id <= t1.id)
from @t t1
)
这个子查询一个别名 a
select count(1) as 项数 from
(select id,amount,sum_amount= (select sum(amount) from @t where id <= t1.id)
from @t t1
)a where sum_amount <= @amount
这句就相当于

select count(1) as 项数 from a where sum_amount <= @amount
此时a中的行就是那个子查询产行的行

或者你将它理解为一个虚拟表也可以。

[kisa99]

明白了！谢谢 fcuandy！！

[LouisXIV]

select max(id) from @t a where @amount>(select sum(amount) from @t where id<=a.id)

[fcuandy]

declare @t table (id int,amount money)
insert into @t(id,amount) values(1,100)
insert into @t(id,amount) values(2,100)
insert into @t(id,amount) values(3,100)
insert into @t(id,amount) values(4,100)

SELECT MAX(ID) FROM (select *,aa=(SELECT SUM(amount) FROM @t b WHERE b.id<=a.id) from @t a) base
WHERE aa<359

[paoluo]

declare @t table (id int,amount money)
insert into @t(id,amount) values(1,100)
insert into @t(id,amount) values(2,100)
insert into @t(id,amount) values(3,100)
insert into @t(id,amount) values(4,100)

declare @amount money
set @amount = 250
Select TOP 1 idCount From(
Select *,(Select SUM(amount) From @t Where id<=A.id) As SUMamount,(Select Count(id) From @t Where id<=A.id) As idCount From @t A) B
Where SUMamount<=@amount Order By id Desc

[冷箫轻笛]

select count(1) as 项数 from
(select id,amount,sum_amount= (select sum(amount) from @t where id <= t1.id)
from @t t1
)a where sum_amount <= @amount

[paoluo]

LouisXIV(夜游神),fcuandy(要学的东西还很多),如果id不連續呢？？

[冷箫轻笛]

楼上好快啊！

[paoluo]

LouisXIV(夜游神),fcuandy(要学的东西还很多),如果id不連續呢？？樓主說的是行數的。

[paoluo]

弄複雜了



declare @t table (id int,amount money)
insert into @t(id,amount) values(1,100)
insert into @t(id,amount) values(2,100)
insert into @t(id,amount) values(3,100)
insert into @t(id,amount) values(4,100)

declare @amount money
set @amount = 250
Select Count(*) As Count From(
Select *,(Select SUM(amount) From @t Where id<=A.id) As SUMamount From @t A) B
Where SUMamount<=@amount

[fcuandy]

id不需要连续。 只要不重复，并且下面的行比上面的行的ID值大就行。
否则，你写的那句也不对(因为你的语句也是以Id作标识用的,形如Where id<=A.id,ORDER BY ID DESC等等)。 就只能用临时表借助IDENTITY或用其它方法了。

你教我的，不用考滤太多，呵呵

[fcuandy]

其实以我严谨的思维早考滤这些了。

不过我经常搞错是因为：虽然我有很严谨的思维但是有一双不听使唤的手.^^

[fcuandy]

看错楼主意思了。他要取行数，不是取ID， 将MAX改为COUNT就可以了。晕

[LouisXIV]

^^

不过个人觉得严谨的思维也就自己的课题才有意义。

因为只有自己的课题才知道自己到底要达到什么目的，有什么意外情况需要处理。

CSDN上答题其实只要给出一个大致的思路，没有逻辑偏差就可以了。

至于什么意外情况应该让LZ自己去处理才是，毕竟我们看到的只是LZ自己的课题并按照LZ自己的思路抽象出来的题目

[marco08]

select max(id) from @t a
where @amount>
(select sum(amount) from @t b where b.id<=a.id
group by amount)