mysql 全连接查询合并字段

1、创建两张表

表t1:

idname
1a1
3a3

表t2: 

idname
1b1
2b2
5b5

联合查询合并字段: 

BcmyId
a1       b1 1
0b22
a303
0b55

sql如下:

SELECT IFNULL(t3.name,0) as B, IFNULL(t3.name1,0) as C , COALESCE(t3.id2,t3.id1) as myId FROM ( SELECT t1.id as id2 ,t1.`name` as name,t2.id as id1 ,t2.`name` as name1 FROM t1 LEFT JOIN t2 on t1.id=t2.id

UNION

SELECT t1.id as id2 ,t1.`name` as name,t2.id as id1 ,t2.`name` as name1 FROM t1 RIGHT JOIN t2 on t1.id=t2.id ) as t3 GROUP BY myId



 

 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 撸撸猫 设计师:设计师小姐姐 返回首页