SQL 选择前几行总和大于某个值的最优算法

Question

请问， 比如员工的金额，amount,如果我要一次排序下来，取出前几名，加起来大于 总和的80%，要如何处理？目前已有方法，但感觉效率低下，谁能提供个最优算法呢？

Answer 1

--> 测试数据：[tbl]
go
if object_id('[tbl]') is not null 
drop table [tbl]
go
create table [tbl](
[name] varchar(1),
[date] varchar(5),
[num] int
)
go
insert [tbl]
select 'a','1-1号',1 union all
select 'b','1-2号',4 union all
select 'a','1-3号',8 union all
select 'a','1-4号',5 union all
select 'b','1-5号',6 union all
select 'b','1-6号',9;with t
as(
select ROW_NUMBER()over(partition by name
order by [date]) as id,
*,num  as total from tbl
),
m as(
select id,name,[date],num,total from t where id=1
union all
select a.id,a.name,a.[date],a.num,b.total+a.num from t a
inner join m b on a.id=b.id+1 and a.name=b.name
)
select name,[date],num,total from m order by name/*
name date num total
--------------------------------------
a 1-3号 8 8
a 1-4号 5 13
a 1-1号 1 14
b 1-2号 4 4
b 1-5号 6 10
b 1-6号 9 19
*/
-------------------------------------------------------
--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([ID] int,[借方] int,[贷方] int)
insert [tbl]
select 1,10,0 union all
select 2,0,4  union all
select 3,0,2  union all
select 4,1,0--这个查询是累计求和，你在每行求个百分比
SELECT 
ID,借方,贷方,
[余额]=(SELECT SUM(借方-贷方) FROM tbl WHERE ID<=a.ID)
FROM tbl AS a

Answer 2

with 
cte as(select row=row_number() over(order by amount desc),* from tb),
cte2 as(select *,cnt=(select sum(amount) from cte where row<=t.row) from cte t),
cte3 as (select amountCnt sum(amount) from tb),
cte4 as (select * ,rate=1.0*cnt/amountCnt from cte2 cross join cte3)
select * from cte4 where rate<=0.8

Answer 3

if object_id('test') is not null
drop table tset
go
create table test(
empid char(4),
deptid char(3),
salary int
)
go
insert test
select '1001','101',2000 union all
select '1002','101',1500 union all
select '1003','103',1900 union all
select '1004','102',3500 union all
select '1005','103',2800 union all
select '1006','101',1500 union all
select '1007','104',1600 union all
select '1008','104',2100 union all
select '1009','102',5000 union all
select '1010','103',4500 union all
select '1011','104',3200 ;with t
as(
select 
id=row_number()over(order by salary desc),
empid,deptid,salary
from test
),
m as(
select 
id,empid,deptid,salary,salary as total
from t where id=1
union all
select 
t.id,t.empid,t.deptid,t.salary,m.total+t.salary
from t 
inner join m
on t.id=m.id+1
)select 
id,empid,deptid,salary,total,
left(ltrim((cast(total as numeric(10,2))/
cast((select sum(salary) from test) as numeric(10,2)))*100),5)+'%' as precent
from m/*
id  empid  deptid  salary  total  precent
------------------------------------------------
1 1009 102 5000 5000 16.89%
2 1010 103 4500 9500 32.09%
3 1004 102 3500 13000 43.91%
4 1011 104 3200 16200 54.72%
5 1005 103 2800 19000 64.18%
6 1008 104 2100 21100 71.28%
7 1001 101 2000 23100 78.04%
8 1003 103 1900 25000 84.45%
9 1007 104 1600 26600 89.86%
10 1002 101 1500 28100 94.93%
11 1006 101 1500 29600 100.0%
*/给你写了一个递归的算法。那个百分比是当前行与它之前所有行的和同总和的百分比，你不是要.>=80%的吗？有了这个百分比，不就有结果了吗？

Answer 4

感谢所有的回答，基本上我自己写的也差不多是这样的。可是感觉效率很低的然后，如果数据库是SQL2000，这些函数都不支持，那该如何处理？

Answer 5

基本上都是这么一个步骤，如果是在2000中的话，
以2楼的方法，把每个cte放到一个临时表中，想要提升效率的话，考虑下给临时表相应的列建立索引

Answer 6

