连接查询综合应用

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.计算每条公交线路司机人数，并显示司机人数大于三的分组信息，按司机人数逆序显示

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

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

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
	销售金额,销售量

2.计算每个订单金额，按照订单下单日期升序和订单金额降序显示订单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

总结：若想表相关联需要准确找齐每个表相关联的字段，所有列出的字段记得标记所属表名，并且每个字段后接文字时，若字段名无括号则需要空格，不然无法运行会报错