SQL GROUP BY 详解及简单实例

 group by 语句用于结合 aggregate 函数，根据一个或多个列对结果集进行分组。

sql group by 语法

select column_name, aggregate_function(column_name)
from table_name
where column_name operator value
group by column_name;

 演示数据库

在本教程中，我们将使用众所周知的 northwind 样本数据库。

下面是选自 "orders" 表的数据：

orderid	customerid	employeeid	orderdate	shipperid
10248	90	5	1996-07-04	3
10249	81	6	1996-07-05	1
10250	34	4	1996-07-08	2

选自 "shippers" 表的数据：

shipperid	shippername	phone
1	speedy express	[phone]
2	united package	[phone]
3	federal shipping	[phone]

选自 "employees" 表的数据：

employeeid	lastname	firstname	birthdate	photo	notes
1	davolio	nancy	1968-12-08	empid1.pic	education includes a ba....
2	fuller	andrew	1952-02-19	empid2.pic	andrew received his bts....
3	leverling	janet	1963-08-30	empid3.pic	janet has a bs degree....

 sql group by 实例

现在我们想要查找每个送货员配送的订单数目。

下面的 sql 语句按送货员进行订单分类统计：

select shippers.shippername,count(orders.orderid) as numberoforders from orders
left join shippers
on orders.shipperid=shippers.shipperid
group by shippername;

group by 一个以上的列

我们也可以对一个以上的列应用 group by 语句，如下所示：

select shippers.shippername, employees.lastname,
count(orders.orderid) as numberoforders
from ((orders
inner join shippers
on orders.shipperid=shippers.shipperid)
inner join employees
on orders.employeeid=employees.employeeid)
group by shippername,lastname;

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！