现有三张表:
学生表:s41071030(sno,sname,ssex,sage,sdept)
课程表:c41071030(cno,cname,cpno,ccredit)
选课表:sc41071030(sno,cno,grade)
现做查询:查询sdept为CS的学生中选课数最多的学生的信息。求各位大侠指点!!求最优解!!
学生表:s41071030(sno,sname,ssex,sage,sdept)
课程表:c41071030(cno,cname,cpno,ccredit)
选课表:sc41071030(sno,cno,grade)
现做查询:查询sdept为CS的学生中选课数最多的学生的信息。求各位大侠指点!!求最优解!!
(SELECT MAX(CNT) MT
FROM
(SELECT SNO,COUNT(CNO) CNT
FROM SC41071030
GROUP BY SNO
))A,
(SELECT SNO,COUNT(CNO) CNT
FROM SC41071030
GROUP BY SNO
)B
WHERE A.MT=B.CNT
from (select t1.*, row_number() over(order by total asc) as cn
from (select sno, count(1) over(partition by sno) as total
from sc41071030 group by sno) t,
s41071030 t1
where t.sno = t1.sno
and t1.sdept = 'CS')
where cn = 1
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;*/