mysql程序题_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

groupby 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));

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