問題描述
MySQL WHERE 時間戳 >= SUBDATE(MAX(timestamp), INTERVAL 5 DAY) (MySQL WHERE timestamp >= SUBDATE(MAX(timestamp), INTERVAL 5 DAY))
What's the best way to express this in one SQL query?
"Select a few random items that fall within x days of the newest item in the table."
I tried the following:
SELECT *
FROM table
HAVING `timestamp` >= SUBDATE(MAX(`timestamp`), INTERVAL 5 DAY)
ORDER BY RAND()
LIMIT 10
But this only gives me a single result, not 10. WHERE instead of HAVING doesn't cut it because of the use of MAX().
參考解法
方法 1:
You probably want your MAX statement in a sub-query:
SELECT *
FROM table
WHERE `timestamp` >= SUBDATE((SELECT MAX(`timestamp`) FROM table), INTERVAL 5 DAY)
ORDER BY RAND()
LIMIT 10
方法 2:
SELECT *
FROM table
where `timestamp` >= (select SUBDATE(MAX(`timestamp`), INTERVAL 5 DAY) from table )
ORDER BY RAND()
LIMIT 10;