按日期排序时间降序?

2021-11-20 00:00:00 sql mysql

全部我想显示特定 id 的最后 5 个输入数据.我的 sql 查询是，

all I want to display last 5 entered data for specific id. My sql query is,

SELECT id, name, form_id, DATE(updated_at) as date
  FROM wp_frm_items
  WHERE user_id = 11 && form_id=9
  ORDER BY updated_at DESC

updated_at 是 DATETIME

updated_at is DATETIME

它显示按日期而不是按时间排序的最后 5 个条目.在同一天，然后按字母顺序排序.

It displays last 5 entry sort by date not by time. On same date then it is sorting alphabetically.

假设我在同一日期有 3 个条目，但时间不同

Suppose i have 3 entries in same date with diff time

说吧

Ajay 1/3/2012 1:15
John 1/3/2012 1:00
Bony 1/3/2012 1:10

查询上面的查询后

我得到的是

Ajay 1/3/2012 1:15
Bony 1/3/2012 1:10
John 1/3/2012 1:00

按日期然后按字母顺序排序

Sort by date then after alphabetically

我想要的是这个..

John 1/3/2012 1:00
Bony 1/3/2012 1:10
Ajay 1/3/2012 1:15

也按日期和时间排序...

Sorted by date and time also...

推荐答案

如果你想要最后5行，升序排列，你需要一个子查询:

If you want the last 5 rows, ordered in ascending order, you need a subquery:

SELECT *
FROM
    ( SELECT id, name, form_id, DATE(updated_at) AS updated_date, updated_at
      FROM wp_frm_items
      WHERE user_id = 11 
        AND form_id=9
      ORDER BY updated_at DESC
      LIMIT 5
    ) AS tmp
ORDER BY updated_at

<小时>

在第 10 次阅读问题后，这可能(只是可能)是您想要的.按日期降序排序，然后按时间(同一日期)升序排序:

After reading the question for 10th time, this may be (just maybe) what you want. Order by Date descending and then order by time (on same date) ascending:

SELECT id, name, form_id, DATE(updated_at) AS updated_date
FROM wp_frm_items
WHERE user_id = 11 
  AND form_id=9
ORDER BY DATE(updated_at) DESC
       , updated_at ASC

相关文章