连接查询综合运用

最新推荐文章于 2024-09-12 01:15:37 发布

wj_1229_

最新推荐文章于 2024-09-12 01:15:37 发布

阅读量79

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/wj_1229_/article/details/117536832

版权

第一题

（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
ORDER BY COUNT(*)DESC;

(4)
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 SUM(od.quantity),SUM(od.quantity*g.unitPrice);

(2)
SELECT o.ordersID,o.ordersDate,SUM(od.quantity*g.unitPrice),c.cName
FROM orders d,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;