连接查询综合应用
一、 连接查询的分组统计
示例: 1.统计每一家公交公司所属线路的站点总数
SELECT
company 公司,
COUNT(*) 站点数
FROM
line_station LS,
line L
WHERE
LS.lineNo = L.lineNo
GROUP BY
company
ORDER BY
COUNT(*)
运行图(如下)
示例:2.计算经过站点“解放大道古田四路”的公交线路的数量以及营运车辆数量
SELECT
COUNT(*) 线路数,
SUM(number) 营运车辆数量
FROM
line_station LS,
line L
WHERE
LS.lineNo = L.lineNo
AND station = '解放大道古田四路'
运行图(如下)
示例:3. 计算每条公交线路的司机人数,并显示司机人数大于 3 的分组信息,按司机人数逆序显示;
SELECT
lineNo,
COUNT(*)
FROM
line l,
driver d,
vehicle v
WHERE
l.lineID = v.lineID
AND d.driverID = v.driverID
GROUP BY
lineNo
HAVING //这里需要用HAVING语句将司机人数大于3的分组进行一个筛选过滤
COUNT(*) > 3
ORDER BY
COUNT(*) DESC
运行图(如下)
二、 实践练习
示例:1.统计每件商品的销售数量和销售金额,要求按照销售量和销售金额升序显示商品名、销售量和销售金额
SELECT
goodsName,
SUM(quantity)销售量,
SUM(quantity * unitPrice)销售金额
FROM
goods g
LEFT JOIN ordersdetail od ON g.goodsID = od.goodsID
GROUP BY
g.goodsID
ORDER BY
销售量,
销售金额
运行图(如下)
示例:2.计算每个订单的金额,要求按照订单下单日期升序和订单金额降序显示订单 ID、订单下单日期、订单金额和顾客姓名
SELECT
o.ordersID,
ordersDate 订单日期,
SUM(quantity * unitPrice) 订单金额,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
订单日期,
订单金额 DESC
运行图(如下)