student 表结构
id studentname address yuwenid shuxueid wuliid
1 张三 地址 1 2 3
teacher 表结构
id teachername
1 李四
2 王五
3 赵六
我现在想查出来这样的结构
id studentname address yuwenid yuwenname shuxueid shuxuename wuliid wuliname
1 张三 地址 1 李四 2 王五 3 赵六
SQL语句要怎么写？
3 个解决方案
#1
指定显示列
SELECT
*
FROM student AS a
INNER JOIN teacher AS b ON a.yuwenid=b.ID
INNER JOIN teacher AS c ON a.shuxueid=b.ID
INNER JOIN teacher AS d ON a.wuliid=b.ID
#2
使用 left join 吧， 没准这三列，哪一列没有数据了，就显示不出来了。
#3
create table student
(
id int,
studentname nvarchar(100),
address nvarchar(100),
yuwenid int,
shuxueid int,
wuliid int
)
insert student
values(1,N'张三',N'地址',1,2,3)
create table teacher
(
id int,
teachername nvarchar(100)
)
insert teacher
select 1,N'李四'
union
select 2,N'王五'
union
select 3,N'赵六'
select t1.id,t1.studentname,t1.address,t2.id,t2.teachername,t3.id,t3.teachername,t4.id,t4.teachername
from student t1
left join teacher t2 on t1.yuwenid=t2.id
left join teacher t3 on t1.shuxueid=t3.id
left join teacher t4 on t1.wuliid=t4.id
上面两位大神已经解决
#1
指定显示列
SELECT
*
FROM student AS a
INNER JOIN teacher AS b ON a.yuwenid=b.ID
INNER JOIN teacher AS c ON a.shuxueid=b.ID
INNER JOIN teacher AS d ON a.wuliid=b.ID
#2
使用 left join 吧， 没准这三列，哪一列没有数据了，就显示不出来了。
#3
create table student
(
id int,
studentname nvarchar(100),
address nvarchar(100),
yuwenid int,
shuxueid int,
wuliid int
)
insert student
values(1,N'张三',N'地址',1,2,3)
create table teacher
(
id int,
teachername nvarchar(100)
)
insert teacher
select 1,N'李四'
union
select 2,N'王五'
union
select 3,N'赵六'
select t1.id,t1.studentname,t1.address,t2.id,t2.teachername,t3.id,t3.teachername,t4.id,t4.teachername
from student t1
left join teacher t2 on t1.yuwenid=t2.id
left join teacher t3 on t1.shuxueid=t3.id
left join teacher t4 on t1.wuliid=t4.id
上面两位大神已经解决