PHP MySQL Select MAX() from Group of Results

Question

我有一个MySQL查询，我正在努力，我只是不能使它工作。

是这样的:

SELECT 
    DISTINCT reminders.recordID 
FROM 
    reminders, 
    (SELECT 
        DISTINCT recordID, 
        MAX(date) as MaxDate 
     FROM reminders 
     GROUP BY recordID
    ) dts 
WHERE 
    reminders.owner = '$owner'
    AND reminders.date = dts.MaxDate

我需要获取表中的所有提醒(提醒)，例如，recordID 14。然后，我需要从该结果集中选择最近的日期(MAX())，并返回具有最近日期但不超过特定日期(WHERE date <= '$date')的recordID。

有谁知道我该怎么做吗?

Answer 1

…

SELECT recordID, date
FROM reminders
WHERE owner = $owner
AND date <= $date
ORDER BY date DESC
LIMIT 1

…哦，我想我现在明白了:"每个ID可能附带几个提醒"澄清了一些问题。因此，可能有一个更优雅的解决方案，但我猜这将工作:

    SELECT DISTINCT r1.recordID,
        (SELECT r2.date
            FROM reminders as r2
            WHERE r2.owner = $owner
            AND r2.date <= $date
            AND r2.recordID = r1.recordID
            ORDER BY r2.date DESC
            LIMIT 1) as maxDate
    FROM reminders as r1
    WHERE r1.owner = $owner
    AND r1.date <= $date