我有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的其它字段.多谢!!
表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的其它字段.多谢!!
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
[align=center]==== 思想重于技巧 ====
[/align]
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
[align=center]==== 思想重于技巧 ====
[/align]