DATEDIFF不行?
DATEDIFF(expr1,expr2) DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation. mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
-> 1
mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
-> -31
DATEDIFF(expr1,expr2) DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation. mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
-> 1
mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
-> -31
INSERT INTO `t_yg` VALUES (2,'员工2');
INSERT INTO `t_yg` VALUES (3,'员工3');
INSERT INTO `t_yg` VALUES (4,'员工4');INSERT INTO `t_evection` VALUES(1,1,'2013-08-20 15:15','2013-08-20 08:30','2013-08-25 08:30',1);
INSERT INTO `t_evection` VALUES(2,1,'2013-08-20 15:15','2013-08-20 08:30','2013-09-08 08:30',1);
INSERT INTO `t_evection` VALUES(3,1,'2013-08-20 15:15','2013-08-20 08:30','2013-09-08 08:30',1);
INSERT INTO `t_evection` VALUES(4,2,'2013-08-20 15:15','2013-08-08 08:30','2013-08-15 08:30',1);
INSERT INTO `t_evection` VALUES(5,2,'2013-08-20 15:15','2013-08-24 08:30','2013-09-10 08:30',1);
INSERT INTO `t_evection` VALUES(6,3,'2013-08-20 15:15','2013-08-20 08:30','2013-09-08 08:30',1);
INSERT INTO `t_evection` VALUES(7,3,'2013-08-20 15:15','2013-08-20 08:30','2013-09-08 08:30',1);
INSERT INTO `t_evection` VALUES(8,4,'2013-08-20 15:15','2013-08-20 08:30','2013-09-08 08:30',1);
例如8月份的统计记录
员工 月份 请假天数
1 13-08 1
2 13-08 2
3 13-08 3
4 13-08 4
5 13-08 5
2013-08-20 - 2013-09-08?
create table canlendar(cdate date primary key);
insert into canlendar values
('2013-01-01'),('2013-01-02'),('2013-01-03'),('2013-01-04'),('2013-01-05'),('2013-01-06'),
...
('2013-12-25'),('2013-12-26'),('2013-12-27'),('2013-12-28'),('2013-12-29'),('2013-12-30'),('2013-12-31')然后可以直接查询。
select user_id,DATE_FORMAT(a.cdate,'%y-%m'),count(*)
from canlendar a,t_evection b
where a.cdate between b.start_time and b.end_time
group by user_id,DATE_FORMAT(a.cdate,'%y-%m');