mysql - MySQL 从行值生成列

Question

我想根据每一天作为列生成每月出勤的查询结果。这是我的出勤表：

+----+-------------+------------+-----------------+-----------------+--------+
| id | employee_id | date       | clock_in        | clock_out       | detail |
+----+-------------+------------+-----------------+-----------------+--------+
|  1 |           1 | 2021-03-10 | 07:24:24.000000 | 12:55:10.000000 | NULL   |
|  2 |           1 | 2021-03-11 | 07:00:24.000000 | 14:00:25.000000 | NULL   |
|  3 |           1 | 2021-03-12 | 16:57:25.000000 | 16:57:30.000000 | NULL   |
|  4 |           1 | 2021-03-13 | NULL            | NULL            | OFF    |
|  5 |           1 | 2021-03-14 | NULL            | NULL            | OFF    |
+----+-------------+------------+-----------------+-----------------+--------+

我想要的结果是这样的：

employee_id | 10-03-2021          | 11-03-2021          | 12-03-2021          | 13-03-2021 | 14-03-2021 
------------+---------------------+---------------------+---------------------+------------+-------------
1           | 07:24:24 - 12:55:10 | 07:00:24 - 14:00:25 | 16:57:25 - 16:57:30 | OFF        | OFF        

我正在尝试将此 MySQL 数据透视行中的答案跟踪到动态列数中，但我仍然无法正确生成结果。这是我的查询语句。

SET @SQL = NULL;
SELECT
    GROUP_CONCAT( CONCAT( '(case when  date_format(date, ''%d-%m-%y'') = ''', date_format( date, '%d-%m-%y' ), ''' then concat(clock_in, '' - '', clock_out) else detail end) 
    AS `', date_format( date, '%d-%m-%y' ), '`' ) ) INTO @SQL 
FROM
    attendance;

SET @SQL = CONCAT( 'SELECT employee_id, ', @SQL, ' FROM attendance
GROUP BY employee_id' );
PREPARE stmt 
FROM
@SQL;
EXECUTE stmt;

这是我的表结构：

CREATE TABLE `attendance` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `clock_in` time(6) DEFAULT NULL,
  `clock_out` time(6) DEFAULT NULL,
  `detail` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into attendance (employee_id, date, clock_in, clock_out, detail) values (1, '2021-03-10', '07:24:24', '12:55:10', null);
insert into attendance (employee_id, date, clock_in, clock_out, detail) values (1, '2021-03-11', '07:00:24', '14:00:25', null);
insert into attendance (employee_id, date, clock_in, clock_out, detail) values (1, '2021-03-12', '16:57:25', '16:57:30', null);
insert into attendance (employee_id, date, clock_in, clock_out, detail) values (1, '2021-03-13', null, null, 'OFF');
insert into attendance (employee_id, date, clock_in, clock_out, detail) values (1, '2021-03-14', null, null, 'OFF');

任何想法将不胜感激。谢谢你。

标签： mysql