当前位置：首页 > news >正文

Excel表格文件分组归并——通过sql

news 2025/4/18 22:00:20

将 Excel 表转换为 SQL 数据库并直接执行 SQL 查询以获得所需的输出。以下是使用 SQL 实现此目的的步骤：

第 1 步：将 Excel 数据导入 MySQL

假设您设置了 MySQL 数据库，则需要先将 Excel 数据导入到表中。您可以使用语句或工具（如 MySQL Workbench、phpMyAdmin 或 Python 库）来加载数据。首先创建数据表。

CREATE TABLE `lab_reports` (`id` INT NOT NULL AUTO_INCREMENT COMMENT '自增主键',`课程编号` VARCHAR(20) NOT NULL COMMENT '课程唯一编号',`课程名称` VARCHAR(100) NOT NULL COMMENT '课程完整名称',`开课单位` VARCHAR(50) NOT NULL COMMENT '开课院系或部门',`教师账号` VARCHAR(30) NOT NULL COMMENT '教师登录账号',`授课教师` VARCHAR(50) NOT NULL COMMENT '教师姓名',`上机对象` VARCHAR(100) DEFAULT NULL COMMENT '实验面向的学生群体',`学生人数` INT UNSIGNED DEFAULT 0 COMMENT '参与学生人数',`实验项目编号` VARCHAR(20) NOT NULL COMMENT '实验项目唯一编号',`实验项目名称` VARCHAR(100) NOT NULL COMMENT '实验项目完整名称',`计划学时` DECIMAL(5,1) UNSIGNED DEFAULT 0.0 COMMENT '计划实验学时',`实验类型` ENUM('基础', '专业', '综合', '创新') DEFAULT '基础' COMMENT '实验类型分类',`状态` ENUM('未开始', '进行中', '已结束', '已取消') DEFAULT '未开始' COMMENT '实验状态',`提交开始时间` DATETIME DEFAULT NULL COMMENT '实验报告提交开始时间',`提交截止时间` DATETIME DEFAULT NULL COMMENT '实验报告提交截止时间',`实验方式` ENUM('线下', '线上', '混合') DEFAULT '线下' COMMENT '实验进行方式',`提交进度` DECIMAL(5,2) UNSIGNED DEFAULT 0.00 COMMENT '提交百分比(0-100)',`批阅进度` DECIMAL(5,2) UNSIGNED DEFAULT 0.00 COMMENT '批阅百分比(0-100)',`所属专业` VARCHAR(50) NOT NULL COMMENT '课程所属专业',`是否独立设实验课` TINYINT(1) DEFAULT 0 COMMENT '是否独立实验课(0否1是)',`批阅超时数量` INT UNSIGNED DEFAULT 0 COMMENT '超时未批阅的报告数量',PRIMARY KEY (`id`),UNIQUE KEY `uk_course_experiment` (`课程编号`, `实验项目编号`),KEY `idx_teacher` (`教师账号`),KEY `idx_department` (`开课单位`),KEY `idx_major` (`所属专业`),KEY `idx_status` (`状态`),KEY `idx_submit_time` (`提交截止时间`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='实验报告管理表';

LOAD DATApandasSQLAlchemy

以下是如何使用 MySQL Workbench 导入数据的示例：

将 Excel 文件转换为 CSV 格式。
使用命令将 CSV 文件加载到表中。LOAD DATA INFILE

或者直接使用navicat工具导入

LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE lab_reports
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- Skips the header row

第 2 步：用于对教师姓名进行分组和组合的 SQL 查询

将数据加载到表中后，您可以使用以下 SQL 查询按课程和实验 ID 对数据进行分组，并根据“提交开始时间”（提交开始时间）是否为 null 来分隔讲师。

SELECT `所属专业`,`课程编号` AS course_id,`课程名称` AS course_name,`实验项目编号` AS experiment_id,`实验项目名称` AS experiment_name,GROUP_CONCAT(DISTINCT CASE WHEN `提交开始时间` IS NOT NULL THEN `授课教师` ELSE NULL END SEPARATOR '、') AS `有提交开始时间的讲师`,GROUP_CONCAT(DISTINCT CASE WHEN `提交开始时间` IS NULL THEN `授课教师` ELSE NULL END SEPARATOR '、') AS `无提交开始时间的讲师`
FROM `lab_reports`
GROUP BY `所属专业`, `课程编号`, `课程名称`, `实验项目编号`, `实验项目名称`
ORDER BY`所属专业`, `课程编号`, `实验项目编号`;

说明：

列顺序：完全按照您要求的顺序输出
- 所属专业
- 课程编号 (别名为course_id)
- 课程名称 (别名为course_name)
- 实验项目编号 (别名为experiment_id)
- 实验项目名称 (别名为experiment_name)
- 有提交开始时间的讲师
- 无提交开始时间的讲师
GROUP_CONCAT改进：
- 添加了DISTINCT关键字确保讲师姓名不重复
- 明确指定了SEPARATOR ', '使结果更清晰
- 使用CASE WHEN...ELSE NULL END确保逻辑清晰
分组和排序：
- 按照所属专业、课程编号和实验项目编号分组
- 结果按所属专业、课程编号和实验项目编号排序
表名：假设数据存储在名为lab_reports的表中