链接查询综合运用

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