mysql 查询 至少选择了两门课_查找至少选取两门课程的学生 oracle数据库

SQL> WITH t AS (

2 SELECT '1' sno,'s1' sname,10 age FROM dual UNION ALL

3 SELECT '2' sno,'s2' sname,20 age FROM dual UNION ALL

4 SELECT '3' sno,'s3' sname,30 age FROM dual UNION ALL

5 SELECT '4' sno,'s4' sname,40 age FROM dual UNION ALL

6 SELECT '5' sno,'s5' sname,50 age FROM dual UNION ALL

7 SELECT '6' sno,'s6' sname,20 age FROM dual

8 )

9 SELECT m.*

10 FROM (SELECT t.*,

11 dense_rank() over(ORDER BY t.age) rn

12 FROM t) m

13 WHERE m.rn > 2

14 ;

SNO SNAME AGE RN

--- ----- ---------- ----------

3 s3 30 3

4 s4 40 4

5 s5 50 5

2011年8月02日 19:40