mysql逆序获取记录

Question

我正在尝试以相反的顺序获取插入到 mysql 表中的最后 5 条记录:

但是对于下面的查询我无法做到这一点，我已经读过子查询可以做到这一点，但究竟在哪里申请，我很困惑:

这是我的 table

select "1" as s,`to` as f, message as m,sent  
from table1 where chat.`to` = "user1" 
union 
select "1" as s,`to` as f, message as m,sent  
from table1 where chat.`from` = "user1" 
ORDER BY sent DESC
limit 5

这是目前的结果

1   v         bbye              10  2014-09-12 02:17:06
1   Gv        not interested    9   2014-09-12 02:17:04
1   Get       football          8   2014-09-12 02:16:55
1   Get       let's play        7   2014-09-12 02:16:50
1   Gv        ok great work     6   2014-09-12 02:16:43

这是我正在尝试做的以下方式

1   Gv        ok great work     6   2014-09-12 02:16:43
1   Get       let's play        7   2014-09-12 02:16:50
1   Get       football          8   2014-09-12 02:16:55
1   Gv        not interested    9   2014-09-12 02:17:04
1   v         bbye              10  2014-09-12 02:17:06

Answer 1

子查询的格式如下:

select * from(
  select *
  from
   (select "1" as s,`to` as f, message as m,sent  
    from table1 where chat.`to` = "user1" 
    union 
    select "1" as s,`to` as f, message as m,sent  
    from table1 where chat.`from` = "user1" ) u
  ORDER BY sent DESC
  limit 5) o order by sent

然而，正如@GGio 指出的那样，重构您的查询以使用OR 会更有效:

select * from (
  SELECT "1" as s, `to` as f, message as m, sent
  FROM table1
  WHERE chat.`to` = "user1" OR chat.`from` = "user1"
  ORDER BY sent DESC
  LIMIT 5) o
order by sent