mysql - 使用对象名称按日期获取最新记录

如何获取最新日期的courseName ='Music'？

courseName      |   dateOfEnrollment
----------------|-----------------------
Music           |   2016-07-24
Art             |   2016-07-01
Art             |   2016-07-23
Music           |   2016-07-25

当我尝试以下语句时

SELECT courseName, dateOfEnrollment FROM MyDatabase.dbo.courseEnrollment WHERE courseName = 'Music' AND GETDATE() >= MAX(dateOfEnrollment)

它返回错误：

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

我的最终目标是将该语句放入IF语句中，如果它不为null，则将新数据插入表中。

更新（我的回答）：

这为我工作：

IF EXISTS (SELECT TOP 1 courseName, dateOfEnrollment FROM courseEnrollment WHERE courseName = 'Music' ORDER BY dateOfEnrollment DESC)
    *Insert data into the table*

ELSE
    *No need to insert*

最佳答案

SELECT
    courseName,
    dateOfEnrollment
FROM
    MyDatabase.dbo.courseEnrollment
WHERE
    courseName = 'Music'
ORDER BY
    dateOfEnrollment
LIMIT 1

这就是查询的外观，但是假设您的主要目标是-您只需要检查是否有“音乐”行，就根本不需要对dateOfEnrollment列感兴趣。我是对还是错过了什么？

因此，您的最终查询（根据条件插入行）将为：

INSERT INTO courseEnrollment
SELECT 'Music', CURDATE() FROM courseEnrollment
WHERE courseName = 'Music';

关于mysql - 使用对象名称按日期获取最新记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38566458/