欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysql 全连接查询合并字段

程序员文章站 2022-06-12 17:39:13
...

1、创建两张表 t1如下: id name 1 a1 3 a3 t2如下: id name 1 b1 2 b2 5 b5 联合查询合并字段: sql语句: SELECT t3.`name` AS B, t3.`name1` AS C ,COALESCE(t3.id2,t3.id1) AS myId FROM (SELECT t1.id as id2 ,t1.`name` as name,t2.id as id1 ,t2.`na

1、创建两张表

t1如下:

id name
1 a1
3 a3


t2如下:

id name
1 b1
2 b2
5 b5


联合查询合并字段:

sql语句:

SELECT t3.`name` AS B, t3.`name1` 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;

查询结果:


B C myID
a1 b1 1
b2 2
a3 3
b5 5