根据当前日期对数据进行排序 [英] Sorting data according to current date

问题描述

我有一个这样的 posts 表格

I have a table of posts like this

+--------------------+--------------+
| Field              | Type         |
+--------------------+--------------+
| id                 | int(11)      |
| title              | varchar(255) |
| body               | text         | 
| published_at       | datetime     |
+--------------------+--------------+

我想要实现的是通过published_atorder.通常我会这样做:

What I want achieve is to order by published_at. Normally I would do:

SELECT * FROM posts ORDER BY published_at;

但我的要求是查询应该从当前日期获取结果，然后是以前的结果，然后是从未来获取结果.

But my requirement here is that the query should fetch the results from current date on top and then the previous ones and after that fetch those from future.

目前我的结果如下:

+-------------------------------+----+---------------------+
| title                         | id | published_at        |
+----------------------------------------------------------|
| Hello world                   |  1 | 2015-01-06 12:21:16 |
| 20+ Tools For RoR Development |  2 | 2015-08-25 12:21:23 |
| Angular JS tutorial           |  3 | 2015-09-31 10:51:55 |
| Visual search                 |  4 | 2015-03-12 12:27:26 |
| Ruby on Rails best practices  |  5 | 2015-01-21 00:00:00 |
+-------------------------------+----+---------------------+  

而我想要的结果是:

+-------------------------------+----+---------------------+
| title                         | id | published_at        |
+----------------------------------------------------------|
| 20+ Tools For RoR Development |  2 | 2015-08-25 12:21:23 |
| Hello world                   |  1 | 2015-01-06 12:21:16 |
| Ruby on Rails best practices  |  5 | 2015-01-21 00:00:00 |
| Visual search                 |  4 | 2015-03-12 12:27:26 |
| Angular JS tutorial           |  3 | 2015-09-31 10:51:55 |
+-------------------------------+----+---------------------+

推荐答案

不使用 UNION/CASE 的解决方案

Solution without using UNION/CASE

SELECT * FROM posts
ORDER BY
    DATE(published_at)=DATE(NOW()) DESC,
    DATE(published_at)<DATE(NOW()) DESC,
    DATE(published_at)>DATE(NOW()) ASC`

检查这是否有效.它可以很好地处理受激数据.您可以根据您对过去和未来日期的排序要求将 desc 更改为 asc

check if this works. Its working fine with stimulated data. You can change desc to asc according to your sorting requirement for past and future dates

这篇关于根据当前日期对数据进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！