※ 引述《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