一道题来对mysql的连接查询复习

题目如上，下面是我按照上面的表字段自己建的表

car

customer

employee

sale

下面是对第四问的的解答，用的是mysql 的连接查询，可能方法有点麻烦。

我的想法很简单，这个查询针对多个表，我就把需要查询的结果一个个的先做简单查询，最后再进行组合。

第一步：从sale和car表中查出每个员工的销售额：

SELECT sale.EMPnum,SUM((sale.CARnum*car.Cprice)) AS money  FROM sale,car WHERE sale.CARnum=car.Cid GROUP BY EMPnum;#从sale和car表中查出每个员工的

第二步：查询每个员工的销售数量：

SELECT EMPnum,COUNT(EMPnum) AS num FROM sale GROUP BY EMPnum;#查询每个员工的销售数量　　

第三步：查询员工的名称：　　

SELECT Eid,Ename FROM Employee;#查询员工的名字

第四步：将第一步和第二步的结果做为这次查询的表，查询每个员工的销售额和销售数量：

 

SELECT t1.EMPnum,t1.money,t2.num FROM (SELECT sale.EMPnum,SUM((sale.CARnum*car.Cprice)) AS money  FROM sale,car WHERE sale.CARnum=car.Cid GROUP BY EMPnum) AS t1 ,(SELECT EMPnum,COUNT(EMPnum) AS num FROM sale GROUP BY EMPnum) AS t2 WHERE t1.EMPnum=t2.EMPnum;

 

　第五步：将第四步的结果和第三步的结果做为这步的查询表，查询最后的结果

SELECT t3.EMPnum,t3.money,t3.num,t4.Ename FROM (SELECT t1.EMPnum,t1.money,t2.num FROM (SELECT sale.EMPnum,SUM((sale.CARnum*car.Cprice)) AS money  FROM sale,car WHERE sale.CARnum=car.Cid GROUP BY EMPnum) AS t1 ,(SELECT EMPnum,COUNT(EMPnum) AS num FROM sale GROUP BY EMPnum) AS t2 WHERE t1.EMPnum=t2.EMPnum) AS t3,(SELECT Eid,Ename FROM Employee) AS t4 WHERE t3.EMPnum=t4.Eid;