## 窗口函数SELECTDISTINCT st.s_id, st.s_name, n.avg_score ## 使用窗口函数必须使用DISTINCTFROM(SELECT*FROM(SELECT s_id, s_score,AVG(s_score)OVER(PARTITIONBY s_id) avg_score
FROM Score
) m
WHERE m.avg_score >85) n
JOIN Student st
ON n.s_id = st.s_id
# 聚合函数SELECT st.s_id, st.s_name,AVG(sc.s_score) avg_score
FROM Student st
JOIN Score sc
ON st.s_id = sc.s_id
GROUPBY st.s_id
HAVING avg_score >=85;
SELECT st.s_name, sc.s_score
FROM Score sc
JOIN Course c
ON sc.c_id = c.c_id
AND c.c_name ="数学"# 因为是INNER JOIN,可以不放在WHEREAND sc.s_score <60JOIN Student st
ON sc.s_id = st.s_id
35、查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
SELECT st.s_id, st.s_name, sc.c_id, c.c_name, sc.s_score
FROM Student st
LEFTJOIN Score sc
ON sc.s_id = st.s_id
LEFTJOIN Course c
ON c.c_id = sc.c_id
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
## 错误:没有考虑没有选课和没有分数的情况-- SELECT st.s_name, c.c_name, n.s_score-- FROM -- (