mysql获取记录的相反顺序

voyeger 发表于 Dev

航海家

我试图以相反的顺序获取插入到mysql表中的最后5条记录：

但是使用以下查询我无法做到这一点，我已经读到一个subquery可以做到，但是确切地在哪里申请，我感到困惑：

这是我的桌子

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

克伦普森

子查询的格式如下：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。