有三張表，分別是學生表stu，課程表cou，選課關系表sc
學生表的欄位有sno sname，birthday，課程表的欄位有cno cname，選課關系表sc欄位有sno cno grade
需求：查詢出選課不少于2門的學生的姓名、選課門數，要求按照選課門數的降序排序，如果選課門數相同，按照姓名升序排序？
請問各位大神，sql應該如何寫？
uj5u.com熱心網友回復：
select a.sname,count(b.cno) from stu a,cno b where a.sno = b.sno group by a.sname having count(b.cno) >= 2 order by count(b.cno) desc,sname ascuj5u.com熱心網友回復：
select s.sname , c.sccountfrom stu s
join (select sno ,count(sno) as sccount from sc grou by sno) c on s.sno = c.sno
where c.sccount >=2
order by s.sname asc;
uj5u.com熱心網友回復：
select a.sname,count(1) tfrom stu a
inner join sc b on a.sno=b.sno
inner join cou c on b.cno=c.cno
where t>2
group by a.sname
order by t desc,a.sname asc;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/43207.html
標籤：開發
下一篇：求助Oracle安裝問題