mysql - 如何计算最近一年的 MySQL 中具有旧值的表？

Question

我无法查询数据以仅获取员工在去年而不是所有年份的订单。

这是所有年份的结果:

SELECT FirstName as 'Voornaam', Lastname as 'Familienaam', COUNT(Orders.OrderDate) as 'Behandelde Orders'
FROM Employees, Orders
WHERE ReportsTo IS NOT NULL
AND Employees.EmployeeID = Orders.EmployeeID
GROUP BY Orders.EmployeeID, FirstName, Lastname
ORDER BY Orders.EmployeeID

给出

• Voornaam Familienaam Behandelde 订单
• 南希-----达沃里奥 ------123
• 珍妮特------ Leverling ----127
• 玛格丽特--孔雀----156
• 史蒂文----布坎南---42
• 迈克尔 ---须山 ----- 67
• 罗伯特 ----金 -------- 72
• 劳拉 -----卡拉汉 -----104
• 安妮 ----- Dodsworth ---43

现在我想做同样的事情，但仅限于最近一年。在这里您可以看到 Orders 表的一些信息。它包含较旧的日期:

OrderID     CustomerID EmployeeID  OrderDate               RequiredDate            ShippedDate             ShipVia     Freight               ShipName                                 ShipAddress                                                  ShipCity        ShipRegion      ShipPostalCode ShipCountry
----------- ---------- ----------- ----------------------- ----------------------- ----------------------- ----------- --------------------- ---------------------------------------- ------------------------------------------------------------ --------------- --------------- -------------- ---------------
10248       VINET      5           1996-07-04 00:00:00.000 1996-08-01 00:00:00.000 1996-07-16 00:00:00.000 3           32,38                 Vins et alcools Chevalier                59 rue de l'Abbaye                                           Reims           NULL            51100          France
10249       TOMSP      6           1996-07-05 00:00:00.000 1996-08-16 00:00:00.000 1996-07-10 00:00:00.000 1           11,61                 Toms Spezialitäten                       Luisenstr. 48                                                Münster         NULL            44087          Germany
10250       HANAR      4           1996-07-08 00:00:00.000 1996-08-05 00:00:00.000 1996-07-12 00:00:00.000 2           65,83                 Hanari Carnes                            Rua do Paço, 67                                              Rio de Janeiro  RJ              05454-876      Brazil
10251       VICTE      3           1996-07-08 00:00:00.000 1996-08-05 00:00:00.000 1996-07-15 00:00:00.000 1           41,34                 Victuailles en stock                     2, rue du Commerce                                           Lyon            NULL            69004          France
10252       SUPRD      4           1996-07-09 00:00:00.000 1996-08-06 00:00:00.000 1996-07-11 00:00:00.000 2           51,30                 Suprêmes délices                         Boulevard Tirou, 255                                         Charleroi       NULL            B-6000         Belgium
10253       HANAR      3           1996-07-10 00:00:00.000 1996-07-24 00:00:00.000 1996-07-16 00:00:00.000 2           58,17                 Hanari Carnes                            Rua do Paço, 67                                              Rio de Janeiro  RJ              05454-876      Brazil
10254       CHOPS      5           1996-07-11 00:00:00.000 1996-08-08 00:00:00.000 1996-07-23 00:00:00.000 2           22,98                 Chop-suey Chinese                        Hauptstr. 31                                                 Bern            NULL            3012           Switzerland
10255       RICSU      9           1996-07-12 00:00:00.000 1996-08-09 00:00:00.000 1996-07-15 00:00:00.000 3           148,33                Richter Supermarkt                       Starenweg 5                                                  Genève          NULL            1204           Switzerland
10256       WELLI      3           1996-07-15 00:00:00.000 1996-08-12 00:00:00.000 1996-07-17 00:00:00.000 2           13,97                 Wellington Importadora                   Rua do Mercado, 12                                           Resende         SP              08737-363      Brazil
10257       HILAA      4           1996-07-16 00:00:00.000 1996-08-13 00:00:00.000 1996-07-22 00:00:00.000 3           81,91                 HILARION-Abastos                         Carrera 22 con Ave. Carlos Soublette #8-35                   San Cristóbal   Táchira         5022           Venezuela
10258       ERNSH      1           1996-07-17 00:00:00.000 1996-08-14 00:00:00.000 1996-07-23 00:00:00.000 1           140,51                Ernst Handel                             Kirchgasse 6                                                 Graz            NULL            8010           Austria
10259       CENTC      4           1996-07-18 00:00:00.000 1996-08-15 00:00:00.000 1996-07-25 00:00:00.000 3           3,25                  Centro comercial Moctezuma               Sierras de Granada 9993                                      México D.F.     NULL            05022          Mexico
10260       OTTIK      4           1996-07-19 00:00:00.000 1996-08-16 00:00:00.000 1996-07-29 00:00:00.000 1           55,09                 Ottilies Käseladen                       Mehrheimerstr. 369                                           Köln            NULL            50739          Germany

我试过:

    SELECT FirstName as 'Voornaam', Lastname as 'Familienaam', COUNT(Orders.OrderDate) as 'Behandelde Orders'
FROM Employees, Orders
WHERE ReportsTo IS NOT NULL
AND Employees.EmployeeID = Orders.EmployeeID
GROUP BY Orders.EmployeeID, FirstName, Lastname
ORDER BY Orders.EmployeeID
HAVING Orders.OrderDate = Max(Year(Orders.OrderDate))


SELECT FirstName as 'Voornaam', Lastname as 'Familienaam', COUNT(Orders.OrderDate) as 'Behandelde Orders'
FROM Employees, Orders
WHERE ReportsTo IS NOT NULL
AND Orders.OrderDate = Max(Year(Orders.OrderDate)))
AND Employees.EmployeeID = Orders.EmployeeID
GROUP BY Orders.EmployeeID, FirstName, Lastname
ORDER BY Orders.EmployeeID

SELECT FirstName as 'Voornaam', Lastname as 'Familienaam', COUNT(Orders.EmployeeID) as 'Behandelde Orders'
FROM Employees, Orders
WHERE ReportsTo IS NOT NULL
AND Orders.OrderDate = Max(year(Orders.OrderDate))
AND Employees.EmployeeID = Orders.EmployeeID
GROUP BY Orders.EmployeeID, FirstName, Lastname
ORDER BY Orders.EmployeeID

和一些其他变体。

Answer 1

首先，永远不要在FROM 子句中使用逗号。 始终使用正确、明确的 JOIN 语法。

以下版本假定您想要每个人最近的日历年:

SELECT FirstName as Voornaam, Lastname as Familienaam,
       COUNT(o.OrderDate) as BehandeldeOrders
FROM Employees e JOIN
     Orders o
     ON e.EmployeeID = o.EmployeeID
WHERE ReportsTo IS NOT NULL AND
      YEAR(o.OrderDate) = (SELECT YEAR(MAX(o2.OrderDate))
                           FROM Orders o2
                           WHERE o2.EmployeeId = o.EmployeeId
                          )
GROUP BY o.EmployeeID, FirstName, Lastname
ORDER BY o.EmployeeID;

如果您想要总体上最近的一年，您可以删除相关子句(子查询中的 WHERE)。