当前位置: 代码迷 >> Sql Server >> select 语句解决方案
  详细解决方案

select 语句解决方案

热度:60   发布时间:2016-04-27 15:53:10.0
select 语句
用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)
  相关解决方案