求一条SQL语句(100分)

求一SQL语句<br>表1<br>A &nbsp; &nbsp;B &nbsp; &nbsp;C &nbsp; &nbsp;D &nbsp; &nbsp; E<br>AAA &nbsp;1 &nbsp; &nbsp;2 &nbsp; &nbsp;1 &nbsp; &nbsp; 5<br>BBB &nbsp;2 &nbsp; &nbsp;1 &nbsp; &nbsp;3 &nbsp; &nbsp; 1<br>表二：<br>字段一 &nbsp;字段二 &nbsp;字段三 &nbsp;<br>B &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 男<br>B &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; 女<br>C &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 工人<br>C &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; 干部<br>D &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 高中<br>D &nbsp; &nbsp; &nbsp; &nbsp;3 &nbsp; &nbsp; &nbsp; 本科<br>E &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 汉族<br>E &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; 白族<br><br>查询结果<br>&nbsp;A &nbsp; &nbsp;B &nbsp; &nbsp;C &nbsp; &nbsp; &nbsp; D &nbsp; &nbsp; &nbsp; E<br>AAA &nbsp;男 &nbsp; &nbsp;干部 &nbsp; &nbsp;高中 &nbsp; &nbsp;白族<br>BBB &nbsp;女 &nbsp; &nbsp;工人 &nbsp; &nbsp;本科 &nbsp; &nbsp;汉族

select A,B=(select 字段三 from t2 where 字段ㄧ='B' and 字段二=t1.B),<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C=(select 字段三 from t2 where 字段ㄧ='C' and 字段二=t1.C),<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D=(select 字段三 from t2 where 字段ㄧ='D' and 字段二=t1.D),<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;E=(select 字段三 from t2 where 字段ㄧ='E' and 字段二=t1.E)<br>from t1

select 表1.A,t3.字段三 as B,t4.字段三 as C,t5.字段三 as D,t6.字段三 as E<br>from 表1<br>left join<br>(select * from 表2 where 字段一='B') t3 on 表1.B=字段二<br>left join<br>(select * from 表2 where 字段一='C') t4 on 表1.C=字段二<br>left join<br>(select * from 表2 where 字段一='D') t5 on 表1.D=字段二<br>left join<br>(select * from 表2 where 字段一='E') t6 on 表1.E=字段二

冒似很简单的问题

俺接受答案啦