create table #t(StudentID int, [Name] varchar(20), Score int, Class tinyint)
insert #t
select 1,'张三', 85, 1 union all
select 3,'李四', 95, 1 union all
select 4,'王六', 34, 1 union all
select 5,'a李四', 45, 1 union all
select 6,'b王六', 22, 1 union all
select 8,'c李四', 77, 1 union all
select 9,'d王六', 66, 1 union all
select 10,'2李四', 95, 1
insert #t select studentid+20,[name]+'2',score,2 from #t where class=1
insert #t select studentid+30,[name]+'3',score,3 from #t where class=1
insert #t select studentid+40,[name]+'4',score,4 from #t where class=1
insert #t select studentid+50,[name]+'5',score,5 from #t where class=1
insert #t select studentid+60,[name]+'6',score,6 from #t where class=1
insert #t select studentid+70,[name]+'7',score,7 from #t where class=1
select * from #tselect id=identity(int,1,1),* into #tt from #t order by class,score descselect distinct a.*
from #tt a,(select distinct class from #tt) c
where a.score in (select distinct top 5 score from #tt where class=c.class order by score desc)
create table #t(StudentID int, [Name] varchar(20), Score int, Class tinyint)
insert #t
select 1,'张三', 85, 1 union all
select 3,'李四', 95, 1 union all
select 4,'王六', 34, 1 union all
select 5,'a李四', 45, 1 union all
select 6,'b王六', 22, 1 union all
select 8,'c李四', 77, 1 union all
select 9,'d王六', 66, 1 union all
select 10,'2李四', 95, 1
insert #t select studentid+20,[name]+'2',score,2 from #t where class=1
insert #t select studentid+30,[name]+'3',score,3 from #t where class=1
insert #t select studentid+40,[name]+'4',score,4 from #t where class=1
insert #t select studentid+50,[name]+'5',score,5 from #t where class=1
insert #t select studentid+60,[name]+'6',score,6 from #t where class=1
insert #t select studentid+70,[name]+'7',score,7 from #t where class=1
select * from #t--查询语句
select * from #t t
where studentid in (select top 5 studentid from #t where class=t.class order by score desc)
order by class,score desc
所以用的是:
select distinct top 5 score from #tt where class=c.class order by score desc
而不是用的:
select top 5 studentid from #t where class=t.class order by score desc
insert #t
select 1,'张三', 85, 1 union all
select 3,'李四', 95, 1 union all
select 4,'王六', 34, 1 union all
select 5,'a李四', 45, 1 union all
select 6,'b王六', 22, 1 union all
select 8,'c李四', 77, 1 union all
select 9,'d王六', 66, 1 union all
select 10,'2李四', 95, 1
insert #t select studentid+20,[name]+'2',score,2 from #t where class=1
insert #t select studentid+30,[name]+'3',score,3 from #t where class=1
insert #t select studentid+40,[name]+'4',score,4 from #t where class=1
insert #t select studentid+50,[name]+'5',score,5 from #t where class=1
insert #t select studentid+60,[name]+'6',score,6 from #t where class=1
insert #t select studentid+70,[name]+'7',score,7 from #t where class=1select * from #t a where a.studentid
in(select top 5 studentid from #t b
where a.class=b.class order by score desc)
order by class ,score desc
drop table #t
====
create table t3 (
StudentID smallint identity(1,1) not null,
Name nvarchar(10),
Score tinyint,
Class tinyint,
Constraint cons1 Check (Score > 0 and Score < 100),
Constraint cons2 Check (Class > 0 and Class < 10)
)插入数据
========
insert into t3 (Name,Score, Class) values (N'张三',85,1)
insert into t3 (Name,Score, Class) values (N'张题',91,1)
insert into t3 (Name,Score, Class) values (N'顾俊',84,1)
insert into t3 (Name,Score, Class) values (N'严华',86,1)
insert into t3 (Name,Score, Class) values (N'马忠',84,2)
insert into t3 (Name,Score, Class) values (N'戴平',84,2)
insert into t3 (Name,Score, Class) values (N'蒋生',87,2)
insert into t3 (Name,Score, Class) values (N'袁和',84,2)
insert into t3 (Name,Score, Class) values (N'王彦',99,3)
insert into t3 (Name,Score, Class) values (N'杨波',84,3)
insert into t3 (Name,Score, Class) values (N'孙贾',84,3)
insert into t3 (Name,Score, Class) values (N'傅山',84,3)
insert into t3 (Name,Score, Class) values (N'戴涛',78,4)
insert into t3 (Name,Score, Class) values (N'赵海',84,4)
insert into t3 (Name,Score, Class) values (N'李力',91,4)
insert into t3 (Name,Score, Class) values (N'周峰',84,4)
insert into t3 (Name,Score, Class) values (N'武梅',84,5)
insert into t3 (Name,Score, Class) values (N'郑莓',57,5)
insert into t3 (Name,Score, Class) values (N'孙俊',84,5)
insert into t3 (Name,Score, Class) values (N'顾和',84,5)
insert into t3 (Name,Score, Class) values (N'山俊',89,6)
insert into t3 (Name,Score, Class) values (N'顾袁',84,6)
insert into t3 (Name,Score, Class) values (N'海俊',92,6)
insert into t3 (Name,Score, Class) values (N'李俊',95,6)
insert into t3 (Name,Score, Class) values (N'顾梅',84,7)进行查询
========
select * from t3 t where studentID in (select top 3 StudentID from t3 where t3.Class = t.Class order by t3.Score)返回结果
========
StudentID Name Score Class
--------- ---------- ----- -----
1 张三 85 1
2 张题 84 1
3 顾俊 84 1
5 马忠 84 2
6 戴平 84 2
8 袁和 84 2
10 杨波 84 3
11 孙贾 84 3
12 傅山 84 3
13 戴涛 78 4
14 赵海 84 4
16 周峰 84 4
18 郑莓 57 5
19 孙俊 84 5
20 顾和 84 5
21 山俊 89 6
22 顾袁 84 6
23 海俊 92 6
25 顾梅 84 7
我一直坚持成绩单的排名必须考虑并列问题
上次我在
主 题: Sql分组统计问题
作 者: yzgnick (yzg)
中发表的现在有了更好的方案,比前次有了大的改进,更符合现实世界虚拟化的概念
我愿和请大家探讨
我取前两名
create table #t
(
SID smallint identity(1,1) not null,
姓名 nvarchar(10),
成绩 tinyint,
班级 tinyint,
Constraint cons1 Check (成绩 > 0 and 成绩 < 100),
Constraint cons2 Check (班级 > 0 and 班级 < 10)
)
insert into #t (姓名,成绩,班级) values (N'张三',85,1)
insert into #t (姓名,成绩,班级) values (N'张题',91,1)
insert into #t (姓名,成绩,班级) values (N'顾俊',84,1)
insert into #t (姓名,成绩,班级) values (N'严华',86,1)
insert into #t (姓名,成绩,班级) values (N'马忠',84,2)
insert into #t (姓名,成绩,班级) values (N'戴平',84,2)
insert into #t (姓名,成绩,班级) values (N'蒋生',87,2)
insert into #t (姓名,成绩,班级) values (N'袁和',84,2)
insert into #t (姓名,成绩,班级) values (N'王彦',99,3)
insert into #t (姓名,成绩,班级) values (N'杨波',84,3)
insert into #t (姓名,成绩,班级) values (N'孙贾',84,3)
insert into #t (姓名,成绩,班级) values (N'傅山',84,3)
insert into #t (姓名,成绩,班级) values (N'戴涛',78,4)
insert into #t (姓名,成绩,班级) values (N'赵海',84,4)
insert into #t (姓名,成绩,班级) values (N'李力',91,4)
insert into #t (姓名,成绩,班级) values (N'周峰',84,4)
insert into #t (姓名,成绩,班级) values (N'武梅',84,5)
insert into #t (姓名,成绩,班级) values (N'郑莓',57,5)
insert into #t (姓名,成绩,班级) values (N'孙俊',84,5)
insert into #t (姓名,成绩,班级) values (N'顾和',84,5)
insert into #t (姓名,成绩,班级) values (N'山俊',89,6)
insert into #t (姓名,成绩,班级) values (N'顾袁',84,6)
insert into #t (姓名,成绩,班级) values (N'海俊',92,6)
insert into #t (姓名,成绩,班级) values (N'李俊',95,6)
insert into #t (姓名,成绩,班级) values (N'顾梅',84,7)select
SID,姓名,成绩,班级
from #t a
where str(成绩,3)+str(班级,2)
in
(
select
top 2 --排名数
str(成绩,3)+str(班级,2)
from #t b
where a.班级 = b.班级
group by 班级,成绩
order by 班级,成绩 desc
)
order by 班级,成绩 desc ,SID
drop table #tSID 姓名 成绩 班级
------ ---------- ---- ----
2 张题 91 1
4 严华 86 1
7 蒋生 87 2
5 马忠 84 2
6 戴平 84 2
8 袁和 84 2
9 王彦 99 3
10 杨波 84 3
11 孙贾 84 3
12 傅山 84 3
15 李力 91 4
14 赵海 84 4
16 周峰 84 4
17 武梅 84 5
19 孙俊 84 5
20 顾和 84 5
18 郑莓 57 5
24 李俊 95 6
23 海俊 92 6
25 顾梅 84 7(所影响的行数为 20 行)
我觉得挺好 严重支持!
(select top 2 StudentID from t3 where Class=a.Class order by score desc)
select * from t3 a
where exists (Select 1 from (select distinct top 5 Score from t3 where class=a.class
order by Score desc )b where class=a.class and Score=a.Score)
order by class,score desc
--====
create table t3 (
StudentID smallint identity(1,1) not null,
Name nvarchar(10),
Score tinyint,
Class tinyint,
Constraint cons1 Check (Score > 0 and Score < 100),
Constraint cons2 Check (Class > 0 and Class < 10)
)--插入数据
--========
insert into t3 (Name,Score, Class) values (N'张三',85,1)
insert into t3 (Name,Score, Class) values (N'张题',91,1)
insert into t3 (Name,Score, Class) values (N'顾俊',84,1)
insert into t3 (Name,Score, Class) values (N'严华',86,1)
insert into t3 (Name,Score, Class) values (N'马忠',84,2)
insert into t3 (Name,Score, Class) values (N'戴平',84,2)
insert into t3 (Name,Score, Class) values (N'蒋生',87,2)
insert into t3 (Name,Score, Class) values (N'袁和',83,2)
insert into t3 (Name,Score, Class) values (N'王彦',99,2)
insert into t3 (Name,Score, Class) values (N'杨波',84,2)
insert into t3 (Name,Score, Class) values (N'孙贾',56,2)
insert into t3 (Name,Score, Class) values (N'傅山',84,2)
insert into t3 (Name,Score, Class) values (N'戴涛',78,4)
insert into t3 (Name,Score, Class) values (N'赵海',85,2)
insert into t3 (Name,Score, Class) values (N'李力',91,2)
insert into t3 (Name,Score, Class) values (N'周峰',86,2)
insert into t3 (Name,Score, Class) values (N'武梅',81,2)
insert into t3 (Name,Score, Class) values (N'郑莓',57,5)
insert into t3 (Name,Score, Class) values (N'孙俊',84,5)
insert into t3 (Name,Score, Class) values (N'顾和',84,5)
insert into t3 (Name,Score, Class) values (N'山俊',89,6)
insert into t3 (Name,Score, Class) values (N'顾袁',84,6)
insert into t3 (Name,Score, Class) values (N'海俊',92,6)
insert into t3 (Name,Score, Class) values (N'李俊',95,6)
insert into t3 (Name,Score, Class) values (N'顾梅',84,7)select * from t3 a
where exists (Select 1 from (select distinct top 5 Score from t3 where class=a.class
order by Score desc )b where class=a.class and Score=a.Score)
order by class,score desc