之前知道了怎么使用 havinghttp://topic.csdn.net/u/20091125/17/a1d59f31-f7cd-4f63-9513-8aab08d21816.html?1072948404类似的语句:SELECT * FROM `atten`
where date between '2009-11-1' and '2009-11-3'
group by code
having count(*)>=3这里我只查了一个时间段,并且每天都有考勤的就会显示出来,
但是,
我要查的是两个时间段,甚至三个、四个、N个时间段:
1~5号(5天)、6~7号(2天)、8~12号(5天)、13~14(2天)……(以此类推),只给定时间范围(如16~次月15号),要怎么筛选呢?
where date between '2009-11-1' and '2009-11-3'
group by code
having count(*)>=3这里我只查了一个时间段,并且每天都有考勤的就会显示出来,
但是,
我要查的是两个时间段,甚至三个、四个、N个时间段:
1~5号(5天)、6~7号(2天)、8~12号(5天)、13~14(2天)……(以此类推),只给定时间范围(如16~次月15号),要怎么筛选呢?
`id` int(11) NOT NULL auto_increment,
`user` int(11) NOT NULL,
`date` date NOT NULL,
`atten` char(6) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;INSERT INTO `atten` (`id`, `user`, `date`, `atten`) VALUES
(1, 1, '2009-11-01', '正常'),
(2, 1, '2009-11-02', '正常'),
(3, 1, '2009-11-03', '正常'),
(4, 1, '2009-11-04', '正常'),
(5, 1, '2009-11-05', '正常'),
(6, 1, '2009-11-06', '正常'),
(7, 1, '2009-11-07', '正常'),
(8, 1, '2009-11-08', '正常'),
(9, 1, '2009-11-09', '正常'),
(10, 1, '2009-11-10', '正常'),
(11, 1, '2009-11-11', '正常'),
(12, 1, '2009-11-12', '正常'),
(13, 1, '2009-11-13', '正常'),
(14, 1, '2009-11-14', '正常'),
(15, 1, '2009-11-15', '正常'),
(16, 1, '2009-11-16', '正常'),
(17, 1, '2009-11-17', '正常'),
(18, 1, '2009-11-18', '正常'),
(19, 1, '2009-11-19', '正常'),
(20, 1, '2009-11-20', '正常'),
(21, 1, '2009-11-21', '正常'),
(22, 1, '2009-11-22', '正常'),
(23, 1, '2009-11-23', '正常'),
(24, 1, '2009-11-24', '正常'),
(25, 1, '2009-11-25', '正常'),
(26, 1, '2009-11-26', '正常'),
(27, 1, '2009-11-27', '正常'),
(28, 1, '2009-11-28', '正常'),
(29, 1, '2009-11-29', '正常'),
(30, 2, '2009-11-01', '正常'),
(31, 2, '2009-11-02', '正常'),
(32, 2, '2009-11-03', '正常'),
(33, 2, '2009-11-04', '正常'),
(34, 2, '2009-11-05', '正常'),
(35, 2, '2009-11-06', '正常'),
(36, 2, '2009-11-07', '正常'),
(37, 2, '2009-11-08', '正常'),
(38, 2, '2009-11-09', '正常'),
(39, 2, '2009-11-10', '正常'),
(40, 2, '2009-11-11', '正常'),
(41, 2, '2009-11-12', '正常'),
(42, 2, '2009-11-13', '正常'),
(43, 2, '2009-11-14', '正常'),
(44, 2, '2009-11-15', '正常'),
(45, 2, '2009-11-16', '正常'),
(46, 2, '2009-11-17', '正常'),
(47, 2, '2009-11-18', '正常'),
(48, 2, '2009-11-19', '正常'),
(49, 2, '2009-11-20', '正常'),
(50, 2, '2009-11-21', '正常'),
(51, 2, '2009-11-22', '正常'),
(52, 2, '2009-11-23', '正常'),
(53, 2, '2009-11-24', '正常'),
(54, 2, '2009-11-25', '正常'),
(55, 2, '2009-11-26', '正常'),
(56, 2, '2009-11-27', '正常'),
(57, 2, '2009-11-28', '正常'),
(58, 2, '2009-11-29', '正常'),
(59, 2, '2009-11-30', '正常'),
(60, 3, '2009-11-01', '正常'),
(61, 3, '2009-11-02', '正常'),
(62, 3, '2009-11-03', '正常'),
(63, 3, '2009-11-04', '正常'),
(64, 3, '2009-11-05', '正常'),
(65, 3, '2009-11-06', '正常'),
(66, 3, '2009-11-07', '正常'),
(67, 3, '2009-11-08', '正常'),
(68, 3, '2009-11-09', '正常'),
(69, 3, '2009-11-10', '正常'),
(70, 3, '2009-11-11', '正常'),
(71, 3, '2009-11-12', '正常'),
(72, 3, '2009-11-13', '正常'),
(73, 3, '2009-11-14', '正常'),
(74, 3, '2009-11-15', '正常'),
(75, 3, '2009-11-16', '正常'),
(76, 3, '2009-11-17', '正常'),
(77, 3, '2009-11-18', '正常'),
(78, 3, '2009-11-19', '正常'),
(79, 3, '2009-11-20', '正常'),
(80, 4, '2009-11-01', '正常'),
(81, 4, '2009-11-02', '正常'),
(82, 4, '2009-11-03', '正常'),
(83, 4, '2009-11-04', '正常'),
(84, 4, '2009-11-05', '正常'),
(85, 4, '2009-11-06', '正常'),
(86, 4, '2009-11-07', '正常'),
(87, 4, '2009-11-08', '正常'),
(88, 4, '2009-11-09', '正常'),
(89, 4, '2009-11-10', '正常');CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`code` char(6) NOT NULL COMMENT '工号',
`name` char(8) NOT NULL COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;INSERT INTO `user` (`id`, `code`, `name`) VALUES
(1, '1001', '小李'),
(2, '1002', '小王'),
(3, '1003', '张'),
(4, '1004', '吴');要得到:
-------------------------------------------------------------------------------
|code|day09|day10|day11|day12|day13|day14|day15|9~13共上班天数|14~15共上班天数|
-------------------------------------------------------------------------------
|1001| 正常| 正常| 正常| 正常| 正常| 正常| 正常| 5| 5|
|1002| 正常| 正常| 正常| 正常| 正常| 正常| 正常| 5| 5|
|1003| 正常| 正常| 正常| 正常| 正常| 正常| 正常| 5| 5|
|1004| 正常| 正常| | | | | | 2| 0|
-------------------------------------------------------------------------------
+----+------+------------+-------+
| id | user | date | atten |
+----+------+------------+-------+
| 1 | 1 | 2009-11-01 | 正常 |
| 2 | 1 | 2009-11-02 | 正常 |
| 3 | 1 | 2009-11-03 | 正常 |
| 4 | 1 | 2009-11-04 | 正常 |
| 5 | 1 | 2009-11-05 | 正常 |
| 6 | 1 | 2009-11-06 | 正常 |
| 7 | 1 | 2009-11-07 | 正常 |
| 8 | 1 | 2009-11-08 | 正常 |
| 9 | 1 | 2009-11-09 | 正常 |
| 10 | 1 | 2009-11-10 | 正常 |
| 11 | 1 | 2009-11-11 | 正常 |
| 12 | 1 | 2009-11-12 | 正常 |
| 13 | 1 | 2009-11-13 | 正常 |
| 14 | 1 | 2009-11-14 | 正常 |
| 15 | 1 | 2009-11-15 | 正常 |
| 16 | 1 | 2009-11-16 | 正常 |
| 17 | 1 | 2009-11-17 | 正常 |
| 18 | 1 | 2009-11-18 | 正常 |
| 19 | 1 | 2009-11-19 | 正常 |
| 20 | 1 | 2009-11-20 | 正常 |
| 21 | 1 | 2009-11-21 | 正常 |
| 22 | 1 | 2009-11-22 | 正常 |
| 23 | 1 | 2009-11-23 | 正常 |
| 24 | 1 | 2009-11-24 | 正常 |
| 25 | 1 | 2009-11-25 | 正常 |
| 26 | 1 | 2009-11-26 | 正常 |
| 27 | 1 | 2009-11-27 | 正常 |
| 28 | 1 | 2009-11-28 | 正常 |
| 29 | 1 | 2009-11-29 | 正常 |
| 30 | 2 | 2009-11-01 | 正常 |
| 31 | 2 | 2009-11-02 | 正常 |
| 32 | 2 | 2009-11-03 | 正常 |
| 33 | 2 | 2009-11-04 | 正常 |
| 34 | 2 | 2009-11-05 | 正常 |
| 35 | 2 | 2009-11-06 | 正常 |
| 36 | 2 | 2009-11-07 | 正常 |
| 37 | 2 | 2009-11-08 | 正常 |
| 38 | 2 | 2009-11-09 | 正常 |
| 39 | 2 | 2009-11-10 | 正常 |
| 40 | 2 | 2009-11-11 | 正常 |
| 41 | 2 | 2009-11-12 | 正常 |
| 42 | 2 | 2009-11-13 | 正常 |
| 43 | 2 | 2009-11-14 | 正常 |
| 44 | 2 | 2009-11-15 | 正常 |
| 45 | 2 | 2009-11-16 | 正常 |
| 46 | 2 | 2009-11-17 | 正常 |
| 47 | 2 | 2009-11-18 | 正常 |
| 48 | 2 | 2009-11-19 | 正常 |
| 49 | 2 | 2009-11-20 | 正常 |
| 50 | 2 | 2009-11-21 | 正常 |
| 51 | 2 | 2009-11-22 | 正常 |
| 52 | 2 | 2009-11-23 | 正常 |
| 53 | 2 | 2009-11-24 | 正常 |
| 54 | 2 | 2009-11-25 | 正常 |
| 55 | 2 | 2009-11-26 | 正常 |
| 56 | 2 | 2009-11-27 | 正常 |
| 57 | 2 | 2009-11-28 | 正常 |
| 58 | 2 | 2009-11-29 | 正常 |
| 59 | 2 | 2009-11-30 | 正常 |
| 60 | 3 | 2009-11-01 | 正常 |
| 61 | 3 | 2009-11-02 | 正常 |
| 62 | 3 | 2009-11-03 | 正常 |
| 63 | 3 | 2009-11-04 | 正常 |
| 64 | 3 | 2009-11-05 | 正常 |
| 65 | 3 | 2009-11-06 | 正常 |
| 66 | 3 | 2009-11-07 | 正常 |
| 67 | 3 | 2009-11-08 | 正常 |
| 68 | 3 | 2009-11-09 | 正常 |
| 69 | 3 | 2009-11-10 | 正常 |
| 70 | 3 | 2009-11-11 | 正常 |
| 71 | 3 | 2009-11-12 | 正常 |
| 72 | 3 | 2009-11-13 | 正常 |
| 73 | 3 | 2009-11-14 | 正常 |
| 74 | 3 | 2009-11-15 | 正常 |
| 75 | 3 | 2009-11-16 | 正常 |
| 76 | 3 | 2009-11-17 | 正常 |
| 77 | 3 | 2009-11-18 | 正常 |
| 78 | 3 | 2009-11-19 | 正常 |
| 79 | 3 | 2009-11-20 | 正常 |
| 80 | 4 | 2009-11-01 | 正常 |
| 81 | 4 | 2009-11-02 | 正常 |
| 82 | 4 | 2009-11-03 | 正常 |
| 83 | 4 | 2009-11-04 | 正常 |
| 84 | 4 | 2009-11-05 | 正常 |
| 85 | 4 | 2009-11-06 | 正常 |
| 86 | 4 | 2009-11-07 | 正常 |
| 87 | 4 | 2009-11-08 | 正常 |
| 88 | 4 | 2009-11-09 | 正常 |
| 89 | 4 | 2009-11-10 | 正常 |
+----+------+------------+-------+
89 rows in set (0.00 sec)mysql> select * from user;
+----+------+------+
| id | code | name |
+----+------+------+
| 1 | 1001 | 小李 |
| 2 | 1002 | 小王 |
| 3 | 1003 | 张 |
| 4 | 1004 | 吴 |
+----+------+------+
4 rows in set (0.00 sec)mysql>
mysql> select u.code,
-> max(if(day(`date`)=09,atten,null)) as day09,
-> max(if(day(`date`)=10,atten,null)) as day10,
-> max(if(day(`date`)=11,atten,null)) as day11,
-> max(if(day(`date`)=12,atten,null)) as day12,
-> max(if(day(`date`)=13,atten,null)) as day13,
-> max(if(day(`date`)=14,atten,null)) as day14,
-> max(if(day(`date`)=15,atten,null)) as day15,
-> sum(if(day(`date`) between 9 and 13,1,0)) as `9~13共上班天数`,
-> sum(if(day(`date`) between 14 and 15,1,0)) as `14~15共上班天数`
-> from atten a inner join user u on a.user=u.id
-> where `date` between '2009-11-09' and '2009-11-15'
-> group by u.code;
+------+-------+-------+-------+-------+-------+-------+-------+----------------+-----------------+
| code | day09 | day10 | day11 | day12 | day13 | day14 | day15 | 9~13共上班天数| 14~15共上班天数 |
+------+-------+-------+-------+-------+-------+-------+-------+----------------+-----------------+
| 1001 | 正常 | 正常 | 正常 | 正常 | 正常 | 正常 | 正常 | 5| 2 |
| 1002 | 正常 | 正常 | 正常 | 正常 | 正常 | 正常 | 正常 | 5| 2 |
| 1003 | 正常 | 正常 | 正常 | 正常 | 正常 | 正常 | 正常 | 5| 2 |
| 1004 | 正常 | 正常 | NULL | NULL | NULL | NULL | NULL | 2| 0 |
+------+-------+-------+-------+-------+-------+-------+-------+----------------+-----------------+
4 rows in set (0.00 sec)mysql>