数据表多行数据转成多列数据

===============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;