用SQL解决复杂Excel问题

2015-03-17湖北雷应兵

网络安全和信息化 2015年11期

■ 湖北雷应兵

故障现象

前一段时间，笔者接到一个非常棘手的问题：某部门有29111笔共796余万元的款项，需要通过银行代发到用户的存折上，该部门将需要发放的Excel表拷贝给代发银行后，由于银行具体操作人员经验不足，将电子表格顺序和部分内容进行了更改。

仔细对比分析两个表后，发现以下情况：1.该部门提供的原表（以下简称“原表”）是29111笔7964331.30元，银行返回来的表（以下简称“银行返表”）显示应代发金额是7964331.11元，代发成功金额是6288496.75元，说明代发银行改动了原始数据，不然应代发金额不会少0.19元。2.银行返表的记录顺序已全部打乱，且删除了备注中注明的分组情况，导致无法统计到底是哪个部门哪些人代发没有成功，哪个部门哪些人代发成功了。3.原表中，有很多相同记录，即银行账号、姓名、金额、备注都相同，即表的记录都有可能不是惟一。

现在提出的问题就是，在记录不能保证惟一的情况下，如何做到原表与银行返表的记录一一对应。只有对应成功后，才能可知道银行代发成功的数据是否都正确，还有哪个组的哪些人员为什么没有代发成功。

故障解决过程

如此复杂的问题，要想在Excel里面解决，将非常困难。笔者试图通过SQL解决以上问题。

第一步，在Excel表最前加一ID列，然后用1、2、3……等惟一数据填充，这样做的目的是为了将每条记录惟一化，便于后面进行数据处理。把修改后的两表导入Excel数据库ZH中，改名为DK-YB（原表）和DK-YH（银行返表）（如图 1）。

第二步，给DK-YB加上两列“YDID”和“成功”。

第三步，根据已知条件，假设我们在DK-YB中选中一条记录，如DY-YB.ID=1，那么可以在DK-YH表中找到姓名、账号和应发金额一一对应的记录，但由于有重记录的存在，需要通过SELECT TOP 1指定惟一的一条记录来与之对应。当两个表中都只有惟一记录在姓名、账号和应发金额三项上都一一对应后，我们就可以根据第一步中设置的惟一ID来把两个表中的数据进行关联，从而达到解决问题的目的。

基于以上分析，写出如图2所示的代码，通过查询分析器执行。

第四步，执行完以上语句，DK-YH表变成了银行对原表进行了改动后的数据，我们执行SELECT * FROM [dkyh] WHERE (成功 = N'o.k.')可以知道银行代发成功，但与原始数据有差别的38（通过语句SELECT COUNT(*)AS 成功条数 FROM [dk-yh]WHERE (成功 = N'o.k.')查询得到）条记录。

第五步，再看DKYB，只要是成功了的，“YDID”和“成功”列就标注上了银行返表的记录号ID和O.K.信息；没有成功的，“YDID”和“成功”列就标注上了银行返表的记录号ID和银行提示的未成功原因；银行对原始数据进行了更改的，“YDID”和“成功”列则都为NULL，通过查询可知共有3774条。

第六步，将第四步的38条记录，通过与第五步“YDID”和“成功”列都为NULL的3774条进行比对，找出这38条出错的原因，剩下的3736条也是打卡没有成功的。

第七步，根据DK-YB表，查询出符合我们要求的各种数据，至此，问题解决。