将您的选择 Package 到外部并添加where row_sort<=2：

select id, student, category, score
  from(
select *,
     row_number() over (partition by student order by random()) as row_sort
from my_table) inner_t
where row_sort<=2

演示here。
| 身份证|学生|范畴|刻痕|
| --------------|--------------|--------------|--------------|
| 二|亚历克斯|D|五|
| 二|亚历克斯|B|五十|
| 三|比尔|A|八十一|
| 七|卡尔|D|10个|
| 七|卡尔|A|五十五|

窗口函数的值只能在查询运行后访问，因此使用第二个CTE就可以了

with my_table (id, student, category, score)
as (values 
(1, 'Alex', 'A', 11),
(2, 'Alex', 'D', 4),
(2, 'Alex', 'B', 50),
(2, 'Alex', 'C', 83),
(2, 'Alex', 'D', 5),
(3, 'Bill', 'A', 81),
(6, 'Carl', 'C', 5),
(7, 'Carl', 'D', 2),
(7, 'Carl', 'B', 21),
(7, 'Carl', 'A', 55),
(7, 'Carl', 'A', 86),
(7, 'Carl', 'D', 10)
),
CTE2 as (select *,
     row_number() over (partition by student order by random()) as row_sort
from my_table)
SELECT id, student, category, score
  FROM CTE2
WHERE row_sort <= 2

SELECT 5

fiddle