我使用 SQL Server 來解決這個問題
輸入：Student表：
student_id student_name gender dept_id
--------------------------------------------
1 Jack M 1
2 Jane F 1
3 Mark M 2
Department桌子：
dept_id dept_name
---------------------
1 Engineering
2 Science
3 Law
輸出：
dept_name student_number
---------------------------
Engineering 2
Science 1
Law 0
SELECT
d.dept_name,
COUNT(s.student_id) OVER(PARTITION BY d.dept_name) AS student_number
FROM
Department AS d
LEFT JOIN
Student AS s ON d.dept_id = s.dept_id
ORDER BY
student_number DESC, d.dept_name;
然而，我得到了
dept_name student_number
--------------------------
Engineering 2
Engineering 2
Science 1
Law 0
dept_id student_number
-----------------------
1 2
1 2
2 1
3 0
DISTINCT()可以解決這個問題，但我不明白為什么我們需要使用DISTINCT()不同的值。誰能幫忙解釋一下？謝謝
SELECT DISTINCT
d.dept_name,
COUNT(s.student_id) OVER(PARTITION BY d.dept_name) AS student_number
FROM
Department AS d
LEFT JOIN
Student AS s ON d.dept_id = s.dept_id
ORDER BY
student_number DESC, d.dept_name;
uj5u.com熱心網友回復：
只需將兩個表連接起來，然后COUNT用作聚合函式，而不是分析函式。
SELECT
d.dept_id,
d.dept_name,
COUNT(s.student_id) AS student_number
FROM Department d
LEFT JOIN Student s
ON s.dept_id = d.dept_id
GROUP BY
d.dept_id,
d.dept_name;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/435719.html
上一篇：將多行轉換為逗號分隔值