php - 在 php/mysql 中实现基于时间的排序的最佳方法是什么？

Question

我有 1 个表格，里面装满了文章。出于这篇文章的目的，我们只说它有 4 个字段。 story_id, story_title, story_numyes, story_numno

每篇文章都可以投"is"或“否”。我将每个评分存储在另一个表中，该表包含 3 个字段:vote_storyid、vote_date(作为时间戳)、vote_code(1 = 是，0 = 否)。

因此，当有人对一篇文章投赞成票时，它会运行对 story_numyes+1 的更新查询以及插入查询以在第二个表中记录故事 ID、日期和投票代码。

我想根据有多少赞成票或反对票对文章进行排序。对于“有史以来最好的”评级显然很简单...... ORDER BY story_numyes DESC。

但是，我将如何在今天、本周、本月撰写最佳/最差文章？

我通过以下方式获取时间戳以标记每个期间的截止日期:

$yesterday= strtotime("yesterday");
$last_week = strtotime("last week");
$last_month = strtotime("last month");

但我不确定如何在 mysql 查询中利用这些时间戳来实现所需的结果。

Answer 1

尝试类似的东西

SELECT id,
SUM(CASE WHEN votedate >= $yesterday THEN 1 ELSE 0 END) AS daycount,
SUM(CASE WHEN votedate >= $last_week THEN 1 ELSE 0 END) AS weekcount,
SUM(1) AS monthcount
FROM votes
WHERE yes_no = 'YES'
AND votedate >= $last_month
GROUP BY id

然后将其设为子查询，您可以获得计数的最大值。

(请允许未经测试的查询中固有的常见语法草率。)

---

回应评论:

将其用作有效的子查询(即不相关)以获取最大值:

SELECT
MAX(daycount) AS MaxDayCount,
MAX(weekcount) AS MaxWeekCount,
MAX(monthcount) AS MaxMonthCount
FROM
(
.... all that stuff ...
) AS qcounts

但是你当然不能把它们归于id，因为它们是不同的。如果你想一次一个地使用 id，你可以

SELECT id, monthcount
FROM
(
.... all that stuff ...
) AS qcounts
ORDER BY monthcount DESC
LIMIT 1

并重复三次，每天/每周/每月一次。

注意:这一切都是为了说明您可以在一个相当高效的查询中完成的一些事情。如果您发现像其他人建议的那样分解它是最简单的(并且简单 == 好)，我不会感到惊讶。