SQL語句和Excel在本科教學(xué)狀態(tài)數(shù)據(jù)庫填報中的應(yīng)用

何光軍+陳戈

摘要：醫(yī)學(xué)院校的教學(xué)任務(wù)往往由多位授課教師共同承擔(dān)，學(xué)校教務(wù)處在填報本科教學(xué)狀態(tài)基本數(shù)據(jù)庫的開課情況統(tǒng)計表時，面臨著工作量大、工作復(fù)雜的問題。本文針對原始教學(xué)任務(wù)授課教師的數(shù)據(jù)庫關(guān)系表和Excel表格兩種不同的存儲方式，提出了兩種不同的解決方案，實(shí)現(xiàn)了數(shù)據(jù)的批量、程序化處理，有效地提高了工作效率。

關(guān)鍵詞：本科教學(xué)狀態(tài)數(shù)據(jù)庫；開課情況表；多位授課教師；合并

中圖分類號：G649； TP392 文獻(xiàn)標(biāo)識碼：A 論文編號：1674-2117（2017）12-0065-04

● 引言

2009年，為進(jìn)行本科教育質(zhì)量常態(tài)監(jiān)測，教育部高等教育教學(xué)評估中心建立了高等教育質(zhì)量監(jiān)測國家數(shù)據(jù)平臺，行業(yè)內(nèi)簡稱“本科教學(xué)狀態(tài)基本數(shù)據(jù)庫”。該平臺的建立是推動高等教育內(nèi)涵式發(fā)展，提高高等學(xué)校人才培養(yǎng)質(zhì)量的重要舉措，是實(shí)施高等學(xué)校教學(xué)質(zhì)量常態(tài)監(jiān)測的重要內(nèi)容，是建立五位一體中國特色、世界水平高等教育質(zhì)量保障體系的重要工作。[1，2]因此，各所高校非常重視該平臺數(shù)據(jù)的填報工作。

填報本科教學(xué)狀態(tài)基本數(shù)據(jù)庫是一個復(fù)雜、系統(tǒng)的工程[3]，基本在每年的下半年集中進(jìn)行，工作量大，時間緊。根據(jù)本科狀態(tài)數(shù)據(jù)庫的人才培養(yǎng)大類的設(shè)置，要求各所高校填報上一學(xué)年的開課情況，其表格要求如下表所示。

在填寫指標(biāo)解釋中，其中授課教師一欄要求“填寫擔(dān)任課程講授任務(wù)的授課教師，同一門次課程有多位授課教師的可多填，不同教師間用英文分號隔開”。[5]也就是說多位授課教師的填寫格式為“教師姓名A；教師姓名B；教師姓名C；教師姓名D”，對應(yīng)的授課教師工號為“工號A；工號B；工號C；工號D”，授課教師姓名與工號必須一一對應(yīng)。

因醫(yī)學(xué)本科院校教學(xué)、課程的特殊性，一門課程基本都是由多位授課教師共同承擔(dān)，少則2～3位，多則10多位教師參與，這就給上表的數(shù)據(jù)填報工作帶來了極大的困難。

一方面，如果手工填寫每個教學(xué)任務(wù)的“授課教師”和“授課教師工號”，面對幾千門次開課的任務(wù)，不但工作量大，而且容易出錯。

另一方面，在現(xiàn)代教學(xué)信息化時代，教學(xué)數(shù)據(jù)往往存放在數(shù)據(jù)庫或者Excel表格中，因此，充分應(yīng)用信息化、智能化手段處理日常教學(xué)管理工作是非常重要的。在數(shù)據(jù)庫的關(guān)系表和Excel表中，同一個任務(wù)的課程任務(wù)號是一致的。課程任務(wù)下面對應(yīng)的多位教師往往是以多行的形式存儲，其任務(wù)號是唯一標(biāo)識，如圖1所示。

