MySQL经典编程问题

 

  星期数的问题

  1 计算日期是周几

这个问题看似很简单,可以用MySQL内置函数来计算

(1) weekday(date)其返回值是0-6,0代表Monday, 6代表Sunday;

(2) dayofweek(date)其返回值是1-7,但是它更不好理解,1代表的是Sunday, 6代表的Saturday;

(3) dayname(date)其返回值直接是具体的名词,Monday, Tuesday...

 

以上三种方法都能够根据日期计算周几,但是都有一定的局限,相对weekday()最符合中国人的习惯。

我们还有另外一个方便的计算方法,如我们知道确切的某天是周几,如:2000-01-03是周一,那么我就可以用下列方式计算某个日期属于周几了

select (datediff(date,'2000-01-03')+1)%7,结果为1就是周一,结果为2就是周二,结果为7就是周日

select (datediff('2017-12-22','2000-01-03')+1)%7 = 5 则 2017-12-22 是周五,我们可以取任意为周一的日期作为参照基数来计算,非常符合中国人的思维习惯。

 

2 按周分组

这个问题如果使用系统函数week(date),当一个周夸年时week会将一个周拆分为两个周统计,具体情况如下:

建立测试数:

CREATE TABLE `sales` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`cost` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sales
-- ----------------------------

INSERT INTO `sales` VALUES ('8', '2017-01-01 00:00:00', '100');
INSERT INTO `sales` VALUES ('1', '2017-11-30 00:00:00', '100');
INSERT INTO `sales` VALUES ('2', '2017-12-01 00:00:00', '100');
INSERT INTO `sales` VALUES ('3', '2017-12-02 00:00:00', '100');
INSERT INTO `sales` VALUES ('4', '2017-12-31 00:00:00', '100');
INSERT INTO `sales` VALUES ('5', '2018-01-01 00:00:00', '100');
INSERT INTO `sales` VALUES ('6', '2018-01-02 00:00:00', '100');
INSERT INTO `sales` VALUES ('7', '2018-01-10 00:00:00', '100');

 

使用week函数按周获取每周的cost

select week(date),sum(cost)
from sales
group by week(date);

  

week 函数计算的结果是把 2017-12-31,2018-01-01,2018-01-02算作一周,而且是算作2017年最后一周,实际应用中我们把2018-01-01 ~ 2018-01-07视作一周, 2017-12-25 ~ 2017-12-31视作一周,所以week函数明显不符合我们实际使用需求。

 

我们介绍一种类似星期几计算的方法,同样使用日期参照法,2017-01-02为周一,方便计算我们使用它作为参照

select floor(datediff(date,'2017-01-02')/7) as week_number,
date_add('2017-01-02',interval floor((datediff(date,'2017-01-02')/7))*7 day) as begin_dt,
date_add('2017-01-02',interval floor((datediff(date,'2017-01-02')/7))*7+6 day) as end_dt,
sum(cost) as total
from sales
group by floor((datediff(date,'2017-01-02')/7));

  

  这样不但可以计算出周,还可以计算出每周的开始与结束时间。

 

 

 

 

 

 

 

posted @ 2017-12-22 23:10  Latiny  阅读(978)  评论(0编辑  收藏  举报