mysqlフル接続クエリーマージフィールド
684 ワード
1、2枚の表を作成する
表t 1:
id
name
1
a1
3
a3
表t 2:
id
name
1
b1
2
b2
5
b5
連結クエリーの連結フィールド:
B
c
myId
a1
b1
1
0
b2
2
a3
0
3
0
b5
5
sqlは以下の通りです.
表t 1:
id
name
1
a1
3
a3
表t 2:
id
name
1
b1
2
b2
5
b5
連結クエリーの連結フィールド:
B
c
myId
a1
b1
1
0
b2
2
a3
0
3
0
b5
5
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