declare @aa int declare @bb int
declare @name varchar(100)
select @aa=0
DECLARE titles_cursor CURSOR FOR
SELECT 金额,用户名 FROM A order by 金额 desc
OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @bb,@name
select @aa=@aa+@bb
if @aa>=110
print @name
FETCH NEXT FROM titles_cursor INTO @bb,@name CLOSE titles_cursor
DEALLOCATE titles_cursor
declare @name varchar(100)
select @aa=0
DECLARE titles_cursor CURSOR FOR
SELECT 金额,用户名 FROM A order by 金额 desc
OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @bb,@name
select @aa=@aa+@bb
if @aa>=110
print @name
FETCH NEXT FROM titles_cursor INTO @bb,@name CLOSE titles_cursor
DEALLOCATE titles_cursor
declare @name varchar(100)
select @aa=0
DECLARE titles_cursor CURSOR FOR
SELECT 金额,用户名 FROM A order by 金额 desc
OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @bb,@name
select @aa=@aa+@bb
if @aa<=110------这里应该这样的
print @name
FETCH NEXT FROM titles_cursor INTO @bb,@name CLOSE titles_cursor
DEALLOCATE titles_cursor
(
[用户名] varchar(10),
[金额] int
)
insert @tb
select '张三',15 union
select '李四',30 union
select '王五',10 union
select '赵六',40 union
select '孙七',50 union
select '马八',60--测试
select [ID]=identity(int,1,1),* into # from @tb order by [金额] descselect [人数]=max(ID)
from
(
select A.ID
from # A
full join # B on A.ID<=B.ID
group by A.ID
having sum(B.[金额])>=110
)tdrop table #--结果
/*
人数
-----------
2(1 row(s) affected)
*/
create table #A(用户名 varchar(10),金额 int)
insert into #A select '张三',15
insert into #A select '李四',30
insert into #A select '王五',10
insert into #A select '赵六',40
insert into #A select '孙七',50
insert into #A select '马八',60--执行查询
declare @i int,@count int,@sum int
set @i = 0
set @count = 110
set @sum = 0select
@i=case when @sum < @count @i+1 else @i end,
@sum = @sum + 金额
from
#Aselect 人数=@i--输出结果
人数
----
5
(
[用户名] varchar(10),
[金额] int
)
insert @tb
select '张三',15 union
select '李四',30 union
select '王五',10 union
select '赵六',40 union
select '孙七',50 union
select '马八',60 union
select '马二',20--测试
select [ID]=identity(int,1,1),* into # from @tb order by [金额] descselect [人数]=min(ID)
from
(
select A.ID
from # A
full join # B on A.ID>=B.ID
group by A.ID
having sum(B.[金额])>=110
)tdrop table #--结果
/*
人数
-----------
2(1 row(s) affected)
*/
declare @tb table(name varchar(20),mon int)
insert into @tb
select 'zhang',15 union
select 'li', 30 union
select 'wang',10 union
select 'zhao',40 union
select 'song',50 union
select 'ma', 60declare @a int
select @a=100
--处理语句,因为考虑到mon值可能相同,所以采用两者相加作为唯一值。
/*
select *
from @tb a
where (case when (select sum(mon) from @tb where convert(varchar,mon)+name>=convert(varchar,a.mon)+a.name)>=@a
then (case when (select sum(mon) from @tb where convert(varchar,mon)+name>convert(varchar,a.mon)+a.name)>=@a
then 2 else 1 end)
else 0 end)<2
order by mon desc,name
--符合条件的记录
name mon
------------------------
ma 60
song 50
下边为统计人数
*/select 人数=count(*)
from
(select top 100 percent name
from @tb a
where (case when (select sum(mon) from @tb where convert(varchar,mon)+name>=convert(varchar,a.mon)+a.name)>=@a
then (case when (select sum(mon) from @tb where convert(varchar,mon)+name>convert(varchar,a.mon)+a.name)>=@a
then 2 else 1 end)
else 0 end)<2
order by mon desc,name ) b
/*结果人数
--------------
2
*/
-----------------------------------------------
--生成测试数据
create table #A(用户名 varchar(10),金额 int)
insert into #A select '张三',15
insert into #A select '李四',30
insert into #A select '王五',10
insert into #A select '赵六',40
insert into #A select '孙七',50
insert into #A select '马八',60--执行查询
declare @i int,@count int,@sum int
set @i = 0
set @count = 110
set @sum = 0select
@i=case when @sum < @count then @i+1 else @i end,
@sum = @sum + 金额
from
(select top 100 percent * from #A order by 金额 desc) aselect 人数=@i/*
--输出结果
人数
----
2
*/