mysql - 从今天到昨天排序

Question

我正在尝试按日期订购查询，从今天到昨天。到目前为止，我可以通过两个查询来做到这一点

SELECT title, premiered 
FROM movies 
WHERE DATE_FORMAT(premiered, '%m-%d') >= DATE_FORMAT(NOW(), '%m-%d') 
ORDER BY MONTH(premiered) ASC, DAY(premiered) ASC

然后

SELECT title, premiered 
FROM movies 
WHERE DATE_FORMAT(premiered, '%m-%d') < DATE_FORMAT(NOW(), '%m-%d') 
ORDER BY MONTH(premiered) ASC, DAY(premiered) ASC

我似乎无法组合这两个查询并正确排序输出。

标签： mysql

Answer 1

您希望premiered有CURRENT_DATE0 天或 1 天的差异：

SELECT title, premiered 
FROM movies 
WHERE DATEDIFF(CURRENT_DATE, premiered) IN (0, 1)
ORDER BY premiered

编辑新要求：

SELECT title, premiered 
FROM movies 
WHERE premiered BETWEEN CURRENT_DATE - INTERVAL 1 DAY AND CURRENT_DATE + INTERVAL 1 MONTH 
ORDER BY premiered