mysql - GROUP BY 并以相同的值显示一次

Question

我想在具有相同值的多行中显示一次

我的查询是这样的

SELECT name, 
       GROUP_CONCAT(class) class, 
       COUNT(*) AS total 
FROM report 
GROUP BY name

和这样的数据

+----+----------+-------+
| ID | NAME     | CLASS | 
+----+----------+-------+
|  1 | John     |  10   | 
|  2 | James    |  21   | 
|  3 | James    |  21   | 
|  4 | John     |  10   | 
|  5 | Augie    |  22   | 
|  6 | Augie    |  22   | 
|  7 | John     |  10   | 
+----+----------+-------+

我想做这样的输出，名字会显示一次

Name : John, class : 10, Total : 3
Name : James, class : 21, Total : 2
Name : Augie, class : 22, Total : 2

标签： mysqlsql

Answer 1

试试这个查询

SELECT name,Class,COUNT(*) AS total FROM report GROUP BY name,Class