mysql - 将日期合并到此 GROUP BY 和 INNER JOIN select 语句中

Question

我正在尝试列出我的奖励系统中收入最高的 20 名员工的名单。

以下语句完美地表明了这一点:

SELECT
    S.User_ID As UserID,
    CONCAT(S.User_Forename, " ", S.User_Surname) AS StudentName,
    T.TotalPoints
FROM
    student S
INNER JOIN 
    ( SELECT
        Datetime, Recipient_ID, SUM(Points) AS TotalPoints
      FROM
        transactions
      GROUP BY
        Recipient_ID ) T
ON
    S.User_ID = T.Recipient_ID
ORDER BY TotalPoints DESC
LIMIT 20

但想一想，明年，我需要包含一个日期范围(即仅显示该学年的最高收入者)。

出于测试目的，我尝试修改以下语句，其中包含 LESS THAN DATETIME选择:

SELECT
    S.User_ID As UserID,
    CONCAT(S.User_Forename, " ", S.User_Surname) AS StudentName,
    T.TotalPoints
FROM
    student S
INNER JOIN 
    ( SELECT
        Datetime, Recipient_ID, SUM(Points) AS TotalPoints
      FROM
        transactions
      GROUP BY
        Recipient_ID ) T
ON
    S.User_ID = T.Recipient_ID
WHERE t.Datetime < 2012-03-20
ORDER BY TotalPoints DESC
LIMIT 20

我也尝试过这个:

SELECT
    S.User_ID As UserID,
    CONCAT(S.User_Forename, " ", S.User_Surname) AS StudentName,
    T.TotalPoints
FROM
    student S
INNER JOIN 
    ( SELECT
        Datetime, Recipient_ID, SUM(Points) AS TotalPoints
      FROM
        transactions
      WHERE Datetime < 2012-03-20
      GROUP BY
        Recipient_ID ) T
ON
    S.User_ID = T.Recipient_ID
ORDER BY TotalPoints DESC
LIMIT 20

但是，这两者都显示空结果集。

如果我做一个简单的SELECT * FROM transactions WHERE Datetime < 2012-03-20 ，返回超过 25K 个结果。

我哪里出错了？

Answer 1

您的日期值似乎缺少单引号 ':

WHERE t.Datetime < '2012-03-20'