SELECT ID,NAME,GRADE,POINT FROM STUDENT GROUP BY GRADE ORDER BY POINT
select top 5 * from tablename order by point group by grade--建议转到sql版
select top 5 * from tablename order by point desc group by grade
select top 5 * from tb group by grade order by point
select top 5 * from tablename group by grade order by point desc--不好意思,后面顺序写翻了
一条SQL 很难实现, 一个年级:1年级 SELECT TOP 5 * FROM STUDENT WHERE GRADE ='1' ORDER BY POINT
select top 5 * from tablename order by point group by grade
select * from tb as a where exists ( select * from ( select top 5 * from tb as b where b.grade = a.grade order by point desc ) as t where b.id = a.id )
create table stu ( id int, name varchar(20), grade varchar(20), point int )insert into stu values(1,'aa','一年级',300) insert into stu values(2,'bb','一年级',301) insert into stu values(3,'cc','一年级',302) insert into stu values(4,'dd','一年级',303) insert into stu values(5,'ee','一年级',304) insert into stu values(6,'ff','一年级',305) insert into stu values(7,'gg','一年级',306) insert into stu values(8,'hh','二年级',307) insert into stu values(9,'ii','二年级',308) insert into stu values(10,'jj','二年级',309)insert into stu values(11,'kk','二年级',310) insert into stu values(12,'ll','二年级',320) insert into stu values(13,'mm','二年级',330) insert into stu values(14,'nn','二年级',340) insert into stu values(15,'oo','二年级',3050) insert into stu values(16,'pp','二年级',306) insert into stu values(17,'qq','三年级',305) insert into stu values(18,'rr','三年级',334) insert into stu values(19,'ss','三年级',3345) insert into stu values(20,'tt','三年级',344) insert into stu values(11,'uu','三年级',345) insert into stu values(12,'vv','三年级',366) insert into stu values(13,'ww','三年级',377) insert into stu values(14,'xx','三年级',388) insert into stu values(15,'yy','三年级',389) insert into stu values(16,'zz','三年级',333) select * from ( select top 5 * from stu where grade='一年级' order by point desc)a union all select * from ( select top 5 * from stu where grade='二年级' order by point desc)b union all select * from ( select top 5 * from stu where grade='三年级' order by point desc)c id name grade point ----------- -------------------- -------------------- ----------- 7 gg 一年级 306 6 ff 一年级 305 5 ee 一年级 304 4 dd 一年级 303 3 cc 一年级 302 15 oo 二年级 3050 14 nn 二年级 340 13 mm 二年级 330 12 ll 二年级 320 11 kk 二年级 310 19 ss 三年级 3345 15 yy 三年级 389 14 xx 三年级 388 13 ww 三年级 377 12 vv 三年级 366(15 行受影响)
SELECT id , [name] , grade , point FROM ( SELECT RANK() OVER ( PARTITION BY grade ORDER BY [point] ) AS ROW , * FROM TB ) WHERE ROW < 62005以上版本
SELECT id , [name] , grade , point FROM ( SELECT RANK() OVER ( PARTITION BY grade ORDER BY [point] ) AS ROW , * FROM TB ) A WHERE ROW < 6纠正
看肉肉的吧。不过在排序一下 SELECT id , [name] , grade , point FROM ( SELECT RANK() OVER ( PARTITION BY grade ORDER BY [point] desc ) AS ROW , * FROM stu ) A WHERE ROW < 6
一个年级:1年级
SELECT TOP 5 * FROM STUDENT WHERE GRADE ='1' ORDER BY POINT
where exists
(
select * from
(
select top 5 * from tb as b where b.grade = a.grade order by point desc
) as t where b.id = a.id
)
(
id int,
name varchar(20),
grade varchar(20),
point int
)insert into stu values(1,'aa','一年级',300)
insert into stu values(2,'bb','一年级',301)
insert into stu values(3,'cc','一年级',302)
insert into stu values(4,'dd','一年级',303)
insert into stu values(5,'ee','一年级',304)
insert into stu values(6,'ff','一年级',305)
insert into stu values(7,'gg','一年级',306)
insert into stu values(8,'hh','二年级',307)
insert into stu values(9,'ii','二年级',308)
insert into stu values(10,'jj','二年级',309)insert into stu values(11,'kk','二年级',310)
insert into stu values(12,'ll','二年级',320)
insert into stu values(13,'mm','二年级',330)
insert into stu values(14,'nn','二年级',340)
insert into stu values(15,'oo','二年级',3050)
insert into stu values(16,'pp','二年级',306)
insert into stu values(17,'qq','三年级',305)
insert into stu values(18,'rr','三年级',334)
insert into stu values(19,'ss','三年级',3345)
insert into stu values(20,'tt','三年级',344)
insert into stu values(11,'uu','三年级',345)
insert into stu values(12,'vv','三年级',366)
insert into stu values(13,'ww','三年级',377)
insert into stu values(14,'xx','三年级',388)
insert into stu values(15,'yy','三年级',389)
insert into stu values(16,'zz','三年级',333)
select * from (
select top 5 * from stu where grade='一年级' order by point desc)a
union all
select * from (
select top 5 * from stu where grade='二年级' order by point desc)b
union all
select * from (
select top 5 * from stu where grade='三年级' order by point desc)c
id name grade point
----------- -------------------- -------------------- -----------
7 gg 一年级 306
6 ff 一年级 305
5 ee 一年级 304
4 dd 一年级 303
3 cc 一年级 302
15 oo 二年级 3050
14 nn 二年级 340
13 mm 二年级 330
12 ll 二年级 320
11 kk 二年级 310
19 ss 三年级 3345
15 yy 三年级 389
14 xx 三年级 388
13 ww 三年级 377
12 vv 三年级 366(15 行受影响)
FROM ( SELECT RANK() OVER ( PARTITION BY grade ORDER BY [point] ) AS ROW , *
FROM TB )
WHERE ROW < 62005以上版本
FROM ( SELECT RANK() OVER ( PARTITION BY grade ORDER BY [point] ) AS ROW , *
FROM TB ) A
WHERE ROW < 6纠正
SELECT id , [name] , grade , point
FROM ( SELECT RANK() OVER ( PARTITION BY grade ORDER BY [point] desc ) AS ROW , *
FROM stu ) A
WHERE ROW < 6