29、查询 1990 年出生的学生名单
①、
SELECT *
FROM student
where CAST(sage AS VARCHAR(10)) like '1990%';
sid | sname | sage | ssex
------------+------------------------+------------+-------
01 | 赵雷 | 1990-01-01 | 男
02 | 钱电 | 1990-12-21 | 男
03 | 孙风 | 1990-12-20 | 男
04 | 李云 | 1990-12-06 | 男
(4 行记录)
②、
SELECT *
FROM student
WHERE EXTRACT(year from sage)='1990';
sid | sname | sage | ssex
------------+------------------------+------------+-------
01 | 赵雷 | 1990-01-01 | 男
02 | 钱电 | 1990-12-21 | 男
03 | 孙风 | 1990-12-20 | 男
04 | 李云 | 1990-12-06 | 男
(4 行记录)
30、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
①、
SELECT cid,AVG(score) AS "平均成绩"
FROM SC
GROUP BY cid
ORDER BY 平均成绩 DESC, cid;
cid | 平均成绩
------------+---------------------
02 | 72.6666666666666667
03 | 68.5000000000000000
01 | 61.4000000000000000
(3 行记录)
②、网络答案:
SELECT C.Cname, C.Cid, AVG(SC.Score)
FROM SC INNER JOIN Course AS C
ON SC.Cid = C.Cid
GROUP BY C.Cid,C.Cname
ORDER BY AVG(SC.Score) DESC, C.Cid;
cname | cid | avg
--------------+------------+---------------------
数学 | 02 | 72.6666666666666667
英语 | 03 | 68.5000000000000000
语文 | 01 | 61.4000000000000000
(3 行记录)
31、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
①、
SELECT S.sid, S.sname,SC1.avgscore AS 平均成绩
FROM student AS S INNER JOIN (
SELECT sid, AVG(score) AS avgscore
FROM SC
GROUP BY sid
HAVING AVG(score)>=85
) AS SC1
ON S.sid=SC1.sid;
sid | sname | 平均成绩
------------+------------------------+---------------------
07 | 郑竹 | 93.5000000000000000
01 | 赵雷 | 94.5000000000000000
(2 行记录)
②、网络答案:
SELECT S.Sid, S.Sname, AVG(SC.Score)
FROM SC INNER JOIN Student AS S
ON SC.Sid = S.Sid
GROUP BY S.Sid
HAVING AVG(SC.Score) >= 85;
sid | sname | avg
------------+------------------------+---------------------
01 | 赵雷 | 94.5000000000000000
07 | 郑竹 | 93.5000000000000000
(2 行记录)
又是先联结和先筛选的问题……有没有大神帮我解答下,到底哪个性能好点,实际应用中优先选择哪种方式