SELECT *,
( SELECT COUNT(*) FROM (SELECT COUNT(*),b.sid FROM sc b LEFT JOIN course c ON b.Cid=c.Cid GROUP BY b.sid,c.tid ) ow WHERE ow.sid = a.sid ) as '
选课数量',
(SELECT sum(Score) FROM Sc d WHERE a.sid = d.sid )as '总成绩'
FROM student a
;
SELECT d.sid,d.sname from teacher a
LEFT JOIN course b on a.tid = b.tid
LEFT JOIN sc c on c.cid = b.cid
left JOIN student d ON d.sid = c.sid
WHERE a.tname = '叶萍';
SELECT sid,sname
FROM student
WHERE sid in(select a.sid
FROM sc A left join sc b on a.sid=b.sid
WHERE a.cid=1 and b.cid=2 and a.score>b.score)
SELECT sc.Sid,sum(CASE WHEN c.Cname='
数学' then sc.Score else 0 end) 数学, SUM(case when c.Cname='物理
' THEN sc.Score ELSE 0 END) 物理,AVG(sc.Score) 平均分
FROM sc
INNER JOIN Course c on sc.Cid=c.Cid
WHERE c.Cname in ('数学','物理')
GROUP BYsc.Sid
ORDER BY AVG(sc.Score) DESC
insert into Sc(Sid,Cid,Score) values(003,3,85);
insert into Sc(Sid,Cid,Score) values(003,3,30);
首先这个不知道是你i写错了还是怎么 一个人化学成绩有2个 所以我在这里按照你这个上做的查询 所以有一个人是选了单个课程
