关于Inner jion的特殊问题

[smszw3370]

我有2张表,表A,表B,结构如下:
表A:A_ID,B_ID1,B_ID2,B_ID3,A_Field1, A_Field2
1 1 2 3 A_Field11 A_Field21
2 3 1 1 A_Field12 A_Field22

表B:B_ID,B_Field1
1 B_Field11
2 B_Field12
3 B_Field13

其中,表A的B_ID1,B_ID2,B_ID3均指向表B的B_ID

问题:
如何构造查询语句,得到:
表C:A_ID,C_NewField1,C_NewField2,C_NewField3,A_Field1,A_Field2
1 B_Field11 B_Field12 B_Field13 A_Field11 A_Field21
2 B_Field13 B_Field11 B_Field11 A_Field12 A_Field22

简单说,就是生成一个查询,将表A的B_ID1,B_ID2,B_ID3替换成表B的B_Field1字段内容,并保留表A的其它字段.

多谢!!

[liuyann]

select a.A_ID,
	b1.B_Field1 as C_NewField1,
	b2.B_Field1 as C_NewField2,
	b3.B_Field1 as C_NewField3,
	a.A_Field1,
	a.A_Field2
from ((A inner join B b1 on a.B_ID1=b1.B_ID)
	inner join B b2 on a.B_ID2=b2.B_ID)
	inner join B b3 on a.B_ID3=b3.B_ID

==== 思想重于技巧 ====

reply via CSDN viewer

[liuyann]

或者

select a.A_ID,
	b1.B_Field1 as C_NewField1,
	b2.B_Field1 as C_NewField2,
	b3.B_Field1 as C_NewField3,
	a.A_Field1,
	a.A_Field2
from ((A left join B b1 on a.B_ID1=b1.B_ID)
	left join B b2 on a.B_ID2=b2.B_ID)
	left join B b3 on a.B_ID3=b3.B_ID

==== 思想重于技巧 ====

reply via CSDN viewer

[smszw3370]

谢了,先试试看!

[smszw3370]

太好了!完全可以啊.