统计数据的问题

[byj2001329]

假设表结构如下：
id int
Name varchar(20)
qty int

统计数据时按id排序，我想找到qty累计>=某数值的最小id

举例如下：
1 A 10
2 B 15
3 C 20
4 D 60
如果我想找到累计>=20的id ,则id=2
累计>=40的id ,则id=3
累计>=70的id ,则id=4
如果能同时返回累计值当然更好

[水族杰纶]

--> Title:生成測試數據
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-19 09:04:03
 
if not object_id('tb') is null
	drop table tb
Go
Create table tb([id] int,[Name] nvarchar(1),[qty] int)
Insert tb
select 1,N'A',10 union all
select 2,N'B',15 union all
select 3,N'C',20 union all
select 4,N'D',60
Go
select top 1 id from(
select [id],(select sum([qty])from tb where id<=t.id)cnt from tb t)
t where cnt>=20
select top 1 id from(
select [id],(select sum([qty])from tb where id<=t.id)cnt from tb t)
t where cnt>=70
/*
(4 個資料列受到影響)
id
-----------
2

(1 個資料列受到影響)

id
-----------
4

(1 個資料列受到影響)

*/

[让你望见影子的墙]

---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2009-08-19 09:05:16
---------------------------------
 
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (id INT,Name VARCHAR(1),qty INT)
INSERT INTO [tb]
SELECT 1,'A',10 UNION ALL
SELECT 2,'B',15 UNION ALL
SELECT 3,'C',20 UNION ALL
SELECT 4,'D',60

select * from [tb]

select min(id) from 
  (select *,cnt=(select sum(qty) from tb where id<=t.id)from tb t)K
where cnt>=20

[水族杰纶]

--> Title:生成測試數據
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-19 09:04:03
 
if not object_id('tb') is null
	drop table tb
Go
Create table tb([id] int,[Name] nvarchar(1),[qty] int)
Insert tb
select 1,N'A',10 union all
select 2,N'B',15 union all
select 3,N'C',20 union all
select 4,N'D',60
Go
-->=20
select top 1 id from tb t
where (select sum([qty])from tb where id<=t.id)>=20
-->=40
select top 1 id from tb t
where (select sum([qty])from tb where id<=t.id)>=40
-->=70
select top 1 id from tb t
where (select sum([qty])from tb where id<=t.id)>=70
/*
(4 個資料列受到影響)
id
-----------
2

(1 個資料列受到影響)

id
-----------
3

(1 個資料列受到影響)

id
-----------
4

(1 個資料列受到影響)
*/

[--小F--]

select 
  min(id) 
from 
  (select *,cnt=(select sum(qty) from tb where id<=t.id)from tb t)t
where 
  cnt>=20

[华夏小卒]

declare @a table
(id int ,
Name varchar(20) ,
qty int )
insert @a select
1, 'A' , 10 union all select
2, 'B' , 15 union all select
3, 'C' , 20 union all select
4, 'D' , 60 


declare @n int
set @n=20

select top 1 id from(
select id,name,qty,hz=(select sum(qty)from @a where id<=t.id) from @a t
)a
where hz>=@n
order by hz 

id
-----------
2

(1 行受影响)

[ch315537]

学习

[--小F--]

----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-08-19 09:11:57
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(1),[qty] int)
insert [tb]
select 1,'A',10 union all
select 2,'B',15 union all
select 3,'C',20 union all
select 4,'D',60
--------------开始查询--------------------------
-----20
select 
  min(id) 
from 
  (select *,cnt=(select sum(qty) from tb where id<=t.id)from tb t)t
where 
  cnt>=20
----40
select 
  min(id) 
from 
  (select *,cnt=(select sum(qty) from tb where id<=t.id)from tb t)t
where 
  cnt>=40
----70
select 
  min(id) 
from 
  (select *,cnt=(select sum(qty) from tb where id<=t.id)from tb t)t
where 
  cnt>=70

----------------结果----------------------------
/*
            
----------- 
2

（所影响的行数为 1 行）

            
----------- 
3

（所影响的行数为 1 行）

            
----------- 
4

（所影响的行数为 1 行）

*/

[lihan6415151528]

[code=SQL

]
select min(id) from
(select *,cnt=(select sum(qty) from tb where id<=t.id)from tb t)K
where cnt>=20
[/code]