mysql查询员工已打卡和未打卡记录
文章描述：
mysql查询员工打开签到记录，把签到结果放到一个字段里面
创建数据表
CREATE TABLE `time_summary` (
`id` int NOT NULL AUTO_INCREMENT,
`emp_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '员工号',
`emp_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '员工姓名',
`time_date` date DEFAULT NULL COMMENT '填报日期',
`finish_flag` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '完成标志：0：未完成，1：已完成',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
插入数据
INSERT INTO `time_summary` (`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES
(1, '1', '张三', '2025-05-01', '1'),
(2, '1', '张三', '2025-05-02', '1'),
(3, '1', '张三', '2025-05-03', '0'),
(4, '1', '张三', '2025-05-04', '1'),
(5, '1', '张三', '2025-05-05', '0'),
(6, '2', '李四', '2025-05-01', '1'),
(7, '2', '李四', '2025-05-02', '0');
查询SQL
SELECT
su.emp_id,
su.emp_name,
a.notFinished,
b.finished
FROM
(
SELECT
t.emp_id,
t.emp_name
FROM
time_summary t
WHERE
t.time_date >= '2025-05-01'
AND t.time_date <= '2025-05-30'
GROUP BY
t.emp_id,
t.emp_name
) su
LEFT JOIN (
SELECT
t.emp_id,
t.emp_name,
GROUP_CONCAT( SUBSTRING( t.time_date, 9, 2 ) SEPARATOR ',' ) AS notFinished
FROM
time_summary t
WHERE
t.time_date >= '2025-05-01'
AND t.time_date <= '2025-05-30'
AND t.finish_flag = '0'
GROUP BY
t.emp_id,
t.emp_name
) a ON su.emp_id = a.emp_id
LEFT JOIN (
SELECT
t.emp_id,
t.emp_name,
GROUP_CONCAT( SUBSTRING( t.time_date, 9, 2 ) SEPARATOR ',' ) AS finished
FROM
time_summary t
WHERE
t.time_date >= '2025-05-01'
AND t.time_date <= '2025-05-30'
AND t.finish_flag = '1'
GROUP BY
t.emp_id,
t.emp_name
) b ON su.emp_id = b.emp_id
结果：
发布时间：2025/05/18
发表评论