按字段进行分组而不是选择 [英] Group by a field not in select

问题描述

我想找出讲师在特定年份教过几个模块，并想选择讲师的名字和该讲师的模块数量。

I want to find how many modules a lecturer taught in a specific year and want to select name of the lecturer and the number of modules for that lecturer.

问题是因为我选择名称，所以我必须按名称对它进行分组才能使其工作。但如果有两位同名的讲师呢？然后SQL将使他们一个，这将是错误的输出。

Problem is that because I am selecting Name, and I have to group it by name to make it work. But what if there are two lecturers with same name? Then sql will make them one and that would be wrong output.

所以我真正想要做的是选择 name ，但是按 id ，这是不允许我做的。有没有办法解决这个问题？

So what I really want to do is select name but group by id, which sql is not allowing me to do. Is there a way around it?

以下是表格：

Below are the tables:

Lecturer(lecturerID, lecturerName)
Teaches(lecturerID, moduleID, year)

这是我的查询到目前为止：

This is my query so far:

SELECT l.lecturerName, COUNT(moduleID) AS NumOfModules
FROM Lecturer l , Teaches t
WHERE l.lecturerID = t.lecturerID
AND year = 2011
GROUP BY l.lecturerName --I want lectureID here, but it doesn't run if I do that

推荐答案

SELECT a.lecturerName, b.NumOfModules
FROM Lecturer a,(
SELECT l.lecturerID, COUNT(moduleID) AS NumOfModules
    FROM Lecturer l , Teaches t
    WHERE l.lecturerID = t.lecturerID
    AND year = 2011
    GROUP BY l.lecturerID) b
WHERE a.lecturerID = b.lecturerID

这篇关于按字段进行分组而不是选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！