欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL GROUP BY 详解及简单实例

程序员文章站 2023-12-11 23:25:52
 group by 语句用于结合 aggregate 函数,根据一个或多个列对结果集进行分组。 sql group by 语法 select colu...

 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 (503) 555-9831
2 united package (503) 555-3199
3 federal shipping (503) 555-9931

选自 "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;

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

上一篇:

下一篇: