mysql - 我想在 "Numbers Of Courses"列中计算 CourseName like(4,2,5)，但输出显示的是完整的 courseName

Question

我想显示教师的全名和他们目前正在教授的类(class)数量。

我的查询是:

SELECT CONCAT( FName, ' ', LName ) AS "Faculty Names", course.CourseName AS 'NUmbers Of Courses'
FROM Faculty LEFT JOIN course ON (course.FacultyID = faculty.FacultyID) 
ORDER BY faculty.FacultyID`

问题 1:我想在“类(class)数量”列中计算类(class)名称(4,2,5)，但显示的输出是完整的类(class)名称。

问题 2:在“类(class)编号”列中使用左联接，一些行具有 Null 值，但我想在那里显示 0。

Answer 1

您需要一个GROUP BY 和COUNT。检查这个 -

SELECT CONCAT( FName, ' ', LName ) AS "Faculty Names",
COUNT(course.CourseName) AS 'NUmbers Of Courses', faculty.FacultyID
FROM Faculty 
LEFT JOIN course ON (course.FacultyID = faculty.FacultyID) 
GROUP BY faculty.FacultyID, "Faculty Names"
ORDER BY faculty.FacultyID 

Note : You should include FacultyID while GROUP BY, because, you never can be sure that you will not have same FName + LName for any 2 Facultes.