一条sql语句,查询出全部分类各前10记录
如一班级成绩表
现建立课程表 tableA
sid sname
1 语文
2 数学
3 英语
.......
在建学生成绩库 tableB
id sid fen name
1 1 100 张三
2 1 89 李四
3 1 95 王五
4 2 89 张三
5 3 78 王五
6 2 99 王五
.....
现使用
可以显示出所有科目前10名单
select
a.sid,sname,id,fen,name
from
tableA a
inner
join
( select * from tableB b
where id in (
select top 10 id from tableB where sid = b.sid
order by fen desc )) as b
on a.sid = b.sid
order by a.sid,fen desc
( select * from tableB b
where id in (
select top 10 id from tableB where sid = b.sid
order by fen desc )) as b
on a.sid = b.sid
order by a.sid,fen desc
或
SELECT
sid, sname, fen,name
FROM ( SELECT px =
( SELECT COUNT ( 1 ) + 1
FROM tableB
WHERE sid = b.sid AND fen > b.fen), a.sid, a.sname, b.fen,b.name
FROM tableA a JOIN
tableB b ON a.sid = b.sid) t
WHERE (px <= 10 )
ORDER BY sid, fen DESC
FROM ( SELECT px =
( SELECT COUNT ( 1 ) + 1
FROM tableB
WHERE sid = b.sid AND fen > b.fen), a.sid, a.sname, b.fen,b.name
FROM tableA a JOIN
tableB b ON a.sid = b.sid) t
WHERE (px <= 10 )
ORDER BY sid, fen DESC