通过"rep"订购帖子但仅适用于“一个"天数限制 [英] Order posts by "rep" but only for "one" day limit

问题描述

我正在尝试按rep排序帖子，但是如果post.date早于一天，则必须按正常顺序进行排序(意味着id字段排序).

这是posts表(日期为unix时间戳)；

id | date       | rep | title
------------------------------
10 | today      | 0   | lorem
9  | today      | 1   | ipsum
8  | yesterday  | 2   | dolor
7  | 2 days ago | 2   | sit
6  | 3 days ago | 10  | amet

预期的结果(试图得到这样的结果是不可能的还是胡说八道，还是我感到困惑)；

id | title
----------
9  | ipsum
10 | lorem
8  | dolor
7  | sit
6  | amet

这是我测试过的内容，它首先返回太旧的帖子；

ORDER BY CASE WHEN
  p.date + unix_timestamp(from_unixtime(p.date) + interval 1 day)
    > unix_timestamp(now()) 
  THEN p.rep ELSE p.id
END DESC
// or
...
END DESC, p.id DESC

解决方案

尝试:

ORDER BY 
  CASE WHEN
    p.date >= unix_timestamp(now() - interval 1 day)
      -- this part edited to worked vers
      -- THEN -p.rep ELSE NULL 
      THEN +p.rep ELSE NULL 
  END 
DESC, p.id DESC;

如果我的理解正确，则应按代表最近1天以内的时间排序. -p.rep和DESC应该在第一层排序中将NULL放在最后.对于日期早于一天的所有行，排序的第一层将为NULL，并显示在最后一层，随后您可以按任意顺序通过p.id进行排序.

i'm trying to get posts ordering by rep, but if post.date older than one day then it must be ordered in normal order (means id field order).

here is posts table (date is unix timestamp);

id | date       | rep | title
------------------------------
10 | today      | 0   | lorem
9  | today      | 1   | ipsum
8  | yesterday  | 2   | dolor
7  | 2 days ago | 2   | sit
6  | 3 days ago | 10  | amet

expected result (is it impossible or nonsense trying to get a result like that, or am i confused);

id | title
----------
9  | ipsum
10 | lorem
8  | dolor
7  | sit
6  | amet

here is what i tested that returns too old posts first;

ORDER BY CASE WHEN
  p.date + unix_timestamp(from_unixtime(p.date) + interval 1 day)
    > unix_timestamp(now()) 
  THEN p.rep ELSE p.id
END DESC
// or
...
END DESC, p.id DESC

解决方案

Try:

ORDER BY 
  CASE WHEN
    p.date >= unix_timestamp(now() - interval 1 day)
      -- this part edited to worked vers
      -- THEN -p.rep ELSE NULL 
      THEN +p.rep ELSE NULL 
  END 
DESC, p.id DESC;

If I understand correctly, this should order by rep for anything more recent than 1 day old. The -p.rep and DESC should work to put NULLs last in this first layer of ordering. For all the rows where the date is older than one day, the first layer of ordering would have been NULL and would appear last, and you can subsequently order by p.id in whatever order you wish.

这篇关于通过"rep"订购帖子但仅适用于“一个"天数限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！