sqlserver left join的on中如何添加多个查询条件??

sweetBug 2008-09-22 03:41:50


create table teacher
(
tid varchar(12) not null,
tname varchar(20) not null,
constraint PK_TEACHER primary key (tid)
)
go

create table student
(
xh varchar(20) not null,
tid varchar(12) null ,
sname varchar(20) not null,
constraint PK_STUDENT primary key (xh)
)
go


create index Relation_11_FK on student (tid)
go

alter table student
add constraint FK_STUDENT_RELATION__TEACHER foreign key (tid)
references teacher (tid)
go




如上,我有两个表,teacher和student.我想用left join查出一个老师下有多少个学生.
如:select teacher.* , student.* from teacher left join student on teacher.tid = student.tid;
但是我想再加上一个条件在后面,如是id为'0001'的老师所带的学生,应该怎么加?
我直接在后面加上..from teacher left join student on teacher.tid = student.tid and teacher.tid = '001'没有任何效果, 它还是会将所有老师的全查出来??
...全文
26096 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
sweetBug 2008-09-22
  • 打赏
  • 举报
回复
谢谢各位啦,居然是这么简单的问题...汗~
再谢,接分!
hyqwan11112 2008-09-22
  • 打赏
  • 举报
回复
"我直接在后面加上..from teacher left join student on teacher.tid = student.tid and teacher.tid = '001'没有任何效果, 它还是会将所有老师的全查出来??"
在on 后边and 添加条件是做连接时用,
此处你想限制老师的tid应该加在where 后边
即:

select teacher.* , student.* from teacher left join student on teacher.tid = student.tid where teacher.tid = '001'

webdou 2008-09-22
  • 打赏
  • 举报
回复
select teacher.* , student.* from teacher left join student on teacher.tid = student.tid
where teacher.tid = '0001'

另外,还有你说是“0001”,怎么条件上写的是‘001’呢,
昵称被占用了 2008-09-22
  • 打赏
  • 举报
回复
select teacher.* , student.* from teacher left join student on teacher.tid = student.tid where  teacher.tid = '001'
wxg22526451 2008-09-22
  • 打赏
  • 举报
回复
用where。。
select teacher.* , student.* from teacher left join student on teacher.tid = student.tid
where teacher.tid = '001'
yrwx001 2008-09-22
  • 打赏
  • 举报
回复
select teacher.* , student.* from teacher left join student on teacher.tid = student.tid
where teacher.tid = '001'

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