请问以上例子的sql如何写_Sql Server

请教以下例子的sql怎么写
表 table1

codea codeb
8 2
3 1
5 3
2 7

表 table2

code name
1 tom
2 kate
3 sherry
........
........
........

codea 和 codeb 分别都是学号，我要生成一个表，类似以下格式，请问sql 怎么写，要求简洁而且效率高的写法。

codea codeb nameA nameB
3 2 sherry kate
3 1
5 3
2 7

------解决方案--------------------

SQL code

select A.codea,A.codeb,B.name as nameA,C.name as nameBfrom table1 as Aleft join table2 as B on A.codea=B.codeleft join table2 as C on A.codeb=C.code
------解决方案--------------------
SQL code--> 测试数据：[table1]if object_id('[table1]') is not null drop table [table1]create table [table1]([codea] int,[codeb] int)insert [table1]select 8,2 union allselect 3,1 union allselect 5,3 union allselect 2,7--> 测试数据：[table2]if object_id('[table2]') is not null drop table [table2]create table [table2]([code] int,[name] varchar(6))insert [table2]select 1,'tom' union allselect 2,'kate' union allselect 3,'sherry' union allselect 4,'tracy'select a.*,isnull(b.name,'') as nameA,isnull(c.name,'') as nameBfrom table1 aleft join table2 b on a.codea=b.codeleft join table2 c on a.codeb=c.code/*codea    codeb    nameA    nameB---------------------------------------------8    2        kate3    1    sherry    tom5    3        sherry2    7    kate    */