费了半天力气写出来个这个
只能查出第一名
select sid = (select top 1 sid from sa where cid = a.cid order by result desc),
cid,
result = (select top 1 result from sa where cid = a.cid order by result desc)
from sa a
group by cid
go
楼主将就看看吧 我也期待答案!!!
只能查出第一名
select sid = (select top 1 sid from sa where cid = a.cid order by result desc),
cid,
result = (select top 1 result from sa where cid = a.cid order by result desc)
from sa a
group by cid
go
楼主将就看看吧 我也期待答案!!!
此程序为并列一同取出,第一或第二都行create table #t
(
课程 nvarchar(10),
姓名 nvarchar(10),
成绩 int
)
insert into #t values
('1','张00',1)
insert into #t values
('1','张01',2)
insert into #t values
('1','张02',2)
insert into #t values
('1','张03',4)
insert into #t values
('2','张04',7)
insert into #t values
('2','李05',11)
insert into #t values
('2','李06',14)
insert into #t values
('3','王07',16)
insert into #t values
('3','王08',17)
insert into #t values
('3','张09',18)
insert into #t values
('3','张10',19)
insert into #t values
('3','张11',22)
insert into #t values
('3','张12',22)
insert into #t values
('3','张13',22)select
课程,姓名,成绩
from #t
where
课程+','+str(成绩,3)+','+姓名
in
(
select
课程+','+max(str(成绩,3)+','+姓名) 成绩
from #t
group by 课程
)
or
(
课程+','+str(成绩,3)
in
(
select
课程+','+max(str(成绩,3)) 成绩
from #t a
where 课程+','+str(成绩,3)+','+姓名
not in
(
select
课程+','+max(str(成绩,3)+','+姓名) 成绩
from #t
group by 课程
)
group by 课程
)
and
课程 in
(
select
课程
from #t a
where 课程+','+str(成绩,3)+','+姓名
in
(
select
课程+','+max(str(成绩,3)+','+姓名) 成绩
from #t
group by 课程
)
group by 课程
having count(*) < 2
)
)
order by 课程,成绩 desc,姓名
drop table #t课程 姓名 成绩
---------- ---------- -----------
1 张03 4
1 张01 2
1 张02 2
2 李06 14
2 李05 11
3 张11 22
3 张12 22
3 张13 22(所影响的行数为 8 行)
--测试环境
create table sa
(sid int,
cid int,
result int)
insert sa
select 1,1,100 union all
select 1,2,78 union all
select 2,1,99 union all
select 2,2,98 union all
select 3,1,68 union all
select 3,2,101 union all
select 4,1,78 union all
select 4,2,58 union all
select 5,1,88 union all
select 5,2,98
go
--语句
select * from sa s
where sid in (select top 2 sid from sa where cid=s.cid order by result desc)
order by cid,result desc
我家里有测试环境只能用E-mail
有问题么??
没明白楼上兄弟意识 我给你留 QQ了 QQ研究吧!
--那就用这个吧
select * from sa s
where sid in (select distinct top 2 sid from sa where cid=s.cid order by result desc)
order by cid,result desc
select StudentID as StudentID,max(Achievement) as Achievement from StudentAchievement group by Courseid where StudentID not in (select StudentID as StudentID,max(Achievement) as Achievement from StudentAchievement group by Courseid ) group by Courseid) order by Achievement
没调试的,应该可以,可能有些小错误,可自己调整
select * from StudentAchievement where studentid in (select top 2 studentid as studentid from StudentAchievement group by Courseid order by Achievement desc)
这个也是没调试的,机子刚卸载SQL Server。第一个思路是:得到课程第一名和第二名的学生号
第二个的思路是,按课程分组,并且按 降序排列,然后取前2笔数据。可能我写的不一定能通过,但是重在体现思路
from
StudentAchievement a
where
a.StudentId in (select top 2 StudentId
from StudentAchievement
where CourseId=a.CourseId
order by Achievement desc)