mysql - 具有额外功能的 MySQL 分组

Question

我正在尝试获取所有年份(单独)的所有关键的总和，每年有一个以上，并从三个数据库中获取信息我可以做一年，但如何做从所有。实际上有两年 1996 和 1997

这是获取 1996 年所有内容的方法，与获取 1997 年的方法几乎相同但是如何加入他们或正确获得一切

SELECT OrderID, CustomerID, OrderDate, sum(Cost) as TotalCost
FROM (SELECT Orders.CustomerID, Orders.OrderID, Orders.OrderDate, Orders.OrderID, OrderDetails.ProductID, (OrderDetails.Quantity* Products.Price) as Cost
             FROM Orders, OrderDetails, Products
             WHERE Orders.OrderID = OrderDetails.OrderID
             AND OrderDetails.ProductID= Products.ProductID)
WHERE OrderDate<="1996-12-31"
Group by CustomerID

这是我的表格订单

+---------+------------+------------+------------+-----------+
| OrderID | CustomerID | EmployeeID | OrderDate  | ShipperID |
+---------+------------+------------+------------+-----------+
|   10248 |         90 |          5 | 1996-07-04 |         3 |
|   10249 |         81 |          6 | 1996-07-05 |         1 |
|   10250 |         34 |          4 | 1996-07-08 |         2 |
|   10251 |         84 |          3 | 1997-07-08 |         1 |
|   10252 |         76 |          4 | 1997-07-09 |         2 |
+---------+------------+------------+------------+-----------+

订单详情

+---------------+---------+-----------+----------+
| OrderDetailID | OrderID | ProductID | Quantity |
+---------------+---------+-----------+----------+
|             1 |   10248 |        1 |       12 |
|             2 |   10248 |        2 |       10 |
|             3 |   10248 |        3 |        5 |
|             4 |   10249 |        1 |        9 |
|             5 |   10249 |        2 |       40 |
|             6 |   10250 |        1 |       10 |
|             7 |   10250 |        2 |       35 |
|             8 |   10250 |        3 |       15 |
+---------------+---------+-----------+----------+

和产品

+-----------+--+------------+------------+--+-------+
| ProductID |  | SupplierID | CategoryID |  | Price |
+-----------+--+------------+------------+--+-------+
|         1 |  |          1 |          1 |  |    18 |
|         2 |  |          1 |          1 |  |    19 |
|         3 |  |          1 |          2 |  |    10 |
+-----------+--+------------+------------+--+-------+

我用的是MySQL抱歉发了这么长的帖子

附言它说

no such function: year

附言我曾经使用 SQLlite

Answer 1

您可以在聚合中包含 year(OrderDate):

SELECT OrderID, CustomerID, year(OrderDate), sum(Cost) as TotalCost
FROM (SELECT Orders.CustomerID, Orders.OrderID, Orders.OrderDate, Orders.OrderID, OrderDetails.ProductID, (OrderDetails.Quantity* Products.Price) as Cost
      FROM Orders JOIN
           OrderDetails
           ON Orders.OrderID = OrderDetails.OrderID JOIN
           Products
           ON OrderDetails.ProductID = Products.ProductID
     ) x
WHERE OrderDate <= '1996-12-31'
Group by CustomerID, year(OrderDate);

实际上，子查询是不必要的，你应该使用表别名:

SELECT o.CustomerID, year(o.OrderDate), SUM(od.Quantity * p.Price) as Cost
FROM Orders o JOIN
     OrderDetails od
     ON o.OrderID = od.OrderID JOIN
     Products p
     ON od.ProductID = p.ProductID
GROUP BY CustomerId, year(OrderDate)