MariaDB 关键字

MariaDB GROUP BY 关键字用于借助聚合函数(计数最大值最小值总和平均值 等)。 GROUP BY 关键字位于 SELECT 语句中的 WHERE 关键字之后,并位于 ORDER BY 关键字之前。

语法

在 MariaDB 中使用 GROUP BY 关键字的语法如下:

SELECT column1, column2
FROM table_name
WHERE condition(s)
GROUP BY column1, column2
ORDER BY column1, column2; 

示例:

考虑一个名为 Employee 的数据库表 包含以下记录:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshLondon283000
6HuangLondon282800

GROUP BY 单列:查找不同年龄组的Employee表按年龄排序(降序),查询为:

SELECT Age, Count(Name) AS Number_of_Employee
FROM Employee
GROUP BY Age
ORDER BY Age DESC; 

这将产生如下所示的结果:

AgeNumber_of_Employee
301
282
271
251
241

GROUP BY 多列:要查找按城市和年龄排序的 Employee 表中的员工的平均工资,查询为:

SELECT City, Age, AVG(Salary) AS AverageSalary
FROM Employee
GROUP BY City, Age
ORDER BY City, Age; 

这将产生如下所示的结果:

CityAgeAverageSalary
Amsterdam303100.0
London253000.0
London282900.0
New York242750.0
Paris272800.0