查询错误:聚合函数或GROUP BY子句

Question

嗨，我有一个SQL查询问题:问题是，如果我添加GROUP BY数据库引擎输出错误:

Column 'dbo.classes.class_name' is invalid in the select list because 
it is not contained in either an aggregate function or the GROUP BY clause.

我的查询是:

string query = "SELECT p.* 
                FROM dbo.classes AS p INNER JOIN teacher_classes AS a 
                 ON a.class_id = p.class_id 
                and teach_id = @id 
                GROUP BY p.class_id";

有什么需要帮助的吗?

注意没有分组的查询工作良好，但结果没有分组。

Answer 1

您的查询是:

SELECT p.*
FROM dbo.classes AS p INNER JOIN
     teacher_classes AS a
     ON a.class_id = p.class_id and teach_id = @id
GROUP BY p.class_name;

您试图从p中选择所有列，但您正在按class_name分组。这在大多数数据库中是不允许的。如果您有两个类，但是它们的信息不同，会发生什么?

一个选项是使用distinct而不是group by来删除重复项:

SELECT distinct c.*
FROM dbo.classes c INNER JOIN
     teacher_classes tc
     ON tc.class_id = c.class_id and tc.teach_id = @id;

另一个选择是使用类似in的东西来为老师找到匹配的类:

select c.*
from classes c
where c.class_id in (select tc.class_id from teacher_classes where teach_id = @id)

注意，我还更改了别名，使它们与表名有一些关系。这使得查询更容易阅读。