Question

mysql 两个表相互匹配的问题

t1:
"id" "i_id" "name"
"01" "1" "a"
"01" "2" "b"
"01" "3" "c"
"01" "4" "d"

t2:
"id" "x" "y" "z" "m" "n"
"01" "1" "2" "2" "3" "3"

我想要得到
"id" "x" "y" "z" "m" "n"
"id" "a" "b" "b" "c" "c"

请问怎么写

Answer 1

select 
t2.id,
x.name x,
y.name y,
z.name z,
m.name m,
n.name n
FROM t2
left JOIN t1 x on x.id = t2.id and x.i_id = t2.x
left JOIN t1 y on y.id = t2.id and y.i_id = t2.y
left JOIN t1 z on z.id = t2.id and z.i_id = t2.z
left JOIN t1 m on m.id = t2.id and m.i_id = t2.m
left JOIN t1 n on n.id = t2.id and n.i_id = t2.n
where t2.id = '01'

Answer 2

1、两张表都有的记录如(1, 'A'); (2, 'B');
select a.* from t1 a inner join t2 b on a.id=b.id and a.val=b.val
1、表一有表二没有的记录如(3, 'C');
select a.* from t1 a left join t2 b on a.id=b.id and a.val=b.val
where b.id is null
3、表二有表一没有的记录如(1, 'C','22'); (4, 'D','44');
select b.* from t1 a right join t2 b on a.id=b.id and a.val=b.val
where a.id is null