php - 如何从数据库中选择日、周、月、年的信息？

Question

我目前通过将“投票”表中的投票值与“故事”表中的文章相加来获得“评分最高的文章”。

当前 PHP 查询:

$sql = mysql_query("SELECT stories.*, SUM(votes.vote_value) as 'total_votes' FROM stories JOIN votes ON stories.id = votes.item_name GROUP BY stories.id ORDER BY total_votes DESC LIMIT 10") or die (mysql_error("There was an error in connection"));

数据库结构:

故事表:

投票表:

---

但是我想知道如何显示不同时间段的信息。说显示评分最高的文章“今天”“上周”“上个月”“去年”。基于“故事”表中的文章创建日期 st_date。我只需要这 4 个(今天、上周、月份、年份)不同的查询或部分负责获取不同时间段内的数据。

Answer 1

$date1 = "TO_DAYS(NOW()) - TO_DAYS(stories.st_date) = 0";
$date2 = "TO_DAYS(NOW()) - TO_DAYS(stories.st_date) <= 7";
$date3 = "TO_DAYS(NOW()) - TO_DAYS(stories.st_date) <= 31";
$date4 = "TO_DAYS(NOW()) - TO_DAYS(stories.st_date) <= 365";

$query = "SELECT stories.*, SUM(votes.vote_value) as 'total_votes' FROM stories JOIN votes ON stories.id = votes.item_name WHERE ".$date1." GROUP BY stories.id ORDER BY total_votes DESC LIMIT 10"
$query = "SELECT stories.*, SUM(votes.vote_value) as 'total_votes' FROM stories JOIN votes ON stories.id = votes.item_name WHERE ".$date2." GROUP BY stories.id ORDER BY total_votes DESC LIMIT 10"
$query = "SELECT stories.*, SUM(votes.vote_value) as 'total_votes' FROM stories JOIN votes ON stories.id = votes.item_name WHERE ".$date3." GROUP BY stories.id ORDER BY total_votes DESC LIMIT 10"
$query = "SELECT stories.*, SUM(votes.vote_value) as 'total_votes' FROM stories JOIN votes ON stories.id = votes.item_name WHERE ".$date4." GROUP BY stories.id ORDER BY total_votes DESC LIMIT 10"