MySQL按日期排序但忽略年份 [英] Mysql sort by date but ignore year

问题描述

我有一个表，该表具有Mysql DATE字段类型.

I have a table which has a Mysql DATE field type.

我正在搜索条目，需要在接下来的30天内尽快对条目进行排序.但是，有些条目上有年份，由于重复数据，现在已经过期了.

I am searching the entries and need to sort the entries soonest first within the next 30 days. BUT, some of entries will have years on them which have now expired as it is recurring data.

对于日期，我使用的是DAYOFYEAR，效果很好.

For the date find I'm using DAYOFYEAR which works great.

问题是:我可以先将以下几类划分为最少几天，直到活动开始吗?

Question is: Can I sort the following into least number of days until the event, first?

select b.name, ue.event_title, ue.event_date from brains b
join user_events ue on b.user_id=ue.user_id
where b.user_id=63 and 
MOD(DAYOFYEAR(ue.event_date) - DAYOFYEAR(CURDATE()) + 365, 365) <= 30 
order by event_date desc 

order by event_date desc是因为年份出错的地方

order by event_date desc is where it goes wrong because of year

推荐答案

弄清楚了:

答案

select b.name, ue.event_title, ue.event_date,
(DAYOFYEAR(ue.event_date) - DAYOFYEAR(CURDATE())) as days
from brains b
join user_events ue on b.user_id=ue.user_id
where b.user_id=63 and 
MOD(DAYOFYEAR(ue.event_date) - DAYOFYEAR(CURDATE()) + 365, 365) <= 30 
order by days asc 

这篇关于MySQL按日期排序但忽略年份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！