使用游标写的查询，求改写成一条SQL语句
CREATE TABLE [typeTable] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[type] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[value] [int] NOT NULL ,
CONSTRAINT [PK_typeTable] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO typeTable VALUES( 'b ', 10);
INSERT INTO typeTable VALUES( 'a ', 10);
INSERT INTO typeTable VALUES( 'b ', 5)
INSERT INTO typeTable VALUES( 'a ', 15)
INSERT INTO typeTable VALUES( 'a ', 10)
INSERT INTO typeTable VALUES( 'a ', 30)
INSERT INTO typeTable VALUES( 'a ', 20)
--------------------
create table #tempTable([id] [int],
[type] [varchar](10),
[value] [int])
declare @type varchar(20)
declare Mydec cursor for select distinct(type) from typeTable
open Mydec
fetch next from Mydec into @type
while(@@fetch_status = 0)
begin
insert into #tempTable select * from typeTable where id in
(select id from
(select id,type,[sum]=(select sum([value])from (select * from typeTable where type = @type) C where id!> A.id and A.type= @type)
from typeTable A ) B where B.[sum] !> 30)
fetch next from Mydec into @type
end
close Mydec
deallocate Mydec
select * from #tempTable
drop table #tempTable
---------------------------------------------
drop table [typeTable]
求：中间的用一条SQL语句实现。。
实现功能： 查出每个type的value之和不大于30的记录
结果：
id type value
2 a 10
4 a 15
1 b 10
3 b 5
不知道我表达的大家能不能看懂！
[解决办法]
CREATE TABLE [typeTable] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[type] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[value] [int] NOT NULL ,
CONSTRAINT [PK_typeTable] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO typeTable VALUES( 'b ', 10);
INSERT INTO typeTable VALUES( 'a ', 10);
INSERT INTO typeTable VALUES( 'b ', 5)
INSERT INTO typeTable VALUES( 'a ', 15)
INSERT INTO typeTable VALUES( 'a ', 10)
INSERT INTO typeTable VALUES( 'a ', 30)
INSERT INTO typeTable VALUES( 'a ', 20)
select * from typeTable a
where value+(select isnull(sum(value),0) from typeTable
where type=a.type and id <a.id) <30
drop table [typeTable]
