7.4.1连接查询中的分组统计
综合使用连接查询、聚合函数和分组查询可以实现很多复杂的查询需求。下面的案例可以很好地体会出连接查询的强大功能。
示例1:
计算经过站点'解放大道古田四路'的公交线路的数量以及运营车辆数量。
分析:连接线路站点表和线路表,SQL语句如下:
select count(*)线路数,sum(number)营运车辆数
from line_ station LS, Line Lwhere LS line
No=L lineNo and station='解放大道古田四路'
示例2:
统计每一家公交公 司所属线路的站点总数。
分析:解决的关键是依据公交公司分组统计该公司所有线路的站点数量,但线路车站表line. station 中仅有线路信息即线路号lineNo,却没有该线路所属公司信息,而线路表line却有公司信息即公司名company,并且这两张表都有线路号lineNo。所以,可以通过连接line_ station 表和line表,并根据line表的company列分组统计每-家公交公司所属线路的站点总数。具体的SQL语句如下:
select company公司,count(*) 站点数
from line_ station LS, line Lwhere LS .lineNo=L.line
No group by company order by count(*)
示例3:计算每条公交线路的司机人数,并显示司机人数大于 3 的分组信息,按司机人数逆序显示
分析:仅需在示例2的基础之上将司机人数大于3的分组数据过滤出来,SQL语句如下:
SELECT l.lineNo 线路号,COUNT(*) 司机数
FROM driver d,vehicle v,line l
WHERE d.driverID=v.driverID
AND v.lineID=l.lineID
GROUP BY lineNo
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC
4:统计每件商品的销售数量和销售金额,要求按照销售量和销售金额升序显示商品名、销售量和销售金额
分析:由于需要统计每件商品的销售数量和销售金额,即便某种商品没有被订购过也需要显示,所以可以采用左连接的方式一次建立商品表和订单明细表的联系,SQL语句如下:
SELECT g.goodsName 商品表,SUM(od.quantity) 销售量,SUM(g.unitPrice*od.quantity) 销售金额
FROM goods g LEFT JOIN ordersdetail od
ON g.goodsID=od.goodsID
GROUP BY g.goodsID
ORDER BY SUM(od.quantity),SUM(g.unitPrice*od.quantity)
示例5:计算每个订单的金额,要求按照订单下单日期升序和订单金额降序显示订单 ID、订单下单日期、订单金额和顾客姓名
分析:需要连接订单表、订单明细表、商品表和客户表,具体的SQL语句如下:
SELECT o.ordersID 订单ID,o.ordersDate 下单日期,SUM(od.quantity*g.unitPrice) 订单金额,c.cName 客户名
FROM orders o,ordersdetail od,goods g,customer c
WHERE o.ordersID=od.ordersID
AND od.goodsID=g.goodsID
AND o.customerID=c.customerID
GROUP BY o.ordersID
ORDER BY o.ordersDate,SUM(od.quantity*g.unitPrice) DESC