我正在尝试从当前日期开始对结果进行升序排序
这就是我现在使用的;
SELECT * FROM friends JOIN bdays
ON bdays.user = friends.friendname
WHERE username = $userid ORDER BY DATE_FORMAT(date, '%m %d')
有任何想法吗?
example现在按日期排序,从一月开始对生日进行排序
我需要的不是从一月份开始列出清单,而是从当前日期开始。
所以,而不是;
January
February
March
April
May
June
July
August
September
November
December
它会像这样排列它们;
April (current month/day)
May
June
July
August
September
November
December
January
February
March
April (all the way up to yesterday)
我是这样做的:
SELECT *, (DATE_FORMAT(date, '%j')-DATE_FORMAT(NOW(), '%j')+365)%365 AS d
FROM foo ORDER BY d;
The %j
日期格式是一年中的第几天,即数字 001...366。
我在一些示例数据上对此进行了测试,它按照您描述的方式进行排序:它忽略年份,并首先对当前日期之后的下一个日期进行排序,然后升序,并回绕到今年早些时候的日期。
+----+------------+------+
| id | date | d |
+----+------------+------+
| 5 | 1999-05-15 | 27 |
| 6 | 1992-06-15 | 59 |
| 7 | 1990-07-15 | 88 |
| 8 | 1988-08-15 | 120 |
| 9 | 1980-11-15 | 212 |
| 1 | 2010-01-15 | 272 |
| 2 | 2009-02-15 | 303 |
| 3 | 2004-03-15 | 332 |
| 4 | 2002-04-15 | 362 |
+----+------------+------+
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)