我有一个数据库表dates
,其中包含以下格式的列:
-------------------------
| id | name | day | month |
-------------------------
| 1 | matt | 7 | 3 |
| 2 | john | 2 | 1 |
| 3 | lily | 24 | 11 |
-------------------------
按月和按天排序提取数据很容易,但是如何从今天的日期开始提取所有数据?
标准 SQL:SELECT * FROM dates ORDER BY month ASC, day ASC
我想我需要以某种方式使用CURDATE()
。
例如,今天是 4 月 17 日,所以我希望返回的数据如下所示:
-------------------------
| 3 | lily | 24 | 11 |
| 2 | john | 2 | 1 |
| 1 | matt | 7 | 3 |
-------------------------
由于 11 月 24 日是今天的下一个日期,然后是 1 月 2 日,然后是 3 月 7 日。
试试这个。
它是 2 个子查询的联合。
SELECT
rslt.id,
rslt.name,
rslt.day,
rslt.month
FROM
(
(SELECT
a.*,
DATE_FORMAT(CURDATE(), '%m-%d') AS cdate,
CONCAT(
LPAD(`month`, 2, 0),
'-',
LPAD(`day`, 2, 0)
) AS bday,
1 AS rank
FROM
`dates` AS a
HAVING cdate <= bday
ORDER BY bday ASC)
UNION
(SELECT
b.*,
DATE_FORMAT(CURDATE(), '%m-%d') AS cdate,
CONCAT(
LPAD(`month`, 2, 0),
'-',
LPAD(`day`, 2, 0)
) AS bday,
2 AS rank
FROM
`dates` AS b
HAVING cdate > bday
ORDER BY bday ASC)
) AS rslt
ORDER BY rank ASC,
bday ASC ;
此查询应该可以帮助您摆脱问题
(
SELECT DATE_FORMAT(CURDATE(), '%d') cur_day, DATE_FORMAT(CURDATE(), '%m') cur_month,`day`, `month`, 'current_year' title
FROM your_table
GROUP BY id
HAVING cur_day >= DAY AND cur_month >= month
ORDER BY `month` ASC, `day` ASC
)
UNION
(
SELECT DATE_FORMAT(CURDATE(), '%d') cur_day, DATE_FORMAT(CURDATE(), '%m') cur_month,`day`, `month`, , 'next_year' title
FROM your_table
GROUP BY id
HAVING cur_day < day and cur_month < month
ORDER BY `month` ASC, `day` ASC
)
首先,我获取当前年份的所有行,即即将到来的日期。
其次,我正在获得所有已经通过的行,并将在明年出现。
试试这个,
SELECT * FROM dates where day >= DAY(CURDATE()) ORDER BY month ASC, day ASC