表结构是这样的:id name num
1 aa 12
2 bb 10
3 cc 1
4 dd 5
现在需求是这样的,搜索出 前N个数据,条件是他们的num总和大于总数(sum(num))的75% 是不是用having?怎么实现呢?
1 aa 12
2 bb 10
3 cc 1
4 dd 5
现在需求是这样的,搜索出 前N个数据,条件是他们的num总和大于总数(sum(num))的75% 是不是用having?怎么实现呢?
select * from user_num group by user having num*4>=sum(num)*3
group by name
having
sum(num)>(select sum(num) from tableName)*0.75 试试看行不?
drop table tbcreate table tb
(
id int,
name varchar(10),
num int
)insert tb(id,name,num)
select
1, 'aa' ,76
union all
select
2 ,'bb', 24;with cte
as
(select *, (select sum(num) from tb b where id<=a.id ) nums from tb a)select * from tb where
id <=(
select min(id) from
(select *, (select sum(num) from tb b where id<=a.id ) nums
from tb a) a where nums >
(select sum(num)*0.75 from tb))
(2 行受影响)
id name num
----------- ---------- -----------
1 aa 76(1 行受影响)
让1,2,3M的NUM加起来大于总和的75%。是不是呢?如果是,用2分法找。
不过需要很多次夺取数据库。
create table Test(
[id] [varchar](10),
[name] [varchar](10),
[num] [int]
)insert into Test values('1','aa',12) ;
insert into Test values('2','bb',10) ;
insert into Test values('3','cc',1) ;
insert into Test values('4','dd',5) ;
select * from
(
select * from Test
group by name,id,num
having
SUM(num) > ((select SUM(num) from Test)*0.75)
) as Test1
(
@Percent int = 75
)
AS
declare @All int;
declare @StepSum int;
declare @ItemsCounted int;
declare @Step int;set @Step = 5;
set @ItemsCounted = 0;
set @All = (select sum(num) from [table]);while 1=1
begin
set @ItemsCounted = @ItemsCounted+@Step;
set @StepSum =( select sum(num) from (select top (@ItemsCounted) num from [table]) t);--sql2000要想别的办法
if(CONVERT(float,@StepSum) * 100/CONVERT(float,@All) >=@Percent)
break;
endwhile 1=1
begin
set @ItemsCounted = @ItemsCounted-1;
set @StepSum =( select sum(num) from (select top (@ItemsCounted) num from [table]) t);--sql2000要想别的办法
if(CONVERT(float,@StepSum) * 100/CONVERT(float,@All) < @Percent)
begin
--测试部分
declare @LastSum float;
set @LastSum = @StepSum;
set @StepSum =( select sum(num) from (select top (@ItemsCounted+1) num from [table]) t);
select @ItemsCounted+1 as 'N',
@All as '总和',
@StepSum as '前N项总和',
CONVERT(float,@StepSum) * 100/CONVERT(float,@All) as '前N项百分比',
@LastSum * 100/CONVERT(float,@All) as '前N-1项百分比';
--测试部分 end
return @ItemsCounted+1;
end
end RETURN -1;
2 b 16
3 c 5
4 d 4
5 e 5
6 f 6
7 g 7
8 h 8
9 i 9
10 j 4结果:
N 总和 前N项总和 前N项百分比 前N-1项百分比
8 65 52 80 67.6923076923077
declare cs1 cursor for select * from tb_11
declare @sumValue float
declare @tmpSum float
set @tmpSum=0
set @sumValue=0
select @sumValue=sum(num) from tb_11
set @sumValue=@sumValue*0.75
declare @tmp float
set @tmp=0
open cs
open cs1
fetch next from cs into @tmp
while(@tmpSum<@sumValue and @@fetch_status=0)
begin
fetch next from cs1
fetch next from cs into @tmp
set @tmpSum=@tmpSum+@tmp
end
close cs
deallocate cs
close cs1
deallocate cs1
复制过去,将tb_11该成你的表名,试试看,嫌换行太多的话就缩到一行
open cs1
fetch next from cs into @tmp
fetch next from cs1
set @tmpSum=@tmpSum+@tmp
while(@tmpSum<@sumValue and @@fetch_status=0)
从open那行到while那行少写了两行
1.必须是id的升序排列
2.select *, (select sum(num) from tb b where id<=a.id ) nums from tb a
这样的语句,如果数据量大,似乎效率也不会太高