MySQL中将多行查询结果合并为一行展示SQL语句书写 |
您所在的位置：网站首页 › mysql查询结果多加一列 › MySQL中将多行查询结果合并为一行展示SQL语句书写 |
|
写在前面 最近开发过程中，遇到一个需求是要将所查询的多条结果汇总成一条结果展示，由于之前没有接触过这方面的业务，所以经过一番折腾之后，解决了需求，这里特此记录一下，以供后续参考！ 1、问题复现这里以一个例子进行说明： 需求：一个员工每月是否完成了打卡，要求统计员工当月完成和未完成日期，展示结果如下： 测试的数据库表字段如下： 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, '100', '张三', '2020-06-24', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (2, '100', '张三', '2020-06-23', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (3, '100', '张三', '2020-06-22', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (4, '100', '张三', '2020-06-19', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (5, '100', '张三', '2020-06-18', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (6, '100', '张三', '2020-06-17', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (7, '100', '张三', '2020-06-16', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (8, '100', '张三', '2020-06-15', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (9, '100', '张三', '2020-06-12', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (10, '100', '张三', '2020-06-11', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (11, '100', '张三', '2020-06-10', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (12, '100', '张三', '2020-06-09', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (13, '100', '张三', '2020-06-08', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (14, '100', '张三', '2020-06-05', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (15, '100', '张三', '2020-06-04', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (16, '100', '张三', '2020-06-03', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (17, '100', '张三', '2020-06-02', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (18, '100', '张三', '2020-06-01', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (19, '101', '李四', '2020-06-24', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (20, '101', '李四', '2020-06-23', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (21, '101', '李四', '2020-06-22', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (22, '101', '李四', '2020-06-19', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (23, '101', '李四', '2020-06-18', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (24, '101', '李四', '2020-06-17', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (25, '101', '李四', '2020-06-16', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (26, '101', '李四', '2020-06-15', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (27, '101', '李四', '2020-06-12', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (28, '101', '李四', '2020-06-11', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (29, '101', '李四', '2020-06-10', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (30, '101', '李四', '2020-06-09', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (31, '101', '李四', '2020-06-08', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (32, '101', '李四', '2020-06-05', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (33, '101', '李四', '2020-06-04', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (34, '101', '李四', '2020-06-03', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (35, '101', '李四', '2020-06-02', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (36, '101', '李四', '2020-06-01', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (37, '102', '王五', '2020-06-24', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (38, '102', '王五', '2020-06-23', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (39, '102', '王五', '2020-06-22', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (40, '102', '王五', '2020-06-19', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (41, '102', '王五', '2020-06-18', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (42, '102', '王五', '2020-06-17', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (43, '102', '王五', '2020-06-16', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (44, '102', '王五', '2020-06-15', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (45, '102', '王五', '2020-06-12', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (46, '102', '王五', '2020-06-11', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (47, '102', '王五', '2020-06-10', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (48, '102', '王五', '2020-06-09', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (49, '102', '王五', '2020-06-08', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (50, '102', '王五', '2020-06-05', '1'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (51, '102', '王五', '2020-06-04', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (52, '102', '王五', '2020-06-03', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (53, '102', '王五', '2020-06-02', '0'); INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (54, '102', '王五', '2020-06-01', '0');View Code 这种情况下，我们一般可以将所有的情况查询出来(这里以6月份数据为例)，查询SQL如下： SELECT t.emp_id,t.emp_name,t.time_date,t.finish_flag from time_summary t where t.time_date >= '2020-06-01' and time_date = '2020-06-01' AND t.time_date = '2020-06-01' AND t.time_date = '2020-06-01' AND t.time_date |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |