SQL中的GROUP BY和HAVING无效。

Question

我有一个SQL查询，它给出的结果不完整（只有一半的结果）。我想从学生表中检索同一班级的所有学生信息，然后按班级分组。

以下是查询代码：

SELECT first_name, last_name, username 
FROM students 
GROUP BY class        
HAVING class IN(SS3);

SELECT first_name, last_name, class 
FROM students 
GROUP BY class
HAVING class = 'JSS1';

SELECT user_id, first_name, last_name, class, COUNT(*) AS total_students
FROM students 
GROUP BY class
HAVING class IN ('JSS1')

最后一个查询仅显示表中第一个“jss1”班级学生的姓名，但这次显示有17个学生在jss1班级。

我该如何操作查询以显示同一班级所有学生的姓名？

以下是表结构：

CREATE TABLE IF NOT EXISTS `students` (
    `user_id` int(11) NOT NULL AUTO_INCREMENT,
    `class` varchar(10) NOT NULL,
    `first_name` varchar(30) NOT NULL,
    `last_name` varchar(50) NOT NULL,
    `username` varchar(20) NOT NULL,
    PRIMARY KEY (`user_id`)
)

- manprogram

---

通常的GROUP BY规则是：如果指定了GROUP BY子句，则SELECT列表中的每个列引用必须标识一个分组列或是集合函数的参数！ - jarlh

如果您想仅显示类别为“学生”的记录，请使用WHERE条件而不是GROUP BY。 - krishn Patel

好的...我会按照那种方式做，但我只是认为使用GROUP BY和HAVING会更好。 - manprogram

为什么你需要使用Group BY？你能展示一下你期望的输出吗？ - krishn Patel

“then group them by class”——听起来您想获得同一班级所有学生相邻的学生列表。这不是GROUP BY所做的。GROUP BY使用由WHERE子句过滤的数据生成新行。每一行都是使用在GROUP BY子句中具有相同值的表达式的行生成的。 - axiac

Answer 1

您所写的查询毫无意义，我所知道的任何其他DBMS都无法解析它们。 MySQL扩展了标准SQL使用GROUP BY的方式，以便选择列表可以引用未在GROUP BY子句中命名的非汇总列。 这既可以是一种福音也可以是一种诅咒。

为了帮助您，请考虑以下示例数据。

UserID      Class       First_name  
------------------------------------
1           A           Test 1  
2           A           Test 2  
3           B           Test 3  
4           B           Test 4  
5           C           Test 5  

现在，在您的第一个查询中（根据上面的数据进行调整），

SELECT first_name
FROM students 
GROUP BY class        
HAVING class IN('A');

我们需要首先考虑上述操作的逻辑顺序：

1. FROM
2. WHERE
3. GROUP BY
4. HAVING 
5. SELECT

所以首先要看的是分组，因为“class”是您想要每个组返回一行的分组列，因此您应该有：

class  first_name
------------------
A       ???
B       ???
C       ???

现在的having子句规定它必须是A，不再是：

class  first_name
------------------
A       ???

现在，这就是大多数DBMS失败的原因，在此阶段您需要选择first_name，但是您有两个可用值（“Test 1”和“test 2”），但只有一行要填写，并且未给引擎任何关于选择其中哪一个的指令。文档说明引擎可以自由选择任何可用值，并且排序不会对其产生影响，因此对于相同的查询，根据执行计划可能会有所不同。

虽然您对GROUP BY不是很确定，但我建议您启用ONLY_FULL_GROUP_BY。

所以这就是为什么您只能得到一行，而不是所有学生的答案。如何获取所有学生的答案非常简单，只需使用WHERE即可：

SELECT first_name, last_name, username
FROM Students
WHERE Class IN ('JSS1');

如果您希望以每个班级一行的相同格式，但列出所有学生，则可能需要使用GROUP_CONCAT：

SELECT  class, GROUP_CONCAT(CONCAT(First_name, ' ', last_name)) AS Students
FROM    Students
GROUP BY class;

最后，你只需要使用聚合函数来进行操作。例如，试图找到有两名学生的班级：

SELECT Class
FROM Students
GROUP BY Class
HAVING COUNT(*) = 2;

- GarethD

Answer 2

GROUP BY子句用于对聚合函数（如你想知道COUNT）的一些信息进行分组。

因此，当你有一个聚合函数并且想要显示它与标量字段时，这些字段必须放在GROUP BY子句中。

HAVING子句用于应用聚合值的条件（例如，我想知道所有行的COUNT > 1），我会写：HAVING COUNT() > 1，所以你的所有查询都必须使用WHERE子句。

你的查询：

错误

SELECT first_name, last_name, username 
FROM students 
GROUP BY class        
HAVING class IN(SS3);

BECOMES

SELECT first_name, last_name, username 
FROM students        
WHERE class IN (SS3);

WRONG

SELECT first_name, last_name, class 
FROM students 
GROUP BY class
HAVING class = 'JSS1';

BECOMES

SELECT first_name, last_name, class 
FROM students 
WHERE class = 'JSS1';

错误。

SELECT user_id, first_name, last_name, class, COUNT(*) AS total_students
FROM students 
GROUP BY class
HAVING class IN ('JSS1')

BECOMES

SELECT user_id, first_name, last_name, class, COUNT(*) AS total_students
FROM students 
WHERE class IN ('JSS1')
GROUP BY class, user_id, first_name, last_name

- Joe Taras

Answer 3

如果你需要给定班级的所有学生，为什么不使用where子句呢？

SELECT first_name, last_name, class 
FROM students 
WHERE class = 'JSS1';

- Damith

---

我会这样做，但是我不能使用GROUP BY吗？ - manprogram

Answer 4

如果你想列出一个给定班级的学生，请执行以下操作：

SELECT first_name, last_name, class 
FROM students 
WHERE class = 'JSS1';

查询每个班级学生数量的语句

SELECT class, count(user_id)
FROM students
GROUP BY class;

你会发现这两个查询解决的目的不同。group by子句用于将相关数据分组在一起，当然你应该在选择的列中提到所有在group by中提到的列。

你也可以使用group by来获取特定班级的学生列表，你的查询将像这样：

SELECT first_name, last_name, class 
FROM students 
GROUP BY first_name, last_name, class
HAVING class = 'JSS1';

请注意，在上述查询中使用group by是不必要的，您可以像第一个查询中一样轻松地使用where子句来实现结果。

- Ubercool