mysql用视图union多表

订单表xfc_order

DROP TABLE IF EXISTS `xfc_order`;
CREATE TABLE `xfc_order` (
  `order_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `order_code` varchar(32) NOT NULL COMMENT '订单号',
  `pay_time` int(11) unsigned NOT NULL COMMENT '付款时间',
  `pay_amount` decimal(12,0) unsigned NOT NULL COMMENT '支付金额',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of xfc_order
-- ----------------------------
INSERT INTO `xfc_order` VALUES ('2', '12345678', '1711050019', '100');
INSERT INTO `xfc_order` VALUES ('3', '12345678', '1711050019', '200');

　　退款表xfc_refund

DROP TABLE IF EXISTS `xfc_refund`;
CREATE TABLE `xfc_refund` (
  `ref_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(11) unsigned NOT NULL COMMENT '订单id',
  `refund_amount` decimal(12,0) unsigned NOT NULL COMMENT '退款金额',
  `verify_time` int(11) unsigned NOT NULL COMMENT '审核时间',
  `ref_status` tinyint(1) NOT NULL DEFAULT '-1' COMMENT '-2拒绝-1待审核1通过',
  PRIMARY KEY (`ref_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of xfc_refund
-- ----------------------------
INSERT INTO `xfc_refund` VALUES ('1', '2', '80', '1711079019', '1');
INSERT INTO `xfc_refund` VALUES ('2', '3', '180', '1711079019', '1');

　　创建现金流视图xfc_refund

CREATE VIEW fund_info AS
(
SELECT order_id,1 fund_type,pay_amount fund_amount,pay_time fundtime FROM `xfc_order` o WHERE o.pay_time>0 )
 UNION  ALL
(SELECT r.order_id,2 fund_type,r.refund_amount fund_amount,r.verify_time fundtime FROM `xfc_refund` r WHERE r.ref_status=1
);

　　SELECT * FROM fund_info;

返回的结果是同一订单一条支付成功一条退款成功的集合，就是订单的交易流水。

mysql> SELECT * FROM fund_info;
+----------+-----------+-------------+------------+
| order_id | fund_type | fund_amount | fundtime   |
+----------+-----------+-------------+------------+
|        2 |         1 |         100 | 1711050019 |
|        3 |         1 |         200 | 1711050019 |
|        2 |         2 |          80 | 1711079019 |
|        3 |         2 |         180 | 1711079019 |
+----------+-----------+-------------+------------+
4 rows in set (0.01 sec)