當我通過使用 self left join 使用 count 時如何顯示零？

我試圖用來count()計算特定學生的平均分數低於 40 的模組的數量。如果學生沒有任何低於 40 的評估分數，它還需要顯示數字 0。但是，我的查詢沒有顯示任何 0 數字。

CREATE TABLE assessment (
assessmentId text, 
mark integer, 
studentId text, 
moduleId text, 
PRIMARY KEY (assessmentId, studentId, moduleId), 
FOREIGN KEY(studentId) REFERENCES student(studentId), 
FOREIGN KEY(moduleId) REFERENCES module(moduleId)) ";

在每條記錄中，它顯示了評估來自的模組，並顯示了哪個學生參加了評估。

這是我的查詢

"select count(m.moduleId), avg(e.mark), m.studentId 
from assessment e 
left join assessment m on m.assessmentId = e.assessmentId 
and m.studentId = e.studentId 
and m.moduleId = e.moduleId 
group by m.studentId 
having avg(e.mark)<40";

該模組可能有多個評估，因此avg(e.mark)要找出每個學生每個模組的平均分數。因此它是分組依據studentId

所以我希望查詢顯示的是：

studentId|avg(e.mark)|count(m.moduleId)
1        | 50        | 0
2        | 20        | 2

但是我得到下面的結果

studentId|avg(e.mark)|count(m.moduleId)
1        | 20        | 2

我真的不知道問題出在哪裡

為了回答你的問題，我做了以下事情：

編輯：

抱歉，我錯過了關於 0 模組未通過任何模組的成績從未低於 40 的學生的位 - 請參閱下面的修改程式碼（自我注意 - 閱讀問題！）。幸運的是，DDL 和表插入 DML 保持不變。

我稍微修改了您的架構，如下所示（此處為小提琴）：

CREATE TABLE assessment 
(
 assessment_id INT, 
 mark INTGER, 
 student_id TEXT, 
 module_id TEXT, 
 PRIMARY KEY (assessment_id, student_id, module_id), 
 FOREIGN KEY (student_id) REFERENCES student(student_id), 
 FOREIGN KEY (module_id) REFERENCES module(module_id)
);

填充它：

INSERT INTO assessment VALUES
(1, 10, 1, 'French1'), 
(2, 20, 2, 'French1'), 
(3, 50, 3, 'French1'), 
(4, 40, 1, 'English2'), 
(5, 60, 2, 'English2'), 
(6, 90, 3, 'English2'),
(7, 30, 1, 'Maths1'),
(8, 10, 2, 'Maths1'),
(9, 80, 3, 'Maths1');

或者（參見這裡的小提琴）：

INSERT INTO assessment VALUES
(1, 10, 1, 'French1'), 
(1, 20, 2, 'French1'), 
(1, 50, 3, 'French1'), 
(2, 40, 1, 'English2'), 
(2, 60, 2, 'English2'), 
(2, 90, 3, 'English2'),
(3, 30, 1, 'Maths1'),
(3, 10, 2, 'Maths1'),
(3, 80, 3, 'Maths1');

我會說assessment_id應該是一個INTEGER，但我知道 SQLite在數據類型上有一件奇怪的事情！

然後執行上面的查詢：

SELECT
 COUNT (a2.module_id) AS "Mod. cnt",
 CASE
   WHEN 
     MIN(a1.mark) <= 40
   THEN
     COUNT(a2.module_id)
   ELSE
     0
 END AS "Failed modules",
 ROUND(AVG (a1.mark), 2) AS "Avg grade", 
 a2.student_id AS "Student id"
FROM assessment a1
 LEFT JOIN assessment a2  
 ON a2.assessment_id = a1.assessment_id 
 AND a2.student_id = a1.student_id 
 AND a2.module_id = a1.module_id 
GROUP BY a2.student_id 
HAVING avg(a1.mark) < 40 OR (SELECT MIN(a1.mark) >= 40)
ORDER BY a2.student_id, avg(a1.mark) DESC; -- ALL outer queries should have an ORDER BY!

結果（對於任何一組數據都相同）：

Mod. cnt    Failed modules  Avg grade   Student id
      3                 3      26.67            1
      3                 3         30            2
      3                 0      73.33            3

我建議你有一個單獨的module表，並JOIN使用INTEGERs 作為你PRIMARY KEY的 s - AFAIK，它比TEXT- SQL 可能變得更複雜一些，但從長遠來看這是值得的 - 標準化！您可能希望為沒有參加任何評估的學生查看SQLiteCOALESCE功能？

使用GROUP_CONCAT()，您可以執行以下操作（僅顯示最後兩行查詢 - 請參見 fiddle here）：

a2.student_id AS "Student id",
GROUP_CONCAT(a2.module_id || ', ' ||  a2.mark,  ', ') AS "Grades"

結果：

Mod. cnt    Failed modules  Avg grade   Student id  Grades
      3                 3      26.67            1  French1, 10, English2, 40, Maths1, 30
      3                 3         30            2  French1, 20, English2, 60, Maths1, 10
      3                 0      73.33            3  French1, 50, English2, 90, Maths1, 80

引用自：https://dba.stackexchange.com/questions/282977