我有一个条形图要填充，需要7个总和1代表一周的每一天，星期一至星期日。
星期一是工作周的开始，星期日是工作周的最后一天。
数据库中的数据是这样的
user1|2013-04-13 16:53:55|1
user1|2013-03-13 16:53:55|1
user1|2013-03-13 15:53:55|1
user1|2013-02-13 09:53:55|1
user1|2013-04-13 07:53:55|1
user2|2013-03-13 15:53:55|1
行数是每个用户的和
这是一个找出一周中天数的方法。
//find day of week
$dayofweek = date('l', strtotime(date("Y-m-d H:i:s")));
$dates = array();
if ($dayofweek == "Monday") {
$dates['monday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Monday last week' ));
$dates['monday']['evening']= date( 'Y-m-d 23:59:59', strtotime( 'Monday last week' ));
$dates['tuesday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Tuesday this week' ));
$dates['tuesday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Tuesday this week' ));
$dates['wednesday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Wednesday this week' ));
$dates['wednesday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Wednesday this week' ));
$dates['thursday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Thursday this week' ));
$dates['thursday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Thursday this week' ));
$dates['friday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Friday this week' ));
$dates['friday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Friday this week' ));
$dates['saturday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Saturday this week' ));
$dates['saturday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Saturday this week' ));
$dates['sunday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Sunday this week' ));
$dates['sunday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Sunday this week' ));
}
elseif ($dayofweek == "Tuesday") {
$dates['monday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Monday last week' ));
$dates['monday']['evening']= date( 'Y-m-d 23:59:59', strtotime( 'Monday last week' ));
$dates['tuesday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Tuesday last week' ));
$dates['tuesday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Tuesday last week' ));
$dates['wednesday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Wednesday this week' ));
$dates['wednesday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Wednesday this week' ));
$dates['thursday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Thursday this week' ));
$dates['thursday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Thursday this week' ));
$dates['friday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Friday this week' ));
$dates['friday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Friday this week' ));
$dates['saturday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Saturday this week' ));
$dates['saturday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Saturday this week' ));
$dates['sunday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Sunday this week' ));
$dates['sunday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Sunday this week' ));
}
elseif ($dayofweek == "Wednesday") {
$dates['monday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Monday last week' ));
$dates['monday']['evening']= date( 'Y-m-d 23:59:59', strtotime( 'Monday last week' ));
$dates['tuesday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Tuesday last week' ));
$dates['tuesday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Tuesday last week' ));
$dates['wednesday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Wednesday last week' ));
$dates['wednesday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Wednesday last week' ));
$dates['thursday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Thursday this week' ));
$dates['thursday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Thursday this week' ));
$dates['friday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Friday this week' ));
$dates['friday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Friday this week' ));
$dates['saturday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Saturday this week' ));
$dates['saturday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Saturday this week' ));
$dates['sunday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Sunday this week' ));
$dates['sunday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Sunday this week' ));
}
elseif ($dayofweek == "Thursday") {
$dates['monday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Monday last week' ));
$dates['monday']['evening']= date( 'Y-m-d 23:59:59', strtotime( 'Monday last week' ));
$dates['tuesday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Tuesday last week' ));
$dates['tuesday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Tuesday last week' ));
$dates['wednesday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Wednesday last week' ));
$dates['wednesday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Wednesday last week' ));
$dates['thursday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Thursday last week' ));
$dates['thursday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Thursday last week' ));
$dates['friday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Friday this week' ));
$dates['friday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Friday this week' ));
$dates['saturday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Saturday this week' ));
$dates['saturday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Saturday this week' ));
$dates['sunday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Sunday this week' ));
$dates['sunday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Sunday this week' ));
}
elseif ($dayofweek == "Friday") {
$dates['monday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Monday last week' ));
$dates['monday']['evening']= date( 'Y-m-d 23:59:59', strtotime( 'Monday last week' ));
$dates['tuesday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Tuesday last week' ));
$dates['tuesday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Tuesday last week' ));
$dates['wednesday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Wednesday last week' ));
$dates['wednesday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Wednesday last week' ));
$dates['thursday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Thursday last week' ));
$dates['thursday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Thursday last week' ));
$dates['friday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Friday last week' ));
$dates['friday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Friday last week' ));
$dates['saturday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Saturday this week' ));
$dates['saturday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Saturday this week' ));
$dates['sunday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Sunday this week' ));
$dates['sunday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Sunday this week' ));
}
elseif ($dayofweek == "Saturday") {
$dates['monday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Monday last week' ));
$dates['monday']['evening']= date( 'Y-m-d 23:59:59', strtotime( 'Monday last week' ));
$dates['tuesday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Tuesday last week' ));
$dates['tuesday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Tuesday last week' ));
$dates['wednesday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Wednesday last week' ));
$dates['wednesday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Wednesday last week' ));
$dates['thursday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Thursday last week' ));
$dates['thursday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Thursday last week' ));
$dates['friday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Friday last week' ));
$dates['friday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Friday last week' ));
$dates['saturday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Saturday last week' ));
$dates['saturday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Saturday last week' ));
$dates['sunday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Sunday this week' ));
$dates['sunday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Sunday this week' ));
}
elseif ($dayofweek == "Sunday") {
$dates['monday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Monday last week' ));
$dates['monday']['evening']= date( 'Y-m-d 23:59:59', strtotime( 'Monday last week' ));
$dates['tuesday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Tuesday last week' ));
$dates['tuesday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Tuesday last week' ));
$dates['wednesday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Wednesday last week' ));
$dates['wednesday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Wednesday last week' ));
$dates['thursday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Thursday last week' ));
$dates['thursday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Thursday last week' ));
$dates['friday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Friday last week' ));
$dates['friday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Friday last week' ));
$dates['saturday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Saturday last week' ));
$dates['saturday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Saturday last week' ));
$dates['sunday']['morning'] = date( 'Y-m-d 00:00:01', strtotime( 'Sunday last week' ));
$dates['sunday']['evening'] = date( 'Y-m-d 23:59:59', strtotime( 'Sunday last week' ));
}
我应该为一周中的每一天创建7个查询吗?
SELECT myDateTime FROM `mylog` WHERE myDateTime >= $dates['monday']['morning'] AND myDateTime <= $dates['monday']['evening']
还是有更简单更有效的方法?
谢谢。
======================== 编辑1 ============================
这是我到目前为止写的。
SELECT date( myDateTime ) , sum( myValue )
FROM mylog
GROUP BY date( myDateTime )
如何指定我想要的日期，比如星期一?
是否有一种方法可以将其变为1个查询而不是7个?
谢谢。
我想你想要的可能是这个。虽然我可能错了
SELECT user, DAYOFWEEK(myDateTime) as day_of_week, SUM(myValue) as total
FROM mylog
WHERE myDateTime >= DATE_FORMAT(NOW() - INTERVAL 1 WEEK, "%Y-%m-%d")
GROUP BY user, DAYOFWEEK(myDateTime)
希望这是不言自明的?WHERE子句给出上周的所有记录，GROUP BY将首先按用户分组，然后按星期(1 =星期天)分组。这个东西的手册是http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_dayofweek。所以你应该以
结尾user | day_of_week | total
------+---------------+-------
user1 | 1 | 23
user1 | 2 | 14
...
user1 | 7 | 65
user2 | 1 | 12
user2 | 2 | 78
等等。这似乎是你一开始就问的问题?