急！经典学生老师问题

[xuanzg]

现有学生表s 字段：s_id, s_name, t_id
老师表t 字段：t_id, t_name, t_other
现在需要用一条语句实现：
查询出所有老师的信息，和他所教的学生个数。
拜托大家了

[yujohny]

select t.t_id,max(t.t_name),max(t.t_other),count(*)
from t inner join s on s.t_id=t.t_id
group by t.t_id

[zosky]

select a.*,count(b.*) from s a,t b where a.t_id=b.t_id

[aierong]

create table 学生表s (s_id int, s_name char(10), t_id int)
create table 老师表t (t_id int, t_name char(10), t_other char(10))

select t_id,t_name,t_other,count(t.*)
from 老师表t as t join 学生表s as s
on t.t_id=s.t_id

[wzh1215]

select a.t_id,a.t_name,a.t_other,b.count from t a inner join
(select t.t_id,count(*) as count
from t,s where t.t_id=s.t_id
group by t.t_id) as b

[wzh1215]

select a.t_id,a.t_name,a.t_other,b.count from t a inner join
(select t.t_id,count(*) as count
from t,s where t.t_id=s.t_id
group by t.t_id) as b
on a.t_id=b.t_id

[xuanzg]

多谢各位捧场，
可是大家的语句都有一个问题，
那就是可以查询出有学生人数的老师，
但是没有学生的老师查询不到，
正确结果应该 没有学生的老师的学生总和应该显示为0

[DigJim]

把 join 改成 left join 啊！

[yujohny]

select t.t_id,max(t.t_name),max(t.t_other),isnull(sum(1),0)
from t left join s on s.t_id=t.t_id
group by t.t_id

[double22822]

select a.t_id,a.t_name,a.t_other,b.count from t a inner join
(select t.t_id,isnull(count(*),0) as count
from t,s where t.t_id=s.t_id
group by t.t_id) as b
on a.t_id=b.t_id
抄袭，请原谅。