如何快速对比两张Excel报表

背景

最近由于工作原因，要切换系统，新老系统都会产生excel报表，上线前需要验证新老系统产生的报表数据的一致性，作为测试的一部分。理论上，是要求新老系统新生成的报表要一模一样，要是不一致的地方，也需要标注出合理的解释。

本文地址：https://www.cnblogs.com/hchengmx/p/17609130.html

难点

1. 报表多、数据多、报表中的字段多；
2. 要对比多天的数据，会有很多字段上的不一致，而这些差异又是合理的，要是每天都标注的重复的记录的话，会比较耗费精力；
3. 新老系统是有可能的计算方式，是有可能有不一致的地方（备注；
4. 展示方式上，有没有更好的方式？
5. 要对比多天的数据，同一天的数据也有可能会多次生成，每次需要快速对比，并且得到所有不一致的字段

要求

1. 体现数据量差异；
2. 能快速捞出来所有不一致的字段；
3. 能根据已保存的错误信息，快速更新已知的原因；

方法

最开始想到的是用vlookup，通过用唯一的key把一张表的数据捞到另外一张表格里面，再利用IF条件筛选出不一致的字段，要是有合理的解释再备注上去。

这样的缺点在于：

1. vlookup可能会需要多个字段关联起来才能确定唯一的值，需要把最开始的报表添加上额外的字段；
2. 会漏掉一些数据量不一致的，分三种情况，1. 老系统有，新系统没有；2. 老系统没有，新系统有；3. 同样的数据，老系统2条记录，新系统1条记录；
3. 会添加很多列，包括另外一张表的字段，整个对比表格就会很大，不直观；

目前采用的方式：

1. 通过把两张excel导入数据库中两张表里面；

这一步可以使用把excel导入数据库的功能，把两张excel表格粘贴进数据库中，e.g. table_prd, table_test

navicat, dbeaver等主流数据库操作软件都有类似的功能。

2. 捞出来条数不一致的数据；

# 多的
select * from 报告_test test left join 报告_prd prd on test.`match_id`=prd.`match_id` 
where prd.`match_id` is null;
# 少的
select prd.* from 报告_test a right join 报告_prd prd on test.`match_id`=prd.`match_id`
where test.`match_id` is null;

# 条数不一致的
select * from (
select a.`match_id`,count(1) as star from 报告_test  a  group by a.`match_id`
) aa join (
select a.`match_id`,count(1) as otc from 报告_prd  a  group by a.`match_id`
)bb on aa.`match_id`=bb.`match_id` and aa.star<>bb.otc
;

3. 创建一张错误的表，根据匹配的字段，把所有不一致的字段捞到另外一张表中；

包括以下几列：match_id, 字段名称, 测试环境的值, 生产环境的值, 备注。

建表语句

DROP TABLE IF EXISTS  errordata0703;

CREATE TABLE `errordata0703`  (
	`match_id` varchar(100) NULL,
	`字段` varchar(50) NULL,
	`test` varchar(500) NULL,
	`prd` varchar(500) NULL,
	`remark` varchar(500) NULL
)
;

insert into errordata0703 select 报告_test.match_id, '客户名称' as 字段, 报告_test.客户名称 as star,报告_prd.客户名称 as otc from 报告_test a join 报告_prd b on 报告_test.`match_id`=报告_prd.`match_id` where 报告_test.客户名称<>报告_prd.客户名称;

4. 维护一张历史错误原因的表，更新 remark/备注 字段

这张表用来把历史已经调查过的问题更新的最新的结果里面。包括以下几个关键信息

1. 类类型：是哪张报表？
2. 字段：
3. 匹配的字段：
4. 匹配的key值：
5. remark：

最后就可以根据 "已知原因" 来更新了。