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

I have 1 table filled with articles. For the purpose of this post, lets just say it has 4 fields. story_id, story_title, story_numyes, story_numno

我有一张桌子上摆满了文章。出于这篇文章的目的,我们只说它有4个字段。 story_id,story_title,story_numyes,story_numno

Each article can be voted YES or NO. I store every rating in another table, which contains 3 fields: vote_storyid, vote_date (as a timestamp), vote_code (1 = yes, 0 = no).

每篇文章都可以选为YES或NO。我将每个评级存储在另一个表中,其中包含3个字段:vote_storyid,vote_date(作为时间戳),vote_code(1 =是,0 =否)。

So when somebody votes yes on an article, it run an update query to story_numyes+1 as well as an insert query to log the story id, date and vote_code in the 2nd table.

因此,当某人对某篇文章投赞成票时,它会对story_numyes + 1运行更新查询,并在第二个表中记录插入查询以记录故事ID,日期和vote_code。

I would like to sort articles based on how many YES or NO votes it has. For "Best of all time" rating is obviously simple.... ORDER BY story_numyes DESC.

我想根据它有多少YES或NO票来对文章进行排序。对于“最好的所有时间”评级显然很简单.... ORDER BY story_numyes DESC。

But how would I go about doing best/worst articles today, this week, this month?

但是,本周,本月,我将如何做最好/最糟糕的文章呢?

I get the timestamps to mark the cut-off dates for each period via the following:

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

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

But Im not sure how to utilize these timestamps in a mysql query to achieve the desired results.

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

3 个解决方案

#1

Try something like

尝试类似的东西

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 id,SUM(CASE WHEN voteedate> = $昨天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'和votedate> = $ last_month GROUP BY id

Then make that a subquery and you can get the max values for the counts.

然后使其成为子查询,您可以获得计数的最大值。

(Please allowing for the usual syntax sloppiness inherent in an untested query.)

(请考虑未经测试的查询中固有的通常语法邋。。)

In response to the comments:

回应评论:

To use it as an efficient subquery (i.e. not correlated) to get the maximum values:

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

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

SELECT MAX(daycount)AS MaxDayCount,MAX(weekcount)AS MaxWeekCount,MAX(monthcount)AS MaxMonthCount FROM(....所有东西...)AS qcounts

but of course you can't attribute them to ids, because they are different. If you want them one at a time with ids, you might

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

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

SELECT id,monthcount FROM(....所有那些东西......)AS qcounts ORDER BY monthcount DESC LIMIT 1

and do it three times, once for day/week/month.

做三次,每天一次/每周一次。

Note: this is all to illustrate some things you could accomplish in a single reasonably efficient query. I wouldn't be surprised if you were to find it's simplest (and simple == good) to break it up as others suggest.

注意:这只是为了说明您可以在一个合理有效的查询中完成的一些事情。如果你发现最简单(和简单= =好)的话就像其他人所说的那样打破它,我不会感到惊讶。

#2

In general:

select story_id, sum(vote_code)
from story_vote
group by story_id;

For particular vote date ranges:

对于特定的投票日期范围:

select story_id, sum(vote_code)
from story_vote
where vote_date >= 'least date, inclusive' 
and vote_date < 'last date, exclusive'
group by story_id;

OP comments:

How would I use the ORDER BY clause?

我如何使用ORDER BY子句?

You'd add an order by sum(vote_code). Descending if you want stories with the most votes first:

您可以通过总和添加订单(vote_code)。如果你想要获得最多票数的故事,那就下降:

order by sum(vote_code) desc;

On edit: I notice he wants all stories, not one, so I'm removing the having clause.

在编辑:我注意到他想要所有的故事,而不是一个,所以我删除了有条款。

#3

SELECT a.*, SUM(vote_code) AS votes
FROM articles a JOIN votes v ON (a.story_id = v.vote_storyid)
WHERE v.vote_date >= $yesterday
GROUP BY a.story_id
ORDER BY 2 DESC;

Likewise for $last_week and $last_month.

同样,$ last_week和$ last_month。

If you want the results to be sorted, it's better to do this in separate queries, instead of trying to do it in a single query. Because the sort order may be very different for each of the three periods.

如果您希望对结果进行排序,最好在单独的查询中执行此操作,而不是尝试在单个查询中执行此操作。因为三个时期中的每一个的排序顺序可能非常不同。

#1