圖1中的數(shù)據(jù)是本文的實(shí)驗數(shù)據(jù)，其中XKKH為任務(wù)號，JSZGH為教師職工號，JSXM為教師姓名。因此，筆者以重慶醫(yī)科大學(xué)的教學(xué)數(shù)據(jù)為例，將針對不同的存儲方式介紹兩種不同的方法獲取符合上頁表格要求的教師信息。針對數(shù)據(jù)庫的存儲，將充分應(yīng)用數(shù)據(jù)庫的分組與合并函數(shù)、Excel的通配符替換功能獲取教學(xué)任務(wù)的教師信息；針對Excel的存儲方式，將利用Excel的分類匯總和高級篩選、填充功能處理承擔(dān)教學(xué)任務(wù)的教師信息。這樣就可以使我們的工作智能化、高效化，同時避免出錯。

● 將數(shù)據(jù)庫關(guān)系表中存放的多位教師信息轉(zhuǎn)為一行

Oracle 10g以上版本提供“行轉(zhuǎn)列組合成字符串的函數(shù)”——wmsys.wm_concat（列名），該函數(shù)的功能是實(shí)現(xiàn)字段合并，把列值以“，”號分隔開并顯示成一行，最終實(shí)現(xiàn)行轉(zhuǎn)列的效果。[6，7]

1.一種錯誤的分組與合并方法

在介紹正確的方法之前，先認(rèn)識一下一種容易出現(xiàn)的錯誤分組與合并方法。

利用Oracle數(shù)據(jù)庫的“函數(shù)wm_concat”將行數(shù)據(jù)轉(zhuǎn)為列數(shù)據(jù)，在PL/SQL程序的SQL窗口執(zhí)行如下SQL語句：

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

結(jié)果如圖2所示，圖2的數(shù)據(jù)從表面上看，似乎正確，也符合本科狀態(tài)數(shù)據(jù)庫表中授課教師與工號的格式要求。然而，仔細(xì)觀察會發(fā)現(xiàn)，如果一個任務(wù)（xkkh）對應(yīng)多位授課教師，ZGH列的工號與JSXM列的姓名沒有一一對應(yīng)，不滿足要求。例如從圖1得知教師白燕的工號為10003，而圖2中任務(wù)號（2015-2016-2）-041011032-1對應(yīng)的合并教師信息中，工號10003對應(yīng)的教師姓名卻是“張良”。

由此可見，在一個SQL語句中如果對多個不同的列使用“wm_concat函數(shù)”，列與列之間原有的對應(yīng)關(guān)系與新列中的合并數(shù)據(jù)并不會一一對應(yīng)。

2.數(shù)據(jù)庫中授課教師信息的正確合并方法

（1）既然在一個SQL語句中不能對多個列同時使用“wm_concat函數(shù)”，那么可以先進(jìn)行合并，將多個列合并為一列，這樣就能保證“工號”和“姓名”一一對應(yīng)。

為了后期便于分離工號和姓名，用括號、方括號分別對工號和姓名進(jìn)行包含標(biāo)識，合并語句如下：

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

合并結(jié)果如上頁圖3所示。

（2）使用“函數(shù)wm_concat”將行數(shù)據(jù)轉(zhuǎn)為列數(shù)據(jù)，SQL語句如下：

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

結(jié)果如上頁圖4所示。

（3）將數(shù)據(jù)導(dǎo)出到tsv文件，將tsv文件復(fù)制到單元格為文本格式的Excel中，并復(fù)制JSXX到C列。接著，將B列的數(shù)據(jù)進(jìn)行下面一系列順序處理，即可得到授課教師工號合并的信息。

①將“，”替換為“；”；

②將“（”替換為空字符；

③將“）”替換為空字符；

④使用Excel的通配符替換功能，將“【？？】”替換為空字符（如上頁圖5），進(jìn)而得到如圖6所示的結(jié)果。

用同樣的方法對C列的數(shù)據(jù)進(jìn)行處理，得到授課教師姓名合并的信息（如圖7）。

①將“，”替換為“；”；

②將“【”替換為空字符；

③將“】”替換為空字符；

④將“（？？？？？）”替換為空字符。

圖7的數(shù)據(jù)完全符合本科狀態(tài)數(shù)據(jù)庫表中授課教師與工號的格式及一一對應(yīng)的要求。

同時，在Excel的通配符替換功能中，可根據(jù)姓名、工號字符數(shù)，增減通配符號“？”的個數(shù)。

