今天在写一个关于上一周每天的流量数据查询,起初的想法是用week函数来处理这个问题,用week函数减一,然后按WEEKDAY来分组,即可得到上周的数据。
SELECT
WEEKDAY(a.leave_time) AS '星期',COUNT(a.leave_time) AS '出场流量'
FROM
(
SELECT
a.leave_time
FROM
biz_vehicle_collect_fee_record_day a
WHERE
YEAR(a.leave_time)=YEAR(NOW()) AND WEEK(a.leave_time)= WEEK(NOW()) -1
UNION
SELECT
b.leave_time
FROM
biz_vehicle_collect_fee_record_his b
WHERE
YEAR(b.leave_time)=YEAR(NOW()) AND WEEK(b.leave_time)= WEEK(NOW()) -1
) a
GROUP BY
WEEKDAY(a.leave_time)
刚开始觉得这样并没有问题,在测试的时候发现在跨年的情况下就不适用了,这使得week()-1是0,而不是上一年的最后一周。
因此只能想其他办法去解决,这是的思路就是:将时间放在函数内部处理,然后在去和数据库的时间进行比较。
解决方案如下:
SELECT
WEEKDAY(a.leave_time) AS '星期',
COUNT(a.leave_time) AS '出场流量'
FROM
(
SELECT
a.leave_time
FROM
biz_vehicle_collect_fee_record_day a
WHERE
YEARWEEK(a.leave_time,1) = YEARWEEK(DATE_SUB(NOW(), INTERVAL 7 DAY),1)
UNION
SELECT
b.leave_time
FROM
biz_vehicle_collect_fee_record_his b
WHERE
YEARWEEK(b.leave_time,1) = YEARWEEK(DATE_SUB(NOW(), INTERVAL 7 DAY),1)
) a
GROUP BY
WEEKDAY(a.leave_time)
如上,利用DATE_SUB(NOW(), INTERVAL 7 DAY) 函数,先对时间惊醒处理,减去七天,然后再用 YEARWEEK函数去跟数据库里的时间进行筛选,最后再按WEEKDAY分组,这样就可以处理掉跨年时的问题了。