聚合函数 = 聚合窗口函数 + DISTINCT
使用窗口函数忘了DISTINCT
就很容易出错
一定注意:窗口函数只有分区,没有分组功能(分组=分区+去重
)
例子:
-- 27、查询出只有两门课程的全部学生的学号和姓名
SELECT DISTINCT st.s_id, st.s_name # 注意去重复(窗口函数不去重)
FROM
(SELECT m.* # 注意子查询的层级关系
FROM
(
SELECT *,
COUNT(c_id) OVER(PARTITION BY s_id) cnt_course
FROM Score
) m
WHERE m.cnt_course = 2
) n
JOIN Student st
ON st.s_id = n.s_id
## 另解:直接使用聚合函数即可
SELECT st.s_id, st.s_name
FROM
(
SELECT s_id, COUNT(c_id) cnt
FROM Score
GROUP BY s_id
HAVING cnt = 2
) m
JOIN Student st
ON m.s_id = st.s_id