作者noguest (guest)
看板Database
标题Re: [SQL ] MySQL如何select出各系的前三高分？
时间Thu Sep 21 11:04:09 2006
※ 引述《noguest (guest)》之铭言：
:
: SELECT x.学号, x.科系, x.成绩
: FROM tbl x
: INNER JOIN tbl y ON x.成绩<=y.成绩 AND x.科系=y.科系
: GROUP BY x.学号, x.科系, x.成绩
: HAVING count(distinct y.成绩) <= 3
: ORDER BY x.科系, x.成绩 desc;
:
: --
: 推 lcloud:我试的结果 还是跑出全部的资料@@ 09/20 14:53
我把在 Oracle 上执行的结果放上来, 结果应该是没有错:
==================================
SQL> set echo on
SQL> select * from tbl;
ID DEP SCORE
---------- ---------- ----------
1001 1 100
1001 2 80
1002 1 60
1003 1 80
1004 2 90
1005 1 70
1005 2 95
1006 2 88
1007 1 75
9 rows selected.
SQL> @query
SQL> SELECT x.id,x.dep, x.score
2 FROM tbl x
3 INNER JOIN tbl y ON x.score<=y.score AND x.dep=y.dep
4 GROUP BY x.id,x.dep, x.score
5 HAVING count(distinct y.score) <= 3
6 ORDER BY x.dep, x.score desc;
ID DEP SCORE
---------- ---------- ----------
1001 1 100
1003 1 80
1007 1 75
1005 2 95
1004 2 90
1006 2 88
6 rows selected.
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 24.6.95.138