搞个体力活 declare @tb table([Name] varchar(1),[Rank] int) insert @tb select 'A',1 union all select 'B',2 union all select 'C',2 union all select 'D',2 union all select 'E',3 union all select 'F',2 union all select 'G',3 union all select 'H',4 union all select 'I',5 union all select 'J',6 print '10%' select TOP 10 PERCENT * From @tb ORDER BY [NAME]print '20%' SELECT * FROM @tb where [name] >= (select top 1 [name] from (select top 90 percent [name] from @tb order by [name] desc) p order by [name]) and [name]< (select top 1 [name] from (select top 70 percent [name] from @tb order by [name] desc) p order by [name])print '40%' SELECT * FROM @tb where [name] >= (select top 1 [name] from (select top 70 percent [name] from @tb order by [name] desc) p order by [name]) and [name]< (select top 1 [name] from (select top 30 percent [name] from @tb order by [name] desc) p order by [name])print '20%' SELECT * FROM @tb where [name] >= (select top 1 [name] from (select top 30 percent [name] from @tb order by [name] desc) p order by [name]) and [name]< (select top 1 [name] from (select top 10 percent [name] from @tb order by [name] desc) p order by [name]) print '10%' select TOP 10 PERCENT * From @tb ORDER BY [NAME] desc --测试结果: /*(所影响的行数为 10 行)10% Name Rank ---- ----------- A 1(所影响的行数为 1 行)20% Name Rank ---- ----------- B 2 C 2(所影响的行数为 2 行)40% Name Rank ---- ----------- D 2 E 3 F 2 G 3(所影响的行数为 4 行)20% Name Rank ---- ----------- H 4 I 5(所影响的行数为 2 行)10% Name Rank ---- ----------- J 6(所影响的行数为 1 行) */
A 1 10%
B 2 10%
C 2 10%
D 2 10%
E 3 20%
F 4 20%
...
Z 50 后10%
rank/sum(Rank)还是什么?
搞个体力活
declare @tb table([Name] varchar(1),[Rank] int)
insert @tb
select 'A',1 union all
select 'B',2 union all
select 'C',2 union all
select 'D',2 union all
select 'E',3 union all
select 'F',2 union all
select 'G',3 union all
select 'H',4 union all
select 'I',5 union all
select 'J',6
print '10%'
select TOP 10 PERCENT * From @tb ORDER BY [NAME]print '20%'
SELECT *
FROM @tb
where [name] >= (select top 1 [name] from (select top 90 percent [name] from @tb order by [name] desc) p order by [name])
and [name]< (select top 1 [name] from (select top 70 percent [name] from @tb order by [name] desc) p order by [name])print '40%'
SELECT *
FROM @tb
where [name] >= (select top 1 [name] from (select top 70 percent [name] from @tb order by [name] desc) p order by [name])
and [name]< (select top 1 [name] from (select top 30 percent [name] from @tb order by [name] desc) p order by [name])print '20%'
SELECT *
FROM @tb
where [name] >= (select top 1 [name] from (select top 30 percent [name] from @tb order by [name] desc) p order by [name])
and [name]< (select top 1 [name] from (select top 10 percent [name] from @tb order by [name] desc) p order by [name])
print '10%'
select TOP 10 PERCENT * From @tb ORDER BY [NAME] desc
--测试结果:
/*(所影响的行数为 10 行)10%
Name Rank
---- -----------
A 1(所影响的行数为 1 行)20%
Name Rank
---- -----------
B 2
C 2(所影响的行数为 2 行)40%
Name Rank
---- -----------
D 2
E 3
F 2
G 3(所影响的行数为 4 行)20%
Name Rank
---- -----------
H 4
I 5(所影响的行数为 2 行)10%
Name Rank
---- -----------
J 6(所影响的行数为 1 行)
*/