用select 写出 '每个月成绩最好 '的前3名同学名称
student (id,name,cj,rq)
select top 3 * from student where *********** order by cj desc
union all
select top 3 * from student where *********** order by cj desc
有没有简单的
用select 一步^_^
------解决方案--------------------
create table student(id int, name varchar(10), cj int, rq datetime)
insert student select 1, 'AA ', 68, '2007-01-02 '
insert student select 2, 'BB ', 88, '2007-01-02 '
insert student select 3, 'CC ', 90, '2007-01-02 '
insert student select 4, 'DD ', 65, '2007-01-02 '
insert student select 5, 'EE ', 70, '2007-01-02 '
insert student select 1, 'AA ', 78, '2007-02-02 '
insert student select 2, 'BB ', 80, '2007-02-02 '
insert student select 4, 'DD ', 90, '2007-02-02 '
insert student select 5, 'EE ', 84, '2007-02-02 '
select * from student as tmp
where
(select count(*) from student
where convert(char(7), rq, 120)=convert(char(7), tmp.rq, 120) and cj> tmp.cj) <3
order by rq, cj desc
--result
id name cj rq
----------- ---------- ----------- ------------------------------------------------------
3 CC 90 2007-01-02 00:00:00.000
2 BB 88 2007-01-02 00:00:00.000
5 EE 70 2007-01-02 00:00:00.000
4 DD 90 2007-02-02 00:00:00.000
5 EE 84 2007-02-02 00:00:00.000
2 BB 80 2007-02-02 00:00:00.000
(6 row(s) affected)