11 个解决方案
#1
--给个例子参考
--查询每门课程的前2名成绩
CREATE TABLE StudentGrade(
stuId CHAR(4), --学号
subId INT, --课程号
grade INT, --成绩
PRIMARY KEY (stuId,subId)
)
GO
--表中数据如下
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',1,97);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',2,50);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',3,70);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',1,92);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',2,80);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',3,30);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',1,93);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',2,95);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',3,85);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',1,73);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',2,78);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',3,87);
GO
/*
要查询每门课程的前2名成绩
001 1 97
003 1 93
003 2 95
002 2 80
004 3 87
003 3 85
如何实现?
*/
--查看数据
select * from StudentGrade
--假如出现并列时,也只取两个同学的话。
--方法一:
select distinct *
from studentgrade as t1
where stuid in
(select top 2 stuid
from studentgrade as t2
where t1.subid=t2.subid
order by t2.grade desc)
order by subid, grade desc
--方法二:
select * from StudentGrade a where (select count(1) from studentGrade where subId=a.subId and grade>=a.grade)<=2
--方法三:
select * from StudentGrade t
where (select count(1) from StudentGrade where subid=t.subid and grade>t.grade)<=1
order by subId,grade desc
--结果
/*
stuId subId grade
----- ----------- -----------
001 1 97
003 1 93
003 2 95
002 2 80
004 3 87
003 3 85
(6 row(s) affected)
*/
drop table StudentGrade
#2
学习
#3
学习
#4
学习
#5
看看用游标
create proc sp_group
@s varchar(500) output
as
begin
declare curgroup cursor
for
select subid from studentgrade as a group by subid
open curgroup
declare @subid varchar(500)
fetch next from curgroup into @subid
set @s=''
while @@fetch_status = 0
begin
select @s=@s+'select top 3 * from studentgrade where subid ='+@subid+char(13)+'go'+char(13)
fetch next from curgroup into @subid
end
print @s
close curgroup
deallocate curgroup
end
create proc sp_group
@s varchar(500) output
as
begin
declare curgroup cursor
for
select subid from studentgrade as a group by subid
open curgroup
declare @subid varchar(500)
fetch next from curgroup into @subid
set @s=''
while @@fetch_status = 0
begin
select @s=@s+'select top 3 * from studentgrade where subid ='+@subid+char(13)+'go'+char(13)
fetch next from curgroup into @subid
end
print @s
close curgroup
deallocate curgroup
end
#6
非常感谢大家的热心帮助!你们的建议很有用!
#7
学习
#8
不实用,均是奇慢无比.
#9
学习
#10
没有这么麻烦,吃饭后来回答,先抢个座。
#11
一直用这种写法.
http://community.csdn.net/Expert/topic/4826/4826331.xml?temp=.7956049
每组取前n条
SELECT * FROM table a WHERE n>(SELECT COUNT(*) FROM table b WHERE b.id<a.id)
假设字段id为你所区分哪个是前哪个是后的字段
http://community.csdn.net/Expert/topic/4826/4826331.xml?temp=.7956049
每组取前n条
SELECT * FROM table a WHERE n>(SELECT COUNT(*) FROM table b WHERE b.id<a.id)
假设字段id为你所区分哪个是前哪个是后的字段
#1
--给个例子参考
--查询每门课程的前2名成绩
CREATE TABLE StudentGrade(
stuId CHAR(4), --学号
subId INT, --课程号
grade INT, --成绩
PRIMARY KEY (stuId,subId)
)
GO
--表中数据如下
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',1,97);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',2,50);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',3,70);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',1,92);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',2,80);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',3,30);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',1,93);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',2,95);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',3,85);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',1,73);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',2,78);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',3,87);
GO
/*
要查询每门课程的前2名成绩
001 1 97
003 1 93
003 2 95
002 2 80
004 3 87
003 3 85
如何实现?
*/
--查看数据
select * from StudentGrade
--假如出现并列时,也只取两个同学的话。
--方法一:
select distinct *
from studentgrade as t1
where stuid in
(select top 2 stuid
from studentgrade as t2
where t1.subid=t2.subid
order by t2.grade desc)
order by subid, grade desc
--方法二:
select * from StudentGrade a where (select count(1) from studentGrade where subId=a.subId and grade>=a.grade)<=2
--方法三:
select * from StudentGrade t
where (select count(1) from StudentGrade where subid=t.subid and grade>t.grade)<=1
order by subId,grade desc
--结果
/*
stuId subId grade
----- ----------- -----------
001 1 97
003 1 93
003 2 95
002 2 80
004 3 87
003 3 85
(6 row(s) affected)
*/
drop table StudentGrade
#2
学习
#3
学习
#4
学习
#5
看看用游标
create proc sp_group
@s varchar(500) output
as
begin
declare curgroup cursor
for
select subid from studentgrade as a group by subid
open curgroup
declare @subid varchar(500)
fetch next from curgroup into @subid
set @s=''
while @@fetch_status = 0
begin
select @s=@s+'select top 3 * from studentgrade where subid ='+@subid+char(13)+'go'+char(13)
fetch next from curgroup into @subid
end
print @s
close curgroup
deallocate curgroup
end
create proc sp_group
@s varchar(500) output
as
begin
declare curgroup cursor
for
select subid from studentgrade as a group by subid
open curgroup
declare @subid varchar(500)
fetch next from curgroup into @subid
set @s=''
while @@fetch_status = 0
begin
select @s=@s+'select top 3 * from studentgrade where subid ='+@subid+char(13)+'go'+char(13)
fetch next from curgroup into @subid
end
print @s
close curgroup
deallocate curgroup
end
#6
非常感谢大家的热心帮助!你们的建议很有用!
#7
学习
#8
不实用,均是奇慢无比.
#9
学习
#10
没有这么麻烦,吃饭后来回答,先抢个座。
#11
一直用这种写法.
http://community.csdn.net/Expert/topic/4826/4826331.xml?temp=.7956049
每组取前n条
SELECT * FROM table a WHERE n>(SELECT COUNT(*) FROM table b WHERE b.id<a.id)
假设字段id为你所区分哪个是前哪个是后的字段
http://community.csdn.net/Expert/topic/4826/4826331.xml?temp=.7956049
每组取前n条
SELECT * FROM table a WHERE n>(SELECT COUNT(*) FROM table b WHERE b.id<a.id)
假设字段id为你所区分哪个是前哪个是后的字段