MySQL 查询统计两张表数据并合并显示

原创于 2023-04-22 16:39:55 发布 · 602 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

sql

本文介绍如何利用MySQL的UNION或UNION ALL关键字，结合order和withdraw表，根据userID按天统计每个用户每天的充值与退款总额，以实现数据的合并显示。参考链接提供详细操作步骤。

order 表

CREATE TABLE `lyh_order` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `order_sn` varchar(128) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `amount` int(11) NOT NULL DEFAULT '0' COMMENT '订单金额',
  `real_amount` int(11) NOT NULL DEFAULT '0' COMMENT '真实支付金额',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态 0待支付 1已支付  2已发放',
  `type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1:普通充值 2:首充大礼包 3:限时秒杀',
  `pay_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '支付方式',
  `paid_at` timestamp NULL DEFAULT NULL COMMENT '支付时间',
  `goods_id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '商品id',
  `goods_info` json NOT NULL COMMENT '商品json',
  `other_sn` varchar(128) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '三方订单号',
  `ip` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `sys_goods_id` varchar(128) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '系统内部计费点id',
  `sys_goods_desc` varchar(128) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `channel_id` int(11) NOT NULL DEFAULT '0' COMMENT '渠道id',
  `user_name` varchar(128) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '用户昵称',
  `other_info` text COLLATE utf8mb4_bin COMMENT '客户端额外信息',
  `version` varchar(64) COLLATE utf8mb4_bin DEFAULT '1.0.0',
  PRIMARY KEY (`id`),
  KEY `idx_user_id_paid_at` (`user_id`,`paid_at`),
  KEY `idx_paid_at` (`paid_at`),
  KEY `idx_order_sn` (`order_sn`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_status` (`status`),
  KEY `idx_type` (`amount`),
  KEY `idx_userid_updatedat_status` (`user_id`,`updated_at`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=36480 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

withdraw 表

CREATE TABLE `withdrawinfo` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '提现ID',
  `UserID` bigint(20) NOT NULL COMMENT '用户ID',
  `Orderid` varchar(32) NOT NULL DEFAULT '',
  `Nick` varchar(64) DEFAULT '',
  `Amount` bigint(20) NOT NULL COMMENT '提现金额',
  `Phone` varchar(64) DEFAULT '' COMMENT '绑定手机号',
  `BScore` bigint(20) NOT NULL DEFAULT '0' COMMENT '提现前金额',
  `Tax` int(11) DEFAULT '0' COMMENT '手续费',
  `Status` int(10) NOT NULL DEFAULT '0' COMMENT '状态 0-申请中,1审核过,2-完成,3-提现失败,4审核失败,5取消提现',
  `RefID` varchar(64) DEFAULT '' COMMENT '关联兑换ID',
  `DMode` varchar(32) DEFAULT '' COMMENT '提现方式',
  `DBankCode` varchar(64) DEFAULT '' COMMENT '提现银行代号',
  `DBankAcct` varchar(64) DEFAULT '' COMMENT '提现银行账号',
  `DBankUser` varchar(64) DEFAULT '' COMMENT '提现银行用户',
  `UpdateTime` datetime DEFAULT '1970-01-01 00:00:00' COMMENT '提现时间',
  `CreateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `Createip` varchar(32) DEFAULT '',
  `OrderMsg` varchar(255) DEFAULT '' COMMENT '订单消息',
  `OperID` int(11) DEFAULT '0',
  `OperTime` datetime DEFAULT CURRENT_TIMESTAMP,
  `Channelid` bigint(20) NOT NULL DEFAULT '0' COMMENT '注册渠道ID',
  `LastDrawed` datetime DEFAULT '2022-01-01 00:00:00',
  PRIMARY KEY (`ID`),
  KEY `orderindex` (`Orderid`),
  KEY `wcreateindex` (`CreateTime`),
  KEY `woperindex` (`OperTime`)
) ENGINE=InnoDB AUTO_INCREMENT=5300 DEFAULT CHARSET=utf8;

这两个表相关联的是用户表中的userID
目标是:按天统计显示每个用户每天的充值和退款值
使用 union(对结果集去重) 或者 union all(不去重) 关键字将两个 select 语句的结果作为一个整体显示出来

        $totalCount = Db::select("select count(*) as count from (select o.*,w.* from (select user_id, SUM(real_amount) as payAmount, COUNT(user_id) as payCount, DATE_FORMAT(paid_at, '%Y-%m-%d') as dt from pay.lyh_order where status in (1, 2) $whereStrPay  GROUP BY user_id,dt ORDER BY dt DESC) o left join (select UserID, SUM(Amount + Tax) as withdrawAmount, COUNT(UserID) as withdrawCount, DATE_FORMAT(UpdateTime, '%Y-%m-%d') as dtW from extradb.withdrawinfo  where `status` = 2 $whereStrWithdraw  GROUP BY dtW,UserID ORDER BY dtW DESC) w on o.user_id = w.UserID union select o.*,w.* from (select user_id, SUM(real_amount) as payAmount, COUNT(user_id) as payCount, DATE_FORMAT(paid_at, '%Y-%m-%d') as dt from pay.lyh_order where status in (1, 2) $whereStrPay GROUP BY user_id,dt ORDER BY dt DESC) o right join (select UserID, SUM(Amount + Tax) as withdrawAmount, COUNT(UserID) as withdrawCount, DATE_FORMAT(UpdateTime, '%Y-%m-%d') as dtW from extradb.withdrawinfo  where `status` = 2 $whereStrWithdraw   GROUP BY dtW,UserID ORDER BY dtW DESC) w on o.user_id = w.UserID) as q");
        $result["total"] = $totalCount[0]->count;
        $result["pageSize"] = $paginatorQo->pageSize;
        $result["currentPage"] = $paginatorQo->currentPage;
        $startRow = ($paginatorQo->currentPage - 1) * $paginatorQo->pageSize;
        $endRow = $startRow + $paginatorQo->pageSize;
        $users = Db::select("select * from (select o.*,w.* from (select user_id, SUM(real_amount) as payAmount, COUNT(user_id) as payCount, DATE_FORMAT(paid_at, '%Y-%m-%d') as dt from pay.lyh_order where status in (1, 2) $whereStrPay  GROUP BY user_id,dt ORDER BY dt DESC) o left join (select UserID, SUM(Amount + Tax) as withdrawAmount, COUNT(UserID) as withdrawCount, DATE_FORMAT(UpdateTime, '%Y-%m-%d') as dtW from extradb.withdrawinfo  where `status` = 2 $whereStrWithdraw  GROUP BY dtW,UserID ORDER BY dtW DESC) w on o.user_id = w.UserID union select o.*,w.* from (select user_id, SUM(real_amount) as payAmount, COUNT(user_id) as payCount, DATE_FORMAT(paid_at, '%Y-%m-%d') as dt from pay.lyh_order where status in (1, 2) $whereStrPay GROUP BY user_id,dt ORDER BY dt DESC) o right join (select UserID, SUM(Amount + Tax) as withdrawAmount, COUNT(UserID) as withdrawCount, DATE_FORMAT(UpdateTime, '%Y-%m-%d') as dtW from extradb.withdrawinfo  where `status` = 2 $whereStrWithdraw   GROUP BY dtW,UserID ORDER BY dtW DESC) w on o.user_id = w.UserID) as q limit $startRow,$endRow"); 
        $result["items"] = $users;