Mysql 数据处理（知识点）

小鱼儿 2023-10-07 11:22 36阅读 0赞

**任务需求**：合并两张表（不重复字段）并根据条件查询出需要的字段；

**合并两张表的数据并把相同的字段合并，不相同的字段都显示出来**

union

select
    		a.regist_time,
    		// Select 子查询
    		(select name from platf_channel p where a.channel_code=p.id) channelName,
    		a.channel_code,
    		a.audit_status2,
    		a.audit_status4,
    		a.audit_status5,
    		//  判断是不是为null 是返回 0 不是返回本身
    		(case when b.applyNum is null then 0 else b.applyNum end) applyNum,
    		(case when b.loanNum is null then 0 else b.loanNum end) loanNum,
    		a.registNum
    
    		from
    		// From 子查询
    		(select
    		count(1) registNum,
    		date(regist_time) regist_time,
    		channel_code,
    		// 求和 判断状态=2的总和
    		sum(case when audit_status = 2 then 1 else 0 end) audit_status2,
    		sum(case when audit_status = 4 then 1 else 0 end) audit_status4,
    		sum(case when audit_status = 5 then 1 else 0 end) audit_status5
    		from
    		platf_user
    		where regist_time between #{registTimeStart} and #{registTimeEnd}
    		group by channel_code, date(regist_time)) a
    		// 左连接 数据以左表的字段为准 包含右表的则显示 不包含的则去掉
    		left join
    
    		(select
    		date(loan_time) loan_time,
    		source_channels,
    		sum(case when loan_time between #{registTimeStart} and #{registTimeEnd} then 1 else 0 end) applyNum,
    		sum(case when (make_loans_time between #{registTimeStart} and #{registTimeEnd}) and make_sts = 3 then 1 else 0 end) loanNum
    		from
    		loan_order
    		where (loan_time between #{registTimeStart} and #{registTimeEnd})
    		or (make_loans_time between #{registTimeStart} and #{registTimeEnd})
    		group by source_channels, date(loan_time)) b
    
    		on a.channel_code = b.source_channels and a.regist_time = b.loan_time
    		// 合并两张表
    		union
    
    		# ---
    
    		select
    
    		d.loan_time regist_time,
    		(select name from platf_channel p where d.source_channels=p.id) channelName,
    		d.source_channels channel_code,
    		// IFNULL 判断是否为空  第一个参数如果为空则返回第二个参数 
    		IFNULL(c.audit_status2, 0) audit_status2,
    		IFNULL(c.audit_status4, 0) audit_status4,
    		IFNULL(c.audit_status5, 0) audit_status5,
    		IFNULL(d.applyNum, 0) applyNum,
    		IFNULL(d.loanNum, 0) loanNum,
    		IFNULL(c.registNum, 0) registNum
    
    		from
    
    		(select
    		count(platf_user.id) registNum,
    		date(regist_time) regist_time,
    		channel_code,
    		sum(case when audit_status = 2 then 1 else 0 end) audit_status2,
    		sum(case when audit_status = 4 then 1 else 0 end) audit_status4,
    		sum(case when audit_status = 5 then 1 else 0 end) audit_status5
    		from
    		platf_user
    		where regist_time between #{registTimeStart} and #{registTimeEnd}
    		group by channel_code, date(regist_time)) c
    		// 右查询 以右边的表为准  包含左边表的字段合并 不包含的则去除
    		right join
    
    		(select
    		date(loan_time) loan_time,
    		source_channels,
    		sum(case when loan_time between #{registTimeStart} and #{registTimeEnd} then 1 else 0 end) applyNum,
    		sum(case when (make_loans_time between #{registTimeStart} and #{registTimeEnd}) and make_sts = 3 then 1 else 0 end) loanNum
    		from
    		loan_order
    		where (loan_time between #{registTimeStart} and #{registTimeEnd})
    		or (make_loans_time between #{registTimeStart} and #{registTimeEnd})
    		group by source_channels, date(loan_time)) d
    
    		on c.channel_code = d.source_channels and c.regist_time = d.loan_time
    
    		order by regist_time desc

### 关注公众号【程序员每日一学】让我们每天一起学习进步～ ###

![在这里插入图片描述][305987ebf35f415eb592cedbf39bf01e.jpg]

[305987ebf35f415eb592cedbf39bf01e.jpg]: https://img-blog.csdnimg.cn/305987ebf35f415eb592cedbf39bf01e.jpg