MySQL链接查询综合应用

 

 

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 driver D, vehicle V, line L 
WHERE D.driverID=V.driverID AND V.lineID=L.lineID GROUP BY lineNo  having count(*)
ORDER BY '司机数' DESC;

 

4.统计每件商品的销售数量和销售金额，要求按照销售量和销售金额升序显示商品名、销售量和销售金额

SELECT g.goodsName 商品名, sum(od.quantity) 销售量, sum(od.quantity*g.unitPrice) 销售金额
FROM goods g LEFT JOIN ordersdetail od ON g.goodsID=od.goodsID GROUP BY g.goodsID

 

 

5.计算每个订单的金额，要求按照订单下单日期升序和订单金额降序显示订单ID、订单下单日期、订单金额和顾客姓名

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 '下单日期', '订单金额' DESC;