mysql - 按日期获取具有相同对象名称的最新记录

Question

如何获取最新日期的 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 语句中，如果它不为空，那么我将向表中插入新数据。

更新(我的回答):

这对我有用:

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*

Answer 1

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';