要从一个表中不同条记录的内容中提取信息,变成一行自己想要的数据,需要使用自连接。
方法:使用JOIN关键字,用on 把这条记录和查询记录的相同条件连接起来。
join Memberinfo T2 on T1.MComID = T2.MemberID
e.g
select T1.CurrencyID,
T1.MemberID,
T1.Balance,
T1.Turnoverp3,
T1.MComID,
T2.PossessionP3 AS MComPer,
T1.SupAgID,
T3.PossessionP3 as SupAgPer,
T3.TurnOverP3 as SupAgComiP,
T1.SenAgID,
T4.PossessionP3 as SenAgPer,
T4.TurnOverP3 as SenAgComiP,
T1.MasAgID,
T5.PossessionP3 as MasAgPer,
T5.TurnOverP3 as MasAgComiP,
T1.AgID,
T6.PossessionP3 as AgPer,
T6.TurnOverP3 as AgComiP
from MemberInfo T1
join Memberinfo T2 on T1.MComID = T2.MemberID
join Memberinfo T3 on T1.SupAgID = T3.MemberID
join Memberinfo T4 on T1.SenAgID = T4.MEMBERID
join Memberinfo T5 on T1.MasAgID = T5.MEMBERID
join Memberinfo T6 on T1.AgID = T6.MEMBERID
where T1.AcctID ='xxx';