SELECT
*,
(
CASE
WHEN d < CURRENT_DATE THEN
- 1
WHEN d = CURRENT_DATE THEN
0
WHEN d > CURRENT_DATE THEN
1
END
) AS x
FROM
(
SELECT
id,
user_name,
real_name,
user_level,
birthday,
positions,
(
CASE
WHEN MONTH (CURRENT_DATE) = 12
AND MONTH (birthday) = 1 THEN
DATE_ADD(d, INTERVAL + 1 YEAR)
WHEN MONTH (CURRENT_DATE) = 1
AND MONTH (birthday) = 12 THEN
DATE_ADD(d, INTERVAL - 1 YEAR)
ELSE
d
END
) d
FROM
(
SELECT
*, STR_TO_DATE(
REPLACE (
DATE_FORMAT(birthday, '%Y-%m-%d'),
YEAR (birthday),
YEAR (CURRENT_DATE)
),
'%Y-%m-%d'
) d
FROM
user_info
) t1
) t2
WHERE
t2.d BETWEEN ADDDATE(CURRENT_DATE, - 30)
AND ADDDATE(CURRENT_DATE, 30)
ORDER BY
x ASC,
t2.d ASC
MySQL 取生日段
最新推荐文章于 2021-02-02 03:53:20 发布