自连接的写法

最新推荐文章于 2023-12-11 15:43:15 发布

ytf8848

最新推荐文章于 2023-12-11 15:43:15 发布

阅读量849

点赞数

分类专栏： SQL 文章标签： join

本文链接：https://blog.csdn.net/ytf8848/article/details/5832216

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

要从一个表中不同条记录的内容中提取信息，变成一行自己想要的数据，需要使用自连接。

方法：使用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';