===============sql==============
SELECT ROW_NUMBER () OVER (ORDER BY tt.id) AS RowNumber,tt.* FROM(
SELECT b.id,b.name,
MAX (CASE WHEN workingdate = '01' THEN workingtime END) 'up1',
MAX (CASE WHEN offworkdate = '01' THEN offworktime END) 'down1',
MAX (CAST ( datediff( MINUTE,
CAST ((CASE WHEN workingdate = '01' THEN workingtime END) AS datetime),
CAST ((CASE WHEN offworkdate = '01' THEN offworktime END) AS datetime)) / 60 AS VARCHAR) + '小时' + CAST (datediff(MINUTE,
CAST ((CASE WHEN workingdate = '01' THEN workingtime END) AS datetime),
CAST ((CASE WHEN offworkdate = '01' THEN offworktime END) AS datetime)) % 60 AS VARCHAR) + '分') 'count1',
MAX (CASE WHEN workingdate = '07' THEN workingtime END) 'up7',
MAX (CASE WHEN offworkdate = '07' THEN offworktime END) 'down7',
MAX (CAST (datediff(MINUTE,
CAST ((CASE WHEN workingdate = '07' THEN workingtime END) AS datetime),
CAST ((CASE WHEN offworkdate = '07' THEN offworktime END) AS datetime)) / 60 AS VARCHAR) + '小时' + CAST (datediff(MINUTE,
CAST ((CASE WHEN workingdate = '07' THEN workingtime END) AS datetime),
CAST ((CASE WHEN offworkdate = '07' THEN offworktime END) AS datetime)) % 60 AS VARCHAR) + '分') 'count7'FROM(
SELECT a.name,a.id,
SUBSTRING (CONVERT (VARCHAR, a.workingtime, 112),7,2) AS workingdate,
SUBSTRING (CONVERT (VARCHAR, a.workingtime, 108),1,5) AS workingtime,
SUBSTRING (CONVERT (VARCHAR, a.offworktime, 112),7,2) AS offworkdate,
SUBSTRING (CONVERT (VARCHAR, a.offworktime, 108),1,5) AS offworktime FROM(
SELECT emp.m1 AS name,tc.id,MAX (mai.face_time) AS offworktime,MIN (mai.face_time) AS workingtime FROM
model_attendance_info AS mai
JOIN ts_card tc ON tc.id = mai.id1
JOIN model_employee AS emp ON emp.m2 = tc.card_no
WHERE
1 = 1
AND CONVERT (VARCHAR (10),mai.face_time,120) LIKE '2016-12%'
AND emp.m1 = '小明'
GROUP BY emp.m1,tc.id,CONVERT (VARCHAR (10),mai.face_time,120)
) AS a
) AS b GROUP BY b.id, b.name
) AS tt
==============表sql===========
/*
Navicat MySQL Data Transfer
Source Server : MYSQL
Source Server Version : 50630
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 50630
File Encoding : 65001
Date: 2017-08-15 18:09:56
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for model_attendance_info
-- ----------------------------
DROP TABLE IF EXISTS `model_attendance_info`;
CREATE TABLE `model_attendance_info` (
`id` bigint(20) NOT NULL,
`face_time` datetime DEFAULT NULL,
`id1` bigint(20) DEFAULT NULL,
`uid2` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of model_attendance_info
-- ----------------------------
INSERT INTO `model_attendance_info` VALUES ('1', '2016-12-07 07:22:02', '1', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('2', '2016-12-07 07:22:02', '2', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('3', '2016-12-07 08:40:02', '3', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('4', '2016-12-07 08:50:02', '4', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('5', '2016-12-07 08:22:02', '5', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('6', '2016-12-07 08:22:02', '6', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('7', '2016-12-07 18:22:02', '1', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('8', '2016-12-07 18:22:02', '2', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('9', '2016-12-07 18:22:02', '3', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('10', '2016-12-07 18:22:02', '4', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('11', '2016-12-07 18:22:02', '5', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('12', '2016-12-07 18:22:02', '6', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('13', '2016-12-08 08:22:02', '1', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('14', '2016-12-08 08:22:02', '2', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('15', '2016-12-08 08:22:02', '3', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('16', '2016-12-08 08:22:02', '4', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('17', '2016-12-08 08:22:02', '5', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('18', '2016-12-08 08:22:02', '6', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('19', '2016-12-08 18:22:02', '1', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('20', '2016-12-08 18:22:02', '2', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('21', '2016-12-08 17:22:02', '3', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('22', '2016-12-08 17:52:02', '4', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('23', '2016-12-08 18:12:02', '5', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('24', '2016-12-08 18:02:02', '6', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('25', '2016-12-14 10:46:43', '10', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('26', '2016-12-14 10:47:01', '6', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('27', '2016-12-14 10:47:39', '5', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('28', '2016-12-14 10:47:50', '5', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('29', '2016-12-14 10:47:52', '5', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('30', '2016-12-14 10:48:10', '5', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('31', '2016-12-14 10:48:16', '5', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('32', '2016-12-14 10:48:18', '5', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('33', '2016-12-14 10:48:20', '5', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('34', '2016-12-14 10:48:28', '3', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('35', '2016-12-14 10:48:32', '9', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('36', '2016-12-14 10:48:40', '1', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('37', '2016-12-14 10:48:42', '10', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('38', '2016-12-14 10:48:46', '6', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('39', '2016-12-14 10:48:50', '4', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('40', '2016-12-14 10:48:52', '4', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('41', '2016-12-14 10:57:45', '2', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('42', '2016-12-14 18:49:03', '4', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('43', '2016-12-14 18:49:11', '10', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('44', '2016-12-14 18:49:17', '3', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('45', '2016-12-14 18:49:23', '10', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('46', '2016-12-14 18:49:25', '2', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('47', '2016-12-14 18:49:27', '6', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('48', '2016-12-14 18:49:29', '4', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('49', '2016-12-14 18:49:31', '9', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('50', '2016-12-14 18:49:33', '5', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('51', '2016-12-14 18:49:35', '1', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('52', '2016-12-15 09:56:07', '10', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('53', '2016-12-15 09:56:15', '10', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('54', '2016-12-15 09:56:24', '10', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('55', '2016-12-15 09:56:27', '1', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('56', '2016-12-15 09:58:42', '9', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('57', '2016-12-15 09:58:48', '5', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('58', '2016-12-15 09:58:52', '2', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('59', '2016-12-15 09:58:54', '4', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('60', '2016-12-15 09:58:58', '6', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('61', '2016-12-15 09:59:00', '3', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('62', '2016-12-15 09:59:02', '9', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('63', '2016-12-15 09:59:04', '1', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('64', '2016-12-15 09:59:07', '10', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('65', '2016-12-15 10:00:09', '1', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('66', '2016-12-15 17:28:56', '1', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('67', '2016-12-15 17:29:00', '10', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('68', '2016-12-15 17:29:04', '3', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('69', '2016-12-15 17:29:12', '4', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('70', '2016-12-15 17:29:16', '6', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('71', '2016-12-15 17:29:28', '2', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('72', '2016-12-15 17:31:37', '3', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('73', '2016-12-15 17:31:47', '6', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('74', '2016-12-15 17:31:53', '1', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('75', '2016-12-15 17:32:30', '1', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('76', '2016-12-15 17:32:32', '10', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('77', '2016-12-15 17:32:36', '4', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('78', '2016-12-15 17:32:46', '2', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('79', '2016-12-15 17:33:52', '5', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('80', '2016-12-15 17:33:54', '2', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('81', '2016-12-15 17:33:56', '3', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('82', '2016-12-15 17:33:58', '6', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
INSERT INTO `model_attendance_info` VALUES ('83', '2016-12-15 17:34:37', '9', 'abbfaf20-66cc-4c3e-85f9-9680d1e1ebdd');
SET FOREIGN_KEY_CHECKS=1;