SQL语句和Excel在本科教学状态数据库填报中的应用

何光军+陈戈

摘要：医学院校的教学任务往往由多位授课教师共同承担，学校教务处在填报本科教学状态基本数据库的开课情况统计表时，面临着工作量大、工作复杂的问题。本文针对原始教学任务授课教师的数据库关系表和Excel表格两种不同的存储方式，提出了两种不同的解决方案，实现了数据的批量、程序化处理，有效地提高了工作效率。

关键词：本科教学状态数据库；开课情况表；多位授课教师；合并

中图分类号：G649； TP392 文献标识码：A 论文编号：1674-2117（2017）12-0065-04

● 引言

2009年，为进行本科教育质量常态监测，教育部高等教育教学评估中心建立了高等教育质量监测国家数据平台，行业内简称“本科教学状态基本数据库”。该平台的建立是推动高等教育内涵式发展，提高高等学校人才培养质量的重要举措，是实施高等学校教学质量常态监测的重要内容，是建立五位一体中国特色、世界水平高等教育质量保障体系的重要工作。[1，2]因此，各所高校非常重视该平台数据的填报工作。

填报本科教学状态基本数据库是一个复杂、系统的工程[3]，基本在每年的下半年集中进行，工作量大，时间紧。根据本科状态数据库的人才培养大类的设置，要求各所高校填报上一学年的开课情况，其表格要求如下表所示。

在填写指标解释中，其中授课教师一栏要求“填写担任课程讲授任务的授课教师，同一门次课程有多位授课教师的可多填，不同教师间用英文分号隔开”。[5]也就是说多位授课教师的填写格式为“教师姓名A；教师姓名B；教师姓名C；教师姓名D”，对应的授课教师工号为“工号A；工号B；工号C；工号D”，授课教师姓名与工号必须一一对应。

因医学本科院校教学、课程的特殊性，一门课程基本都是由多位授课教师共同承担，少则2～3位，多则10多位教师参与，这就给上表的数据填报工作带来了极大的困难。

一方面，如果手工填写每个教学任务的“授课教师”和“授课教师工号”，面对几千门次开课的任务，不但工作量大，而且容易出错。

另一方面，在现代教学信息化时代，教学数据往往存放在数据库或者Excel表格中，因此，充分应用信息化、智能化手段处理日常教学管理工作是非常重要的。在数据库的关系表和Excel表中，同一个任务的课程任务号是一致的。课程任务下面对应的多位教师往往是以多行的形式存储，其任务号是唯一标识，如图1所示。

图1中的数据是本文的实验数据，其中XKKH为任务号，JSZGH为教师职工号，JSXM为教师姓名。因此，笔者以重庆医科大学的教学数据为例，将针对不同的存储方式介绍两种不同的方法获取符合上页表格要求的教师信息。针对数据库的存储，将充分应用数据库的分组与合并函数、Excel的通配符替换功能获取教学任务的教师信息；针对Excel的存储方式，将利用Excel的分类汇总和高级筛选、填充功能处理承担教学任务的教师信息。这样就可以使我们的工作智能化、高效化，同时避免出错。

● 将数据库关系表中存放的多位教师信息转为一行

Oracle 10g以上版本提供“行转列组合成字符串的函数”——wmsys.wm_concat（列名），该函数的功能是实现字段合并，把列值以“，”号分隔开并显示成一行，最终实现行转列的效果。[6，7]

1.一种错误的分组与合并方法

在介绍正确的方法之前，先认识一下一种容易出现的错误分组与合并方法。

利用Oracle数据库的“函数wm_concat”将行数据转为列数据，在PL/SQL程序的SQL窗口执行如下SQL语句：

select distinct xkkh， wm_concat （jszgh） zgh， wm_concat （jsxm） jsxm from dgjsskxxb1 group by xkkh order by xkkh；

结果如图2所示，图2的数据从表面上看，似乎正确，也符合本科状态数据库表中授课教师与工号的格式要求。然而，仔细观察会发现，如果一个任务（xkkh）对应多位授课教师，ZGH列的工号与JSXM列的姓名没有一一对应，不满足要求。例如从图1得知教师白燕的工号为10003，而图2中任务号（2015-2016-2）-041011032-1对应的合并教师信息中，工号10003对应的教师姓名却是“张良”。

由此可见，在一个SQL语句中如果对多个不同的列使用“wm_concat函数”，列与列之间原有的对应关系与新列中的合并数据并不会一一对应。

2.数据库中授课教师信息的正確合并方法

（1）既然在一个SQL语句中不能对多个列同时使用“wm_concat函数”，那么可以先进行合并，将多个列合并为一列，这样就能保证“工号”和“姓名”一一对应。

为了后期便于分离工号和姓名，用括号、方括号分别对工号和姓名进行包含标识，合并语句如下：

select xkkh， '（'||jszgh||'）'||'【'||jsxm||'】' jsxx from dgjsskxxb1

合并结果如上页图3所示。

（2）使用“函数wm_concat”将行数据转为列数据，SQL语句如下：

select distinct xkkh， wm_concat（jsxx） from dgjsskxxb1 group by xkkh order by xkkh；

结果如上页图4所示。

（3）将数据导出到tsv文件，将tsv文件复制到单元格为文本格式的Excel中，并复制JSXX到C列。接着，将B列的数据进行下面一系列顺序处理，即可得到授课教师工号合并的信息。

①将“，”替换为“；”；

②将“（”替换为空字符；

③将“）”替换为空字符；

④使用Excel的通配符替换功能，将“【？？】”替换为空字符（如上页图5），进而得到如图6所示的结果。

用同样的方法对C列的数据进行处理，得到授课教师姓名合并的信息（如图7）。

①将“，”替换为“；”；

②将“【”替换为空字符；

③将“】”替换为空字符；

④将“（？？？？？）”替换为空字符。

图7的数据完全符合本科状态数据库表中授课教师与工号的格式及一一对应的要求。

同时，在Excel的通配符替换功能中，可根据姓名、工号字符数，增减通配符号“？”的个数。

● 将Excel表中存放的多位教师信息转为一行

上面介绍的方法适合对数据库和SQL语句熟悉的教学管理人员使用，下面笔者介绍用Excel“分类汇总”及“合并同类项”的方法，如何将Excel表中相同任务号的多位教师信息合并到一起，并用“；”隔开。

（1）对图1中的数据按A列XKKH进行升序排序。用“高级筛选”功能获取A列数据的非重复项[8]，并复制到其他Sheet中保存，如图8和图9所示。

（2）对图1的数据进行预处理，利用公式“=B2&"；"”和“自动填充”在B列JSZGH后面加上分号“；”；同理，利用公式“=C2&"；"”和“自动填充”在C列JSXM后面加上分号“；”。

（3）进行分类汇总，汇总项只选择XKKH（如图10），接着通过分类汇总把不同任务号的教师信息用空行隔开，如上页图11所示。

（4）选中列C2∶C13的数据，将此列宽度调大，然后“点击‘开始选项卡→选择‘填充→点击‘两端对齐”。同理，选中列D2∶D13的数据，将此列宽度调大，然后点击“‘开始选项卡→点击‘填充→选择‘两端对齐”，得到如图12的结果。操作原理是：“两端对齐”填充功能以空行为分隔界限，分别把以空行隔开的多个单元格区域合并为一个单元格。[9]

（5）选中C列按快捷键F5定位，定位条件选择“空值”，右键删除整行，再用公式“=MID（C2，1，LEN（C2）-1）”“=MID（D2，1，LEN（C2）-1）”和“自动填充”功能将JSZGH和JSXM后面的“；”删除。注意，在JSZGH中分号后面还有一个空格，则用替换的功能将其删除。结果如图13所示。

（6）将图13与图9的数据合并，最终得到的数据与图7的数据一致。

● 总结

笔者针对本科教学状态数据库的开课情况表的填报，介绍了两种方法，实现了对授课教师信息的批量处理和获取，进而提高了工作效率，减少了工作失误。此外，文中处理Excel表格数据的各种方法也可以应用于教学管理工作的各个方面。

参考文献：

[1][4][5]高等教育质量监测国家数据平台用户操作指南V3.0[EB/OL].http：//udb.heec.edu.cn/passport/portal/index.html.2016-10-14.

[2]高等教育质量监测国家数据平台数据表格及内涵说明V3.0[EB/OL].http：//udb.heec.edu.cn/passport/portal/index.html.2016-10-14.

[3]朱雪莲.浅析教学基本状态数据库建设和填报工作的重要性[J].中国管理信息化，2016，19（22）：198-199.

[6]wm_concat函数[EB/OL]. http：//www.360doc.com/content/12/0303/22/8101845_191469228.shtml.2016-12-14.

[7]孟德欣.Oracle 10g数据库技术[M].北京：清华大学出版社，2010：78-99.

[8]钱建军.Excel从入门到精通[M].北京：清华大学出版社，2014：135-136.

[9]快速合并相同内容的单元格[EB/OL].http：//www.vccoo.com/v/025576.2016-12-11.

作者简介：何光军（1986.11—），助理研究员，硕士研究生，主要研究方向为教学技术、教育信息化；陈戈（1980.8—），通讯作者，硕士研究生，主要研究方向為教育信息化、高等教育研究。