MySQL连接查询

连接查询综合应用

1.统计每一家公司所属下线路的站点总数

SELECT company,COUNT(*)
FROM line l, line_station ls
WHERE l.lineNo=ls.lineNo
GROUP BY company

2.计算经过站点“解放大道古田四路”的公交线路数量以及运营车辆数量

SELECT COUNT(*), SUM(number)
FROM line l, line_station ls
WHERE l.lineNo=ls.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(*)>3
ORDER BY COUNT(*) 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
ORDER BY SUM(od.quantity),SUM(od.quantity*g.unitPrice)

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

SELECT o.ordersID, 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 ASC,SUM(od.quantity*g.unitPrice) DESC