mysql通过计算交集和差集实现两表的数据比对

最新推荐文章于 2024-08-02 13:27:36 发布

程序猿20

最新推荐文章于 2024-08-02 13:27:36 发布

阅读量507

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/liaomingwu/article/details/122957546

版权

SQL 数据比对表同步数据一致性数据库操作

关键词由CSDN通过智能技术生成

数据库专栏收录该内容

32 篇文章 2 订阅

订阅专栏

两个表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 |
+--------------------------------------+--------------------------------------+