Postgresql 第24、25 题

24、查询每门课程被选修的学生数

SELECT cid, count(sid)
FROM  SC
GROUP BY cid;

    cid     | count
------------+-------
 02         |     6
 03         |     6
 01         |     6
(3 行记录)

25、查询出只选修两门课程的学生学号和姓名

①、

SELECT sid,sname
from student
where sid in (
SELECT sid
FROM  SC
GROUP BY sid
HAVING COUNT(cid)=2
);

    sid     |         sname
------------+------------------------
 07         | 郑竹
 06         | 吴兰
 05         | 周梅
(3 行记录)

②、网络答案：

SELECT S.Sid, S.Sname, A.选课数 
FROM( 
SELECT Sid, COUNT(*) AS 选课数 
FROM SC 
GROUP BY Sid) AS A 
INNER JOIN Student AS S ON A.Sid = S.Sid 
WHERE A.选课数 = 2;

    sid     |         sname          | 选课数
------------+------------------------+--------
 07         | 郑竹                   |      2
 06         | 吴兰                   |      2
 05         | 周梅                   |      2
(3 行记录)