在我们的开发过程中，往往会遇到对会员或者商家进行收益排行，分别按照当月，当周或者当日进行排序；当然，你可以先把用到的数据获取到，然后再根据你想要的功能对数据进行相应处理。下面，我要给大家演示的是直接对MySQL进行操作，利用MySQL的一些统计函数，很方便的进行数据的筛选。

首先，给大家介绍的是使用php获取当日、当周、当月以及昨日等时间戳；

获取今日开始时间戳和结束时间戳

$today_start=mktime(0,0,0,date(\'m\'),date(\'d\'),date(\'Y\'));

$today_end=mktime(0,0,0,date(\'m\'),date(\'d\')+1,date(\'Y\'))-1;

获取昨日起始时间戳和结束时间戳

$yesterday_start=mktime(0,0,0,date(\'m\'),date(\'d\')-1,date(\'Y\'));

$yesterday_end=mktime(0,0,0,date(\'m\'),date(\'d\'),date(\'Y\'))-1;

获取上周起始时间戳和结束时间戳

$lastweek_start=mktime(0,0,0,date(\'m\'),date(\'d\')-date(\'w\')+1-7,date(\'Y\'));

$lastweek_end=mktime(23,59,59,date(\'m\'),date(\'d\')-date(\'w\')+7-7,date(\'Y\'));

获取本周周起始时间戳和结束时间戳

$thisweek_start=mktime(0,0,0,date(\'m\'),date(\'d\')-date(\'w\')+1,date(\'Y\'));

$thisweek_end=mktime(23,59,59,date(\'m\'),date(\'d\')-date(\'w\')+7,date(\'Y\'));

获取本月起始时间戳和结束时间戳

$thismonth_start=mktime(0,0,0,date(\'m\'),1,date(\'Y\'));

$thismonth_end=mktime(23,59,59,date(\'m\'),date(\'t\'),date(\'Y\'));

下面是MySQL获取当天，当周，当月等数据一些基本操作；

查询当天的数据

SELECT * FROM 表名 WHERE TO_DAYS(时间字段)=TO_DAYS(NOW());

查询昨天的数据

SELECT * FROM 表名 WHERE TO_DAYS(NOW())-TO_DAYS(时间字段）=1;

查询当周的数据

SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT(时间字段,\'%Y-%m-%d\'))=YEARWEEK(NOW());

查询上周的数据

SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT(时间字段,\'%Y-%m-%d\'))=YEARWEEK(NOW())-1;

查询当月的数据

SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段,\'%Y%m\')=DATE_FORMAT(CURDATE(),\'%Y%m\');

查询上月的数据

SELECT * FROM 表名 WHERE PERIOD_DIFF(DATE_FORMAT(NOW(),\'%Y%m\'),DATE_FORMAT(时间字段,\'%Y%m\'))=1;

查询当年的数据

SELECT * FROM 表名 WHERE YEAR(时间字段) =YEAR(NOW());

查询最近7天的数据

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY)<=DATE(时间字段);

下面我们将使用上述的一些查询操作，对下图所示的收益排行功能进行数据处理；

数据格式如上图，下面直接上代码：

switch ($style) {

case \'total\'://总榜

// $total = M(\'income_log\')->group(\'userid\')->sum($field);

$total = M(\'\')->query(\"SELECT userid,SUM($field) as sum FROM income_log GROUP BY userid ORDER BY SUM($field) DESC LIMIT 10\");

break;

case \'day\'://日榜

$total = M(\'\')->query(\"SELECT userid,SUM($field) as sum FROM income_log where TO_DAYS(date)=TO_DAYS(NOW()) GROUP BY userid ORDER BY SUM($field) DESC LIMIT 10\");

break;

case \'week\'://周榜

$total = M(\'\')->query(\"SELECT userid,SUM($field) as sum FROM income_log where YEARWEEK(DATE_FORMAT(date,\'%Y-%m-%d\'))=YEARWEEK(NOW()) GROUP BY userid ORDER BY SUM($field) DESC LIMIT 10\");

break;

case \'month\'://月榜

$total = M(\'\')->query(\"SELECT userid,SUM($field) as sum FROM income_log where DATE_FORMAT(date,\'%Y%m\')=DATE_FORMAT(CURDATE(),\'%Y%m\') GROUP BY userid ORDER BY SUM($field) DESC LIMIT 10\");

break;

default:

break;

}

下面是获取到的数据结果，只需要对其进行处理即可；这里，我是通过group by 将数据以用户id进行分组，方便用户信息的获取并排名；

Array

(

[0] => Array

(

[userid] => 2

[sum] => 6.00

)

[1] => Array

(

[userid] => 1

[sum] => 6.00

)

[2] => Array

(

[userid] => 3

[sum] => 1.00

)

[3] => Array

(

[userid] => 4

[sum] => 0.20

)