if object_id('test') is not null
drop table test
go
create table test(
empid char(4),
deptid char(3),
salary int
)
go
insert test
select '1001','101',2000 union all
select '1002','101',1500 union all
select '1003','103',1900 union all
select '1004','102',3500 union all
select '1005','103',2800 union all
select '1006','101',1500 union all
select '1007','104',1600 union all
select '1008','104',2100 union all
select '1009','102',5000 union all
select '1010','103',4500 union all
select '1011','104',3200 
create table #test(
id int identity(1,1),
empid char(4),
deptid char(3),
salary int
)
go
insert #test(empid,deptid,salary)
select * from test order by salary desc
go--穿建临时表为递归准备:
create table #tbl(
id int ,
empid char(4),
deptid char(3),
salary int,
total float
)
go
insert #tbl
select *,salary from #test where id=1
go--开始递归:
while @@ROWCOUNT<>0 
begin
insert #tbl
select 
a.id,a.empid,a.deptid,a.salary,b.total+a.salary
from #test a
inner join #tbl b
on a.id=b.id+1
where not exists(select 1 from #tbl c where a.id=c.id)
endselect *,
LEFT(
ltrim((cast(total as numeric(8,2))/
cast((select SUM(salary)from test)as numeric(8,2)))*100),5)+'%' as [percent]
from #tbl/*
id empid deptid salary total percent
-----------------------------------------------------
1 1009 102 5000 5000 16.89%
2 1010 103 4500 9500 32.09%
3 1004 102 3500 13000 43.91%
4 1011 104 3200 16200 54.72%
5 1005 103 2800 19000 64.18%
6 1008 104 2100 21100 71.28%
7 1001 101 2000 23100 78.04%
8 1003 103 1900 25000 84.45%
9 1007 104 1600 26600 89.86%
10 1002 101 1500 28100 94.93%
11 1006 101 1500 29600 100.0%
*/
--2000

Answer 7

再问个问题，在2000里面这个会报错？是怎么回事？
@importantNo是一个数字。select top (@importantNo) leadID
from ##importantLead

Answer 8

TOP后面不能跟一个变量吗？？？

Answer 9

declare @a int
set @a=10
exec('select top '+ltrim(@a)+' * from test' )
可以跟，但是好像需要动态

Answer 10

那，比如我的这个select语句是一个子查询，那怎么处理，直接exec就可以了么

Answer 11

if object_id('test') is not null
drop table test
go
create table test(
empid char(4),
deptid char(3),
salary int
)
go
insert test
select '1001','101',2000 union all
select '1002','101',1500 union all
select '1003','103',1900 union all
select '1004','102',3500 union all
select '1005','103',2800 union all
select '1006','101',1500 union all
select '1007','104',1600 union all
select '1008','104',2100 union all
select '1009','102',5000 union all
select '1010','103',4500 union all
select '1011','104',3200 
goselect identity(int, 1,1) as sno, *
    , cast(null as int) as summary_after
    , cast(null as numeric(10,4)) as [percent]
into #tb_list
from test
order by salary descupdate a set summary_after = (
    select sum(salary) from #tb_list b
    where b.sno <= a.sno
    )
from #tb_list adeclare @summary int
set @summary = (select top 1 summary_after from #tb_list order by sno desc)
update #tb_list set
    [percent] = 100. * summary_after / @summaryselect * from #tb_list
where sno <= (
    select sno
    from #tb_list a
    where [percent]>=80.
    and not exists (
        select 1 from #tb_list b
        where b.[percent]>=80. 
        and b.sno < a.sno)
    )drop table #tb_list
go
sno empid deptid salary summary_after percent
1 1009 102 5000 5000 16.8919
2 1010 103 4500 9500 32.0946
3 1004 102 3500 13000 43.9189
4 1011 104 3200 16200 54.7297
5 1005 103 2800 19000 64.1892
6 1008 104 2100 21100 71.2838
7 1001 101 2000 23100 78.0405
8 1003 103 1900 25000 84.4595（所影响的行数为 8 行）

Answer 12

还有个问题，如果是在存储过程里面，##table这个表，如何判断是否存在，不然每次运行都会报错
我用的是object_id 这个方法，可是还是不行啊

Answer 13

USE tempdb
goDECLARE @Percent NUMERIC(5,2)SET @Percent=70.00DECLARE @tmp TABLE(Customer NVARCHAR(50),Amount MONEY)
INSERT INTO @tmp
SELECT N'a',15000.00 UNION ALL
SELECT N'b',20000.00 UNION ALL
SELECT N'c',14500.00 UNION ALL
SELECT N'd',40000.00


;WITH c AS (
SELECT 
ROW_NUMBER()OVER(ORDER BY Amount DESC) AS Row
,Customer
,Amount
,CONVERT(NUMERIC(5,2),Amount/SUM(Amount)OVER()*100) AS [Percent(%)]
FROM @tmp
)
,c2 AS (
SELECT 
Row
,Customer
,Amount
,c.[Percent(%)]
,CONVERT(NUMERIC(5,2),[Percent(%)]) AS [TotalPercent(%)]
FROM c 
WHERE Row=1
UNION ALL
SELECT 
c.Row
,c.Customer
,c.Amount
,c.[Percent(%)]
,CONVERT(NUMERIC(5,2),c.[Percent(%)]+c2.[TotalPercent(%)]) AS [TotalPercent(%)]
FROM c
INNER JOIN c2 ON c.Row=c2.Row+1
WHERE c2.[TotalPercent(%)]<@Percent
)SELECT Customer,Amount,[Percent(%)],[TotalPercent(%)] FROM c2

Answer 14

if object_id('tempdb..##table') is not null drop table ##table

Answer 15

SQL找到累计求和不超出50%的前N条记录 .
select 1 as Value,0.00 as P  INTo #T   
INSERT INTo #T select 18,0.00  
INSERT INTo #T select 14,0.00  
INSERT INTo #T select 3,0.00  
INSERT INTo #T select 13,0.00  
INSERT INTo #T select 5,0.00  
INSERT INTo #T select 20,0.00  
INSERT INTo #T select 7,0.00  
INSERT INTo #T select 9,0.00  
INSERT INTo #T select 12,0.00  
INSERT INTo #T select 10,0.00  
INSERT INTo #T select 8,0.00  
INSERT INTo #T select 15,0.00  
INSERT INTo #T select 22,0.00  
INSERT INTo #T select 30,0.00  Update #T set P=CAST(Value as numeric(18, 2))/(select sum(Value) from #T)   --原始数据
select * from #T  Order By Value desc  --SQL找到累计求和不超出50%的前N条记录 .
select  a.Value,a.P from #T a left join #T b on a.Value<=b.Value   
group by a.Value,a.P  having SUM(b.P)<=0.5 Order By a.Value desc  
drop table #T  http://blog.csdn.net/anzhiqiang_touzi/article/details/6928332原理都差不多
你自己改下就是

Answer 16

select 1 as Value,0.00 as P  INTo #T   
INSERT INTo #T select 18,0.00  
INSERT INTo #T select 14,0.00  
INSERT INTo #T select 3,0.00  
INSERT INTo #T select 13,0.00  
INSERT INTo #T select 5,0.00  
INSERT INTo #T select 20,0.00  
INSERT INTo #T select 7,0.00  
INSERT INTo #T select 9,0.00  
INSERT INTo #T select 12,0.00  
INSERT INTo #T select 10,0.00  
INSERT INTo #T select 8,0.00  
INSERT INTo #T select 15,0.00  
INSERT INTo #T select 22,0.00  
INSERT INTo #T select 30,0.00  --计算百分比
Update #T set P=CAST(Value as numeric(18, 2))/(select sum(Value) from #T)   --原始数据
select * from #T  Order By Value desc  --SQL找到累计求和不超出80%的前N条记录
select  a.Value,a.P,SUM(b.P) as '累计求和' INTO #Result from #T a left join #T b on a.Value<=b.Value   
group by a.Value,a.P  having SUM(b.P)<=0.9 Order By a.Value desc  ---得到最后的结果数据
select Value,p,累计求和 from #Result where 累计求和<=(select Min(累计求和) from #Result where 累计求和>=0.8)drop table #Result  
drop table #T    
/*-------------------原始信息
Value       P
----------- -------
30          0.16
22          0.12
20          0.11
18          0.10
15          0.08
14          0.07
13          0.07
12          0.06
10          0.05
9           0.05
8           0.04
7           0.04
5           0.03
3           0.02
1           0.01-------------------最后统计结果超出80%
Value       p                      累计求和
----------- ------------------- ---------------------------------------
30          0.16                    0.16
22          0.12                    0.28
20          0.11                    0.39
18          0.10                    0.49
15          0.08                    0.57
14          0.07                    0.64
13          0.07                    0.71
12          0.06                    0.77
10          0.05                    0.82

Answer 17

select 1 as Value,0.00 as P  INTo #T   
INSERT INTo #T select 18,0.00  
INSERT INTo #T select 14,0.00  
INSERT INTo #T select 3,0.00  
INSERT INTo #T select 13,0.00  
INSERT INTo #T select 5,0.00  
INSERT INTo #T select 20,0.00  
INSERT INTo #T select 7,0.00  
INSERT INTo #T select 9,0.00  
INSERT INTo #T select 12,0.00  
INSERT INTo #T select 10,0.00  
INSERT INTo #T select 8,0.00  
INSERT INTo #T select 15,0.00  
INSERT INTo #T select 22,0.00  
INSERT INTo #T select 30,0.00  --计算百分比
Update #T set P=CAST(Value as numeric(18, 2))/(select sum(Value) from #T)   --原始数据
select * from #T  Order By Value desc  --SQL找到累计求和不超出80%的前N条记录
select  a.Value,a.P,SUM(b.P) as '累计求和' INTO #Result from #T a left join #T b on a.Value<=b.Value   
group by a.Value,a.P  having SUM(b.P)<=0.9 Order By a.Value desc  ---得到最后的结果数据
select Value,p,累计求和 from #Result where 累计求和<=(select Min(累计求和) from #Result where 累计求和>=0.8)drop table #Result  
drop table #T