求联合查询语句。查询多套人员代码！

Question

例如表1中字段为：学生代码、学生姓名，学生年级。表2中字段为：语文、学生代码1、数学、学生代码2、英语、学生代码3
现在查通过查询，查到与表2学生代码相符的学生姓名。结果为：
 例： 语文、学生姓名1、学生代码1、数学、学生姓名2、学生代码2、英语、学生姓名、学生代码3
       100分、张三、 A001、 100分、李四、A002、120分、李飞、A003
求联合查询的查询语句，主要是怎么把学生代码给区分开来。谢谢大虾们！

Answer 1

use test
go
if object_id('test.dbo.tb1') is not null drop table tb1
-- 创建数据表
create table tb1
(
学生代码 char(5),
学生姓名 char(5),
学生年级 int
)
go
--插入测试数据
insert into tb1 select 'A001','张三',1
union all select 'A002','李四',1
union all select 'A003','李飞',1
union all select 'A004','王五',1
go
if object_id('test.dbo.tb2') is not null drop table tb2
-- 创建数据表
create table tb2
(
语文 int,
学生代码1 char(5),
数学 int,
学生代码2 char(5),
英语 int,
学生代码3 char(5)
)
go
--插入测试数据
insert into tb2 select 100,'A001',100,'A002',120,'A003'
union all select 110,'A002',90,'A004',115,'A001'
go
--代码实现select 语文
,学生姓名1=(select 学生姓名 from tb1 where 学生代码=a.学生代码1)
,学生代码1
,数学
,学生姓名2=(select 学生姓名 from tb1 where 学生代码=a.学生代码2)
,学生代码2
,英语
,学生姓名3=(select 学生姓名 from tb1 where 学生代码=a.学生代码3)
,学生代码3
from tb2 a/*测试结果语文 学生姓名1 学生代码1 数学 学生姓名2 学生代码2 英语 学生姓名 学生代码3
-------------------------------------------------------------------------------------
100 张三  A001  100 李四  A002  120 李飞  A003 
110 李四  A002  90 王五  A004  115 张三  A001 (2 行受影响)
*/

Answer 2

--你的表设计不合理
select a.语文,学生姓名1=b.学生姓名,a.学生代码1
,数学,学生姓名2=c.学生姓名,学生代码2,
英语,学生姓名3=d.学生姓名,学生代码3
from tb2 a
    inner join tb1 b on a.学生代码1=b.学生代码
 inner join tb1 c on a.学生代码2=c.学生代码
 inner join tb1 d on a.学生代码3=d.学生代码