我有一个正在苦苦挣扎的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()
),并返回包含最新的recordID
但不能超过特定日期(WHERE date <= '$date'
)。有人对我可能如何处理有任何想法吗?
最佳答案
怎么样 ...
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
关于php - PHP MySQL从结果组中选择MAX(),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/6868600/