求一条语句，比较有难度

[giraffedl]

字段如下：
班级ID 人数
001 2
002 3
003 8
--------------
怎么能用一个脚本选择出当人数字段总和大于10时候的班级id列表
比如说按照上面的数据则选出所有3个班级（因为三个班级的人数总和为2+3+8 = 13 > 10）
如果要求改为总和大于4则选出两个班级（因为三个班级的人数总和为2+3 = 5 > 4）

[soft_wsx]

最好贴出结果

[liangCK]

-------------------------------------------
--  Author : liangCK 小梁
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-09-07 14:17:54
-------------------------------------------
 
--> 生成测试数据: @tb
DECLARE @tb TABLE (班级ID VARCHAR(3),人数 INT)
INSERT INTO @tb
SELECT '001',2 UNION ALL
SELECT '002',3 UNION ALL
SELECT '003',8

--SQL查询如下:

SELECT * FROM @tb AS A
WHERE ISNULL((SELECT SUM(人数) FROM @tb WHERE 班级ID <A.班级ID),0) <= 10;

SELECT * FROM @tb AS A
WHERE ISNULL((SELECT SUM(人数) FROM @tb WHERE 班级ID <A.班级ID),0) <= 4;

/*
班级ID 人数
---- -----------
001  2
002  3
003  8

(3 row(s) affected)

班级ID 人数
---- -----------
001  2
002  3

(2 row(s) affected)
*/

[华夏小卒]

不懂

[xiequan2]

--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([班级ID] varchar(3),[人数] int)
insert [t1]
select '001',2 union all
select '002',3 union all
select '003',8

declare @i int
set @I=10
select [班级ID] , [人数]   from
(
select *,总人数=(select isnull(sum(人数),0) from t1 where [班级ID]<t.[班级ID] ) from [t1] t 
) tmp
where  总人数<@i

[playwarcraft]

create table T(班级ID char(03),  人数  int)
insert into T select '001',      2 
insert into T select '002',      3 
insert into T select '003',      8 


declare @max int
set @max=10  --判断数字

select * from T as A
where isnull((select sum(人数) from T where 班级ID<A.班级ID),0) <= @max

drop table T

[lihan6415151528]

[Quote=引用 5 楼 playwarcraft 的回复:]
SQL codecreatetable T(班级IDchar(03), 人数int)insertinto Tselect'001',2insertinto Tselect'002',3insertinto Tselect'003',8declare@maxintset@max=10--判断数字select*from Tas Awhereisnull((selectsum(人数)from Twhe¡­
[/Quote]
up.

[pl_mm]

-->Title:生成測試數據
-->Author:pl_mm(风吹屁屁凉~)
-->Date :2009-09-07 14:15:45
 
if not object_id('tb') is null
	drop table tb
Go
Create table tb([班级ID] nvarchar(3),[人数] int)
Insert tb
select N'001',2 union all
select N'002',3 union all
select N'003',8
Go
select * from tb where [班级ID]<=
(select  top 1 [班级ID]
from tb t
where (select sum([人数])from tb where [班级ID]<=t.[班级ID])>10)
/*
班级ID 人数
---- -----------
001  2
002  3
003  8

(3 個資料列受到影響)

*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-09-07 14:19:55
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([班级ID] varchar(3),[人数] int)
insert [tb]
select '001',2 union all
select '002',3 union all
select '003',8
--------------开始查询--------------------------
select 
*  
from
(
select *,总人数=(select isnull(sum(人数),0) from tb where [班级ID]<t.[班级ID] ) from [tb] t 
) t
where  
总人数<10
----------------结果----------------------------
/* 班级ID 人数          总人数
---- ----------- -----------
001  2           0
002  3           2
003  8           5

(3 行受影响)
*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-09-07 14:19:55
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([班级ID] varchar(3),[人数] int)
insert [tb]
select '001',2 union all
select '002',3 union all
select '003',8
--------------开始查询--------------------------
select 
  班级ID,人数  
from
(
select *,总人数=(select isnull(sum(人数),0) from tb where [班级ID]<t.[班级ID] ) from [tb] t 
) t
where  
  总人数<10
----------------结果----------------------------
/* 班级ID 人数
---- -----------
001  2
002  3
003  8

(3 行受影响)
*/

[guguda2008]

我只是不明白大于10的为什么不是3+8