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—），通訊作者，碩士研究生，主要研究方向為教育信息化、高等教育研究。