两个表bank_bill和biz_bill的结构定义最好是一致的,如果不完全一致,需要在sql语句中进行定义别名的方式进行重新定义。
0. 环境准备
银行账单表:
CREATE TABLE `bank_bill` (
`id` varchar(32) NOT NULL COMMENT '流水号',
`merchant_no` varchar(32) DEFAULT NULL COMMENT '商户编号',
`pay_channel` varchar(32) DEFAULT NULL COMMENT '支付渠道',
`trans_type` varchar(32) DEFAULT NULL COMMENT '交易类型',
`merchant_order_no` varchar(64) DEFAULT NULL COMMENT '订单编号',
`trans_datetime` varchar(32) DEFAULT NULL COMMENT '交易时间',
`trans_amount` double(15,2) DEFAULT NULL COMMENT '交易金额',
`merchant_account` varchar(32) DEFAULT NULL COMMENT '商户账户',
`account_change` double(15,2) DEFAULT NULL COMMENT '动账金额',
`customer_account` varchar(32) DEFAULT NULL COMMENT '客户账户',
`account_type` varchar(32) DEFAULT NULL COMMENT '账户类型',
`bank_fee` double(15,2) DEFAULT NULL COMMENT '回佣手续费',
`divide_fee` double(15,2) DEFAULT NULL COMMENT '分期手续费',
`account_date` varchar(20) DEFAULT NULL COMMENT '会计日期',
`flow_host` varchar(20) DEFAULT NULL COMMENT '主机流水号',
`flow_9014` varchar(32) DEFAULT NULL COMMENT '流水号',
`ref_order` varchar(64) DEFAULT NULL COMMENT '原订单号',
PRIMARY KEY (`id`),
UNIQUE KEY `pkindex` (`merchant_no`,`merchant_order_no`) USING BTREE,
KEY `bill_no_index` (`merchant_order_no`) USING BTREE,
KEY `trans_datetime` (`trans_datetime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='银行账单表';
业务数据:
INSERT INTO `bank_bill` VALUES ('21ca1b16d1c44a1b80cd9ca4b463da2d', '103882280002012', 'wxalipay', 'weixinpay', '102002060000156235202105262020412243', '2020-06-10 09:57:33', '0.01', '22810101941000825', '0.01', '', 'OTHERS', '0.00', '0.00', '2020-06-11', '285702419', '6AECEP01095416928604', '');
INSERT INTO `bank_bill` VALUES ('4256f61dc53e485da6bfd3dc6932fb1d', '103882280002012', 'wxalipay', 'weixinpay', '102002060000156397202105252100295721', '2020-06-10 10:06:33', '0.01', '22810101941000825', '0.01', '', 'OTHERS', '0.00', '0.00', '2020-06-11', '285702419', '6AECEP01100448335484', '');
INSERT INTO `bank_bill` VALUES ('83f446fe484f4e2a8dbb5cbab3f8195e', '103882280002012', 'wxalipay', 'weixinpay', '102002060000156235202105262124027097', '2020-06-10 09:55:55', '0.01', '22810101941000825', '0.01', '', 'OTHERS', '0.00', '0.00', '2020-06-11', '285702419', '6AECEP01095244873600', '');
INSERT INTO `bank_bill` VALUES ('8a15423309974f35b106c09f9ca023b7', '103882280002012', 'wxalipay', 'weixinpay', '102002060000131626202105261638336030', '2020-06-10 09:59:05', '0.01', '22810101941000825', '0.01', '', 'BOCD_DEBIT', '0.00', '0.00', '2020-06-11', '285702419', '6AECEP01095823067133', '');
业务账单表
CREATE TABLE `biz_bill` (
`id` varchar(32) NOT NULL COMMENT '流水号',
`merchant_no` varchar(32) DEFAULT NULL COMMENT '商户编号',
`pay_channel` varchar(32) DEFAULT NULL COMMENT '支付渠道',
`trans_type` varchar(32) DEFAULT NULL COMMENT '交易类型',
`merchant_order_no` varchar(64) DEFAULT NULL COMMENT '订单编号',
`trans_datetime` varchar(32) DEFAULT NULL COMMENT '交易时间',
`trans_amount` double(15,2) DEFAULT NULL COMMENT '交易金额',
`merchant_account` varchar(32) DEFAULT NULL COMMENT '商户账户',
`account_change` double(15,2) DEFAULT NULL COMMENT '动账金额',
`customer_account` varchar(32) DEFAULT NULL COMMENT '客户账户',
`account_type` varchar(32) DEFAULT NULL COMMENT '账户类型',
`bank_fee` double(15,2) DEFAULT NULL COMMENT '回佣手续费',
`divide_fee` double(15,2) DEFAULT NULL COMMENT '分期手续费',
`account_date` varchar(20) DEFAULT NULL COMMENT '会计日期',
`flow_host` varchar(20) DEFAULT NULL COMMENT '主机流水号',
`flow_9014` varchar(32) DEFAULT NULL COMMENT '流水号',
`ref_order` varchar(64) DEFAULT NULL COMMENT '原订单号',
PRIMARY KEY (`id`),
UNIQUE KEY `pkindex` (`merchant_no`,`merchant_order_no`) USING BTREE,
KEY `bill_no_index` (`merchant_order_no`) USING BTREE,
KEY `trans_datetime` (`trans_datetime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='业务账单表';
测试数据:
INSERT INTO `biz_bill` VALUES ('21ca1b16d1c44a1b80cd9ca4b463da2d', '103882280002012', 'wxalipay', 'weixinpay', '102002060000156235202105262020412243', '2020-06-10 09:57:33', '0.01', '22810101941000825', '0.01', '', 'OTHERS', '0.00', '0.00', '2020-06-11', '285702419', '6AECEP01095416928604', '');
INSERT INTO `biz_bill` VALUES ('4256f61dc53e485da6bfd3dc6932fb1d', '103882280002012', 'wxalipay', 'weixinpay', '102002060000156397202105252100295721', '2020-06-10 10:06:33', '0.01', '22810101941000825', '0.01', '', 'OTHERS', '0.00', '0.00', '2020-06-11', '285702419', '6AECEP01100448335484', '');
INSERT INTO `biz_bill` VALUES ('83f446fe484f4e2a8dbb5cbab3f8195e', '103882280002012', 'wxalipay', 'weixinpay', '102002060000156235202105262124027097', '2020-06-10 09:55:55', '0.01', '22810101941000825', '0.01', '', 'OTHERS', '0.00', '0.00', '2020-06-11', '285702419', '6AECEP01095244873600', '');
INSERT INTO `biz_bill` VALUES ('8a15423309974f35b106c09f9ca023b7', '103882280002012', 'wxalipay', 'weixinpay', '102002060000131626202105261638336030', '2020-06-10 09:59:05', '0.01', '22810101941000825', '0.01', '', 'BOCD_DEBIT', '0.00', '0.00', '2020-06-11', '285702419', '6AECEP01095823067133', '');
1. 计算bank_bill和biz_bill的差集
bank_bill和biz_bill的差集是指在bank_bill表中存在,但是在biz_bill表中不存在的数据。
mysql> select a.merchant_order_no, b.merchant_order_no from bank_bill a left join biz_bill b on a.merchant_order_no = b.merchant_order_no where b.merchant_order_no is null;
Empty set
# 修改biz_bill表中两条记录的merchant_order_no字段值,在末尾添加1
mysql> select a.merchant_order_no, b.merchant_order_no from bank_bill a left join biz_bill b on a.merchant_order_no = b.merchant_order_no where b.merchant_order_no is null;
+--------------------------------------+-------------------+
| merchant_order_no | merchant_order_no |
+--------------------------------------+-------------------+
| 102002060000131626202105261638336030 | NULL |
| 102002060000156235202105262124027097 | NULL |
+--------------------------------------+-------------------+
针对找到的差异,根据业务需求,可以对有偏差的数据进行自动修正或者人工修正,必要时,还可以进行多次比对。
2. 计算biz_bill和bank_bill的差集
biz_bill和bank_bill的差集是指在biz_bill表中存在,但是在bank_bill表中不存在的数据。
mysql> select a.merchant_order_no, b.merchant_order_no from biz_bill a left join bank_bill b on a.merchant_order_no = b.merchant_order_no where b.merchant_order_no is null;
+---------------------------------------+-------------------+
| merchant_order_no | merchant_order_no |
+---------------------------------------+-------------------+
| 1020020600001316262021052616383360301 | NULL |
| 1020020600001562352021052621240270971 | NULL |
+---------------------------------------+-------------------+
2 rows in set
3. 计算bank_bill和biz_bill的交集
当bank_bill和biz_bill的差集与biz_bill和bank_bill的差集都为空的时候,就说明bank_bill和biz_bill的数据是一致的了。此时a和b以及a和b的交集都是一样的数据了。
#两个表数据一致时
mysql> select a.merchant_order_no, b.merchant_order_no from biz_bill a left join bank_bill b on a.merchant_order_no = b.merchant_order_no where b.merchant_order_no = a.merchant_order_no;
+--------------------------------------+--------------------------------------+
| merchant_order_no | merchant_order_no |
+--------------------------------------+--------------------------------------+
| 102002060000131626202105261638336030 | 102002060000131626202105261638336030 |
| 102002060000156235202105262020412243 | 102002060000156235202105262020412243 |
| 102002060000156235202105262124027097 | 102002060000156235202105262124027097 |
| 102002060000156397202105252100295721 | 102002060000156397202105252100295721 |
+--------------------------------------+--------------------------------------+
#两个表数据部分匹配时
mysql> select a.merchant_order_no, b.merchant_order_no from biz_bill a left join bank_bill b on a.merchant_order_no = b.merchant_order_no where b.merchant_order_no = a.merchant_order_no;
+--------------------------------------+--------------------------------------+
| merchant_order_no | merchant_order_no |
+--------------------------------------+--------------------------------------+
| 102002060000156235202105262020412243 | 102002060000156235202105262020412243 |
| 102002060000156397202105252100295721 | 102002060000156397202105252100295721 |
+--------------------------------------+--------------------------------------+
4. 根据业务需求决定统计数据来源
当数据根据关键字和状态信息比对成功以后,就可以根据相关的业务数据存储位置,分别从对应的表中读取相关数据信息,进行统计汇总。
5. 将两个表合并为一个大表
将两个表的数据合并为一个大表,设置一个全局唯一的主键,原则上以基准表中的主键为主键,两个表中的字段都添加到一个表中,采用特定的命名方式进行区分。当数据添加完成后,可以通过where条件对单表进行各种条件的查询,以达到取交集和并集的目的。