create table Vivilion (ID int, Name varchar(10), Score int)insert into Vivilion
select 1,'aaa',100 union all
select 2,'bbb',56 union all
select 3,'ccc',70 union all
select 4,'ddd',65 union all
select 5,'eee',99 union all
select 6,'fff',80 union all
select 7,'ggg',70 union all
select 8,'hhh',60 union all
select 9,'iii',58 union all
select 10,'jjj',70select * from Vivilion
order by Score DESC
ID Name Score
----------- ---------- -----------
1 aaa 100
5 eee 99
6 fff 80
7 ggg 70
3 ccc 70
10 jjj 70
4 ddd 65
8 hhh 60
9 iii 58
2 bbb 56(10 row(s) affected)问题1:如何找出分数最高的前25%,例如这个就是前10*25%=2.5即
ID Name Score
----------- ---------- -----------
1 aaa 100
5 eee 99问题2:如何找出分数中上的25%,例如这个就是在10*25%=2.5和10*50%=5之间的即
ID Name Score
----------- ---------- -----------
6 fff 80
7 ggg 70
3 ccc 70
问题3:中下的25%
问题4:最后的25%说明:总人数不确定。但是人数很大,上万吧。
select 1,'aaa',100 union all
select 2,'bbb',56 union all
select 3,'ccc',70 union all
select 4,'ddd',65 union all
select 5,'eee',99 union all
select 6,'fff',80 union all
select 7,'ggg',70 union all
select 8,'hhh',60 union all
select 9,'iii',58 union all
select 10,'jjj',70select * from Vivilion
order by Score DESC
ID Name Score
----------- ---------- -----------
1 aaa 100
5 eee 99
6 fff 80
7 ggg 70
3 ccc 70
10 jjj 70
4 ddd 65
8 hhh 60
9 iii 58
2 bbb 56(10 row(s) affected)问题1:如何找出分数最高的前25%,例如这个就是前10*25%=2.5即
ID Name Score
----------- ---------- -----------
1 aaa 100
5 eee 99问题2:如何找出分数中上的25%,例如这个就是在10*25%=2.5和10*50%=5之间的即
ID Name Score
----------- ---------- -----------
6 fff 80
7 ggg 70
3 ccc 70
问题3:中下的25%
问题4:最后的25%说明:总人数不确定。但是人数很大,上万吧。
order by Score DESC
sqlserver2005有关键字ntile(x)和over(partition by.. order by..)子句配合.比如获取每个表的前10%个字段。
select id , name , colid , rn from (
select * , rn = ntile (10 )
over (partition by id order by colorder )
from syscolumns )t where rn = 1
if object_id('Vivilion')is not null drop table Vivilion
go
create table Vivilion (ID int, Name varchar(10), Score int)
insert into Vivilion
select 1,'aaa',100 union all
select 2,'bbb',56 union all
select 3,'ccc',70 union all
select 4,'ddd',65 union all
select 5,'eee',99 union all
select 6,'fff',80 union all
select 7,'ggg',70 union all
select 8,'hhh',60 union all
select 9,'iii',58 union all
select 10,'jjj',70select top 25 percent * from Vivilion ---前25%order by Score DESCID Name Score
----------- ---------- -----------
1 aaa 100
5 eee 99
6 fff 80(3 行受影响)
if object_id('Vivilion')is not null drop table Vivilion
go
create table Vivilion (ID int, Name varchar(10), Score int)
insert into Vivilion
select 1,'aaa',100 union all
select 2,'bbb',56 union all
select 3,'ccc',70 union all
select 4,'ddd',65 union all
select 5,'eee',99 union all
select 6,'fff',80 union all
select 7,'ggg',70 union all
select 8,'hhh',60 union all
select 9,'iii',58 union all
select 10,'jjj',70
select top 25 percent * from Vivilion ---中上25%
where id not in
(select top 25 percent id from Vivilion order by Score DESC)
order by Score DESC/*
ID Name Score
----------- ---------- -----------
3 ccc 70
7 ggg 70(2 行受影响)
*/select top 25 percent * from Vivilion ---中下25%
where id not in
(select top 50 percent id from Vivilion order by Score DESC)
order by Score DESC
/*
ID Name Score
----------- ---------- -----------
10 jjj 70
4 ddd 65(2 行受影响)
*/ select top 25 percent * from Vivilion ---最后25%
order by Score asc/*
ID Name Score
----------- ---------- -----------
2 bbb 56
9 iii 58
8 hhh 60(3 行受影响)
*/
select top m * from tablename where id not in (select top n id from tablename order by id asc/*|desc*/) 2.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入到临时表
set rowcount n --只取n条结果
select * from 表变量 order by columnname desc 3.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc
4.如果tablename里没有其他identity列,那么:
先生成一个序列,存储在一临时表中.
select identity(int) id0,* into #temp from tablename 取n到m条的语句为:
select * from #temp where id0 > =n and id0 <= m 如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行:
exec sp_dboption 你的DB名字,'select into/bulkcopy',true
5.如果表里有identity属性,那么简单:
select * from tablename where identity_col between n and m 6.SQL2005开始.可以使用row_number() over()生成行号
;with cte as
(
select id0=row_number() over(order by id),* from tablename
)
select * from cte where id0 between n to m
create table Vivilion (ID int, Name varchar(10), Score int)
insert into Vivilion
select 1,'aaa',100 union all
select 2,'bbb',56 union all
select 3,'ccc',70 union all
select 4,'ddd',65 union all
select 5,'eee',99 union all
select 6,'fff',80 union all
select 7,'ggg',70 union all
select 8,'hhh',60 union all
select 9,'iii',58 union all
select 10,'jjj',70declare @px1 as int
declare @px2 as int--前25%
set @px1 = 1
set @px2 = (select count(1) from Vivilion) * 0.25select ID,Name,Score from
(
select * ,px = (select count(1) from Vivilion where score > t.score ) + 1 from Vivilion t
) t
where px between @px1 and @px2
/*
ID Name Score
----------- ---------- -----------
1 aaa 100
5 eee 99(所影响的行数为 2 行)
*/--中上25%
set @px1 = (select count(1) from Vivilion) * 0.25 + 1
set @px2 = (select count(1) from Vivilion) * 0.5
select ID,Name,Score from
(
select * ,px = (select count(1) from Vivilion where score > t.score ) + 1 from Vivilion t
) t
where px between @px1 and @px2
/*
ID Name Score
----------- ---------- -----------
3 ccc 70
6 fff 80
7 ggg 70
10 jjj 70(所影响的行数为 4 行)
*/--中下25%
set @px1 = (select count(1) from Vivilion) * 0.5 + 1
set @px2 = (select count(1) from Vivilion) * 0.75
select ID,Name,Score from
(
select * ,px = (select count(1) from Vivilion where score > t.score ) + 1 from Vivilion t
) t
where px between @px1 and @px2
/*
ID Name Score
----------- ---------- -----------
4 ddd 65(所影响的行数为 1 行)
*/--最后的25%
set @px1 = (select count(1) from Vivilion) * 0.75 + 1
set @px2 = (select count(1) from Vivilion)
select ID,Name,Score from
(
select * ,px = (select count(1) from Vivilion where score > t.score ) + 1 from Vivilion t
) t
where px between @px1 and @px2
/*
ID Name Score
----------- ---------- -----------
2 bbb 56
8 hhh 60
9 iii 58(所影响的行数为 3 行)
*/
drop table Vivilion
insert into Vivilion
select 1,'aaa',100 union all
select 2,'bbb',56 union all
select 3,'ccc',70 union all
select 4,'ddd',65 union all
select 5,'eee',99 union all
select 6,'fff',80 union all
select 7,'ggg',70 union all
select 8,'hhh',60 union all
select 9,'iii',58 union all
select 10,'jjj',70declare @px1 as int
declare @px2 as int--前25%
set @px1 = 1
set @px2 = (select count(1) from Vivilion) * 0.25select ID,Name,Score from
(
select * ,px = (select count(1) from Vivilion where score > t.score or (score = t.score and ID < t.ID)) + 1 from Vivilion t
) t
where px between @px1 and @px2
/*
ID Name Score
----------- ---------- -----------
1 aaa 100
5 eee 99(所影响的行数为 2 行)
*/--中上25%
set @px1 = (select count(1) from Vivilion) * 0.25 + 1
set @px2 = (select count(1) from Vivilion) * 0.5
select ID,Name,Score from
(
select * ,px = (select count(1) from Vivilion where score > t.score or (score = t.score and ID < t.ID)) + 1 from Vivilion t
) t
where px between @px1 and @px2
/*
ID Name Score
----------- ---------- -----------
3 ccc 70
6 fff 80
7 ggg 70(所影响的行数为 3 行)*/--中下25%
set @px1 = (select count(1) from Vivilion) * 0.5 + 1
set @px2 = (select count(1) from Vivilion) * 0.75
select ID,Name,Score from
(
select * ,px = (select count(1) from Vivilion where score > t.score or (score = t.score and ID < t.ID)) + 1 from Vivilion t
) t
where px between @px1 and @px2
/*
ID Name Score
----------- ---------- -----------
4 ddd 65
10 jjj 70(所影响的行数为 2 行)
*/--最后的25%
set @px1 = (select count(1) from Vivilion) * 0.75 + 1
set @px2 = (select count(1) from Vivilion)
select ID,Name,Score from
(
select * ,px = (select count(1) from Vivilion where score > t.score or (score = t.score and ID < t.ID)) + 1 from Vivilion t
) t
where px between @px1 and @px2
/*
ID Name Score
----------- ---------- -----------
2 bbb 56
8 hhh 60
9 iii 58(所影响的行数为 3 行)
*/
drop table Vivilion
问题1:如何找出分数最高的前25%,例如这个就是前10*25%=2.5即 select top 25 percent * from test order by num desc;问题2:如何找出分数中上的25%,例如这个就是在10*25%=2.5和10*50%=5之间的即select top 25 percent * from test where id not in (select top 25 percent id from test)order by num desc问题3:中下的25%select top 25 percent * from test where id not in (select top 50 percent id from test)order by num desc问题4:最后的25% select top 25 percent * from test where id not in (select top 75 percent id from test)order by num desc
问题1:如何找出分数最高的前25%,例如这个就是前10*25%=2.5即 select top 25 percent * from test order by num desc;问题2:如何找出分数中上的25%,例如这个就是在10*25%=2.5和10*50%=5之间的即select top 25 percent * from test where id not in (select top 25 percent id from test)order by num desc问题3:中下的25%select top 25 percent * from test where id not in (select top 50 percent id from test)order by num desc问题4:最后的25% select top 25 percent * from test where id not in (select top 75 percent id from test)order by num desc
declare @Vivilion table (ID int, Name varchar(10), Score int)insert into @Vivilion
select 1,'aaa',100 union all
select 2,'bbb',56 union all
select 3,'ccc',70 union all
select 4,'ddd',65 union all
select 5,'eee',99 union all
select 6,'fff',80 union all
select 7,'ggg',70 union all
select 8,'hhh',60 union all
select 9,'iii',58 union all
select 10,'jjj',70select * from
(
select *,(Row_Number() over (Order by Score desc)) as rn
from @Vivilion) t
where t.rn>=0 and t.rn<=((select count(1) from @Vivilion)/4)