● 將Excel表中存放的多位教師信息轉(zhuǎn)為一行

上面介紹的方法適合對數(shù)據(jù)庫和SQL語句熟悉的教學(xué)管理人員使用，下面筆者介紹用Excel“分類匯總”及“合并同類項”的方法，如何將Excel表中相同任務(wù)號的多位教師信息合并到一起，并用“；”隔開。

（1）對圖1中的數(shù)據(jù)按A列XKKH進(jìn)行升序排序。用“高級篩選”功能獲取A列數(shù)據(jù)的非重復(fù)項[8]，并復(fù)制到其他Sheet中保存，如圖8和圖9所示。

（2）對圖1的數(shù)據(jù)進(jìn)行預(yù)處理，利用公式“=B2&"；"”和“自動填充”在B列JSZGH后面加上分號“；”；同理，利用公式“=C2&"；"”和“自動填充”在C列JSXM后面加上分號“；”。

（3）進(jìn)行分類匯總，匯總項只選擇XKKH（如圖10），接著通過分類匯總把不同任務(wù)號的教師信息用空行隔開，如上頁圖11所示。

（4）選中列C2∶C13的數(shù)據(jù)，將此列寬度調(diào)大，然后“點(diǎn)擊‘開始選項卡→選擇‘填充→點(diǎn)擊‘兩端對齊”。同理，選中列D2∶D13的數(shù)據(jù)，將此列寬度調(diào)大，然后點(diǎn)擊“‘開始選項卡→點(diǎn)擊‘填充→選擇‘兩端對齊”，得到如圖12的結(jié)果。操作原理是：“兩端對齊”填充功能以空行為分隔界限，分別把以空行隔開的多個單元格區(qū)域合并為一個單元格。[9]

（5）選中C列按快捷鍵F5定位，定位條件選擇“空值”，右鍵刪除整行，再用公式“=MID（C2，1，LEN（C2）-1）”“=MID（D2，1，LEN（C2）-1）”和“自動填充”功能將JSZGH和JSXM后面的“；”刪除。注意，在JSZGH中分號后面還有一個空格，則用替換的功能將其刪除。結(jié)果如圖13所示。

（6）將圖13與圖9的數(shù)據(jù)合并，最終得到的數(shù)據(jù)與圖7的數(shù)據(jù)一致。

● 總結(jié)

筆者針對本科教學(xué)狀態(tài)數(shù)據(jù)庫的開課情況表的填報，介紹了兩種方法，實(shí)現(xiàn)了對授課教師信息的批量處理和獲取，進(jìn)而提高了工作效率，減少了工作失誤。此外，文中處理Excel表格數(shù)據(jù)的各種方法也可以應(yīng)用于教學(xué)管理工作的各個方面。

參考文獻(xiàn)：

[1][4][5]高等教育質(zhì)量監(jiān)測國家數(shù)據(jù)平臺用戶操作指南V3.0[EB/OL].http：//udb.heec.edu.cn/passport/portal/index.html.2016-10-14.

[2]高等教育質(zhì)量監(jiān)測國家數(shù)據(jù)平臺數(shù)據(jù)表格及內(nèi)涵說明V3.0[EB/OL].http：//udb.heec.edu.cn/passport/portal/index.html.2016-10-14.

[3]朱雪蓮.淺析教學(xué)基本狀態(tài)數(shù)據(jù)庫建設(shè)和填報工作的重要性[J].中國管理信息化，2016，19（22）：198-199.

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

[7]孟德欣.Oracle 10g數(shù)據(jù)庫技術(shù)[M].北京：清華大學(xué)出版社，2010：78-99.

[8]錢建軍.Excel從入門到精通[M].北京：清華大學(xué)出版社，2014：135-136.

[9]快速合并相同內(nèi)容的單元格[EB/OL].http：//www.vccoo.com/v/025576.2016-12-11.

作者簡介：何光軍（1986.11—），助理研究員，碩士研究生，主要研究方向為教學(xué)技術(shù)、教育信息化；陳戈（1980.8—），通訊作者，碩士研究生，主要研究方向為教育信息化、高等教育研究。