SELECT * FROM s41071030 t WHERE EXISTS (SELECT 1 FROM (SELECT sno FROM (SELECT sno,Count(1)num FROM sc41071030 GROUP BY sno) WHERE num=(SELECT Max(num) FROM (SELECT sno,Count(1)num FROM sc41071030 GROUP BY sno))) WHERE sno=t.sno);

------解决方案--------------------
SQL code

create table s41071030
(
    sno int ,
    sname varchar2(10),
    ssex char(1),
    sage int,
    sdept varchar2(10)
);
/
create table c41071030(
    cno int,
    cname varchar2(10),
    cpno int,
    ccredit varchar2(10)
);
/
create table sc41071030
(
    sno int,
    cno int,
    grade int 
);
/
insert into sc41071030
select 1001,1,40 from dual union all
select 1001,2,45 from dual union all
select 1001,3,50 from dual union all
select 1002,1,44 from dual union all
select 1002,2,40 from dual union all
--select 1002,3,50 from dual union all
select 1003,1,60 from dual;
/

insert into s41071030(sno,sdept)
select 1001,'CS' from dual union all
select 1002,'CS' from dual union all
select 1003,'CS' from dual union all
select 1004,'dd' from dual;
/


with cte as
(
      select a.sno ,count(1) as v_count from sc41071030 a
      inner join s41071030 b on a.sno=b.sno
      where b.sdept='CS'
      group by a.sno
      order by count(1) desc
 )
select a.* from cte a 
inner join 
(
     select max(v_count) as v_count from cte
)b  on a.v_count=b.v_count

      
/*drop table s41071030;
drop table c41071030;
drop table sc41071030;*/