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.idUNION 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 | |