我有一个studen表 字段为:ID(编号) Name(姓名) ChineseTID(姓名老师ID) EnglishTID(英语老师ID)
ID Name ChineseTID EnglishTID
1 张三 01 02
2 李四 01 04和一个teacher表 字段为ID(老师编号) Name(老师姓名)ID Name
01 王老师
02 阳老师
03 朱老师
04 虞老师现在我要返回的数据要求为: 学生的姓名,学号和对应的语文老师姓名,和英语老师姓名
如:
ID Name ChineseTName EnglishTName
1 张三 阳老师 虞老师请问:该如何写SQL语句....谢谢了
ID Name ChineseTID EnglishTID
1 张三 01 02
2 李四 01 04和一个teacher表 字段为ID(老师编号) Name(老师姓名)ID Name
01 王老师
02 阳老师
03 朱老师
04 虞老师现在我要返回的数据要求为: 学生的姓名,学号和对应的语文老师姓名,和英语老师姓名
如:
ID Name ChineseTName EnglishTName
1 张三 阳老师 虞老师请问:该如何写SQL语句....谢谢了
解决方案 »
- C# 中如何去掉GroupBox中的边框
- 如何循环遍历from窗体上的自定义控件
- SQL Server 数据库怎么批量添加?
- 数据库中二进制文件数据的显示
- Server.URLEncode的问题
- 网页图片显示红叉,用记事本打开,保存一下就能显示了,什么都没有改动,请问是怎么回事?
- 类似于CSDN的会员站内消息系统是怎么实现的?希望朋友们给些方案,分不够可加
- 我在程序中引用了sqldmo控件,可是安装时总是注册不上,提示“不能找到sqldmo输入点,无法注册控件”
- 关于线程间切换的问题(难度比较大)
- c# WebBrowser得不到HtmlElement btn = webBrowser1.Document.All["submit_cnzz"];提交按钮
- 我有点糊涂了,请教实例化问题
- 不能同时访问一个变量的方法
(select Name from teacher where ID = ChineseTID ) as ChineseTName ,
(select Name from teacher where ID = ChineseTID ) as EnglishTName from studen
FROM studen A LEFT JOIN
teacher B ON B.ID=A.ChineseTID
LEFT JOIN
teacher C ON C.ID=A.EnglishTID
select [ID],[Name]
(select Name from teacher where ID = ChineseTID ) as [ChineseTName],
(select Name from teacher where ID = EnglishTID ) as [EnglishTName] from studen
From studen A Inner Join teacher B on A.ChineseTID=B.ID
Inner Join teacher C On A.EnglishTID =C.ID
select studen.ID,studen.name, Ta.name, Tb.nam
from studen join teacher Ta
on Ta.ID = studen.ChineseTID
join teacher Tb
on Tb.ID = studen.EnglishTID
(select Name from teacher where ID = ChineseTID ) as [ChineseTName],
(select Name from teacher where ID = EnglishTID ) as [EnglishTName] from studen
create table student
(
ID int identity(1,1) not null,Name varchar(20),ChineseTID int,EnglishTID int
)
insert into student
select '张三' , 1,2 union all
select '李四' , 1,4
create table teacher
(
ID int identity(1,1) not null,Name varchar(20)
)
insert into teacher
select '王老师' union all
select '阳老师' union all
select '朱老师' union all
select '虞老师' select
ID,Name,
(select Name from teacher t where ChineseTID=t.ID) as ChineseTeacherName,
(select Name from teacher t where EnglishTID=t.ID) as EnglishTeacherName
from
student ID Name ChineseTeacherName EnglishTeacherName
----------- -------------------- -------------------- --------------------
1 张三 王老师 阳老师
2 李四 王老师 虞老师
(select Name from teacher where ID = ChineseTID ) as [ChineseTName],
(select Name from teacher where ID = EnglishTID ) as [EnglishTName] from studen这种写法的执行速度最快。 其他的凡是有 join 表联查的,速度回慢的多。在一百万条数据下,执行科看出效果。
SELECT A.ID,A.NAME,B.Name as ChineseTName,C.Name as EnglishTName
FROM studen A LEFT JOIN
teacher B ON B.ID=A.ChineseTID
LEFT JOIN
teacher C ON C.ID=A.EnglishTID select studen.ID,studen.name, Ta.name, Tb.nam
from studen join teacher Ta
on Ta.ID = studen.ChineseTID
join teacher Tb
on Tb.ID = studen.EnglishTID等这些执行速度多回很慢的。
from
(
select A.id,A.name,
ChineTeacher=case when A.ChineseTID=B.id then B.name else null end,
EnglishTeacher =case when A.EnglishTID=B.id then B.name else null end
from student A,teacher B
)A
group by ID,name
,[Chineseteacher] = select name from teacher where ChineseTID = ID
,[Englishteacher] = select name from teacher where EnglishTID = ID
from student