php - SQL 分页查询数据按照生日日期从近到远排序应该如何处理？

Question

现有一张用户数据表 user_table 里面有个 birthday 字段存放用户出生日期（如 1994-01-01、1996-07-1）对应的时间戳数据

现在需要按照生日从近到远的排序来分页查找用户数据
比如今天 2016-07-28 的话，生日日期分别为
1995-07-30、1993-11-31、1997-08-29、1995-07-27
的四个用户数据排序后就是
1995-07-30、1997-08-29、1993-11-31、1995-07-27

SQL 语句怎么写才能满足这样的查询条件？
或者说我这存放生日的数据表结构应该修改成怎样才合适？

Answer 1

不考虑数据量和性能问题的简单做法:

1. 加一个字段, 保存dayofyear的信息, 比如说这个字段名就叫day_of_year, 那么你插入语句是这样的:

insert into user_table set ... = ..., day_of_year = DAYOFYEAR('1995-07-30');

1. 查询的时候查询两次, 首先是查询 day_of_year 字段大于你的当前值的, 然后查询这个字段小于当前值的, 按 day_of_year 排序:

select * from user_table where day_of_year >= DAYOFYEAR(CURDATE()) order by day_of_year asc;
select * from user_table where day_of_year < DAYOFYEAR(CURDATE()) order by day_of_year asc;

Answer 2

先前少看一个条件
mysql 把时间 1995-07-30 ，先转换成一年中的第几天 DAYOFYEAR('1995-07-30'),减去今天是一年中的第几天，得到一个差值然后order by 一下就行。

如果生日的月日一样，可以再按年递增排序。

select * from user_table order by (DAYOFYEAR(birthday)-DAYOFYEAR(curdate())),year(birthday) ASC

Answer 3

思路:你这个其实就是先按相减之后的月排,排完之后再按相减之后的日排

Answer 4

mysql> SELECT name, birthday FROM user_table
 -> WHERE MONTH(birthday) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH))
 -> ORDER BY DAY(birthday) asc;
 

上面这个代码可以实现获取当前月份之后1个月的生日数据并按照日升序排列，你再写个repeat把后面的取出来就好了

Answer 5

DROP TEMPORARY TABLE IF EXISTS aa;
DROP TEMPORARY TABLE IF EXISTS bb;
create temporary table aa(select birthday from user_table 
WHERE substring(birthday,6,10)>'07-28' ORDER BY substring(birthday,6,10) asc);
create temporary table bb(select birthday from user_table 
WHERE substring(birthday,6,10)<'07-28' ORDER BY substring(birthday,6,10) asc );
SELECT * FROM aa Union All SELECT * FROM bb
测试了一下，效果如图所示

1995-07-30
1997-08-29
1993-11-31
1999-01-01
1995-07-27