Question

mysql数据库查询，怎么实现以下结果

A表
AID ACODE （字段）
101 XXX
102 YYY

B表
BID AID BPID1 BPID2 BPID3 （字段）
201 101 B1 B2 B3
202 102 B4 B5 B6

A表与B表由AID相关联

想要得出以下结果
输出结果
B1 XXX
B2 XXX
B3 XXX
B4 YYY
B5 YYY
B6 YYY

请问查询语句怎么写？





Answer 1

select a.aid,a.acode,b.bid,b.aid
from a,b
where a.aid = b.aid

Answer 2

select bpid1 as bp,acode from
(select acode,bpid1,bpid2,bpid3 from
a1 INNER JOIN b1 on a1.aid=b1.aid) as t1;
union all
select bpid2 as bp,acode from
(select acode,bpid1,bpid2,bpid3 from
a1 INNER JOIN b1 on a1.aid=b1.aid) as t2;
union all
select bpid3 as bp,acode from
(select acode,bpid1,bpid2,bpid3 from
a1 INNER JOIN b1 on a1.aid=b1.aid) as t3;