求查询语句！在线等待！高分相送 or others

[渔夫]

遇到一个这样的问题：
表一（students）ID,name,sex,birth
表二（teachers）学生选课表 : ID,stuId,subject,score

1、查找学生的所有信息，和部分课程信息，
2、学生信息一个不少，一个也不能多。
3、课程任选一个（无所谓）也就是说设掉一部分数据，保留学生的全部信息。
请各位高手赐教。
mail:[e-mail]

[welyngj]

1.select s.*,stuid,subject ,score from students s left join teachers t on s.[id]=t.[id]
2,select t.stuid, name,sex,birth from students s left join on teachers t on s.id=t.id

[伍子V5]

1\select a.name,a.sex,a.birth,isnull(b.subject,''),isnull(b.score,0) from students a
left join teachers b on a.ID=b.ID

[txlicenhe]

Select a.*,b.subject,c.score from students a
Left Join
(Select stuID,min(id) as id from teachers group by stuID) b on a.id = b.stuID
left join teachers c on b.id = c.id

[aierong]

left join

[zjcxc]

左连接:

select a.*,b.subject,b.score
from students a left join teachers b on a.id=b.stuid

[pengdali]

select a.*,b.subject,b.score
from students a left join teachers b on a.id=b.stuid

或：

select a.*,b.subject,b.score
from teachers b right join students a on a.id=b.stuid

[渔夫]

各位，这里面所有的都不能达到要求，microsoft mssql 根本就不让舍掉数据库记录。所以用作联接，都不能实现。
但是如果在里面采用max 函数会导致select * 不能用。请问有没有不用存储过程的别的办法

[pengdali]

select * from students a left join (
select * from teachers tem where ID=(select max(ID) from teachers where stuId=tem.stuId)
) b on a.id=b.stuId

[maoyesky]

select identity(int,1,1) as nowid,* into #temp from teachers

select a.ID,a.name, a.sex,a.birth,b.ID,b.subject,b.score from students a left join (select ID,stuId,subject,score from teachers where nowid in (select
min(nowid) from teachers group by stuId)) b on a.ID=b.stuID

[maoyesky]

上面寫錯了
select identity(int,1,1) as nowid,* into #temp from teachers

select a.ID,a.name, a.sex,a.birth,b.ID,b.subject,b.score from students a left join (select ID,stuId,subject,score from teachers where nowid in (select
min(nowid) from #temp group by stuId)) b on a.ID=b.stuID

[orcale]

select * from students a,teachers b where
a.id=b.id

[gmlxf]

我想这个苯方法应该可以吧：
select
(select ID from students where ID=a.ID) as ID,
(select name from students where ID=a.ID) as name,
(select sex from students where ID=a.ID) as sex,
(select birth from students where ID)=a.ID as birth,
a.subject,a.score from teachers a

[gmlxf]

select * from students a,teachers b where
a.id=b.id

与
select * from students a left join (
select * from teachers tem where ID=(select max(ID) from teachers where stuId=tem.stuId)
) b on a.id=b.stuId

查出来好象一样

[渔夫]

还不行

[渔夫]

为什么这个问题这么难

[渔夫]

微软公司的bug.