mysql - 如何在主选择和连接中访问子查询

Question

我是 SQL 的新手，所以我决定弄乱 Northwind 数据库。我目前正在尝试显示有关员工的信息，特别是每个人从他们的所有订单中获得了多少钱。这是我目前必须从每个 EmployeeID 获得总金额:

SELECT Orders.EmployeeID, SUM(cost.Total) AS TotalSales
FROM Orders, (
SELECT OrderID, SUM(OrderDetails.UnitPrice * OrderDetails.Quantity) AS Total
FROM OrderDetails
GROUP BY OrderID
) AS cost
GROUP BY Orders.EmployeeID;

这工作正常，但是我无法将此子查询添加到其余选择:

SELECT Employees.EmployeeID AS ID, Employees.LastName AS Last, Employees.FirstName AS First, Employees.Title, COUNT(EmployeeTerritories.TerritoryID) AS `Number of Territories`
FROM Employees
LEFT JOIN EmployeeTerritories ON Employees.EmployeeID = EmployeeTerritories.EmployeeID
GROUP BY Employees.EmployeeID;

我已经试过了:

SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, sales.TotalSales
FROM Employees, (
SELECT Orders.EmployeeID, SUM(cost.Total) AS TotalSales
FROM Orders, (
SELECT OrderID, SUM(OrderDetails.UnitPrice * OrderDetails.Quantity) AS Total
FROM OrderDetails
GROUP BY OrderID
) AS cost
GROUP BY Orders.EmployeeID
) AS sales
LEFT JOIN sales ON Employees.EmployeeID = sales.EmployeeID
GROUP BY EmployeeID;

但是，我收到一条错误消息，指出表“northwind.sales”不存在。我想我明白这是因为它只存在于它被声明的地方，但我不确定如何解决这个问题。

如何添加此子查询以便它与 JOIN 一起工作，并且我可以拥有一个 TotalSales 列？这甚至可能吗，或者创建一个新表是我可以做到这一点的唯一方法吗？

Answer 1

您可以简化您的查询，以后更容易组合它们。第一个可以通过简单的JOIN 和GROUP BY 来完成。

SELECT o.EmployeeID, SUM(od.UnitPrice * od.Quantity) AS TotalSales
FROM Orders as o
join OrderDetails as od
    on od.[OrderID] = o.[OrderID]
GROUP BY o.EmployeeID;

select
    *
from (SELECT o.EmployeeID, SUM(od.UnitPrice * od.Quantity) AS TotalSales
FROM Orders as o
join OrderDetails as od
    on od.[OrderID] = o.[OrderID]
GROUP BY o.EmployeeID
        ) as sales
join (SELECT Employees.EmployeeID AS ID, Employees.LastName AS Last, Employees.FirstName AS First, Employees.Title, COUNT(EmployeeTerritories.TerritoryID) AS NumberOfTerritories
FROM Employees
LEFT JOIN EmployeeTerritories ON Employees.EmployeeID = EmployeeTerritories.EmployeeID
GROUP BY Employees.EmployeeID, Employees.FirstName, Employees.LastName, Employees.Title
        ) as employlees
    on sales.EmployeeID = employlees.ID