可以使用ROW_NUMBER()。例如：

select id_student
from (
  select t.*, row_number() over(partition by id_student order by id) as rn 
  from t
) x
where id_desireCollage = 5 and rn > 5

可以使用ROW_NUMBER分析函数：

SELECT id_student
FROM   (
  SELECT id_student,
         id_desireCollage,
         ROW_NUMBER() OVER (PARTITION BY id_student ORDER BY id) AS rn
  FROM   student_desire
)
WHERE  id_desireCollage = 5
AND    rn > 5;

或者，从Oracle 12（不带任何函数）使用MATCH_RECOGNIZE：

SELECT id_student
FROM   student_desire
MATCH_RECOGNIZE(
  PARTITION BY id_student
  ORDER BY id
  PATTERN (^ not5{5,} id5)
  DEFINE
    not5 AS id_desireCollage != 5,
    id5  AS id_desireCollage = 5
);

其中，对于示例数据：

CREATE TABLE student_desire (ID, id_desireCollage, id_student) AS
  SELECT 1, 1, 1 FROM DUAL UNION ALL
  SELECT 2, 2, 1 FROM DUAL UNION ALL
  SELECT 3, 3, 1 FROM DUAL UNION ALL
  SELECT 4, 4, 1 FROM DUAL UNION ALL
  SELECT 6, 5, 1 FROM DUAL UNION ALL
  SELECT 7, 2, 2 FROM DUAL UNION ALL
  SELECT 8, 5, 2 FROM DUAL UNION ALL
  SELECT 9, 1, 2 FROM DUAL UNION ALL
  SELECT 10, 3, 2 FROM DUAL UNION ALL
  SELECT 11, 7, 2 FROM DUAL UNION ALL
  SELECT 12, 4, 3 FROM DUAL UNION ALL
  SELECT 13, 3, 3 FROM DUAL UNION ALL
  SELECT 14, 2, 3 FROM DUAL UNION ALL
  SELECT 15, 1, 3 FROM DUAL UNION ALL
  SELECT 16, 8, 3 FROM DUAL UNION ALL
  SELECT 17, 9, 3 FROM DUAL UNION ALL
  SELECT 18, 7, 3 FROM DUAL UNION ALL
  SELECT 19, 5, 3 FROM DUAL

所有输出：
| ID_学生|
| - ------|
| 三个|
您可以不用分析函数或MATCH_RECOGNIZE，而是使用相关子查询和聚合函数，但效率（低得多）（并且确实使用了COUNT聚合函数）：

SELECT id_student
FROM   student_desire s
WHERE  id <= ( SELECT id
               FROM   student_desire c
               WHERE  s.id_student = c.id_student
               AND    c.id_desireCollage = 5 )
GROUP BY id_student
HAVING COUNT(*) > 5

fiddle

假设ID在组内是连续的，您可以查询ID id_desireCollage=5与第一个id_desireCollage之间的差值大于5的学生：

SELECT   id_student
FROM     student_desire
GROUP BY id_student
HAVING   MIN(CASE id_desireCollage WHEN 5 THEN id END) - MIN(id) > 5