有三张表:
A班学生表
A学号 学生姓名 教师号
Sno Sna fidB班学生表
B 学号 学生姓名 教师号
sno sna tid教师表
教师号 教师姓名
tid tna教师既教A班的学生又教B班的学生(像研究生老师一个老师带几个学生,学生可能在一个班也可能在不同的班),现在求带A班学生人数多于带B班学生人数的教师的教师号和教师姓名,要用group by
A班学生表
A学号 学生姓名 教师号
Sno Sna fidB班学生表
B 学号 学生姓名 教师号
sno sna tid教师表
教师号 教师姓名
tid tna教师既教A班的学生又教B班的学生(像研究生老师一个老师带几个学生,学生可能在一个班也可能在不同的班),现在求带A班学生人数多于带B班学生人数的教师的教师号和教师姓名,要用group by
(select count(a.sno) as sno,fid from
A a group by a.fid) a,
(select count(b.sno) as sno,tid from
B b group by b.tid) b,
jshb c
where a.fid=b.tid and a.fid=c.tid
select a.fid,c.tna from
(select count(a.sno) as sno,fid from
A a group by a.fid) a,
(select count(b.sno) as sno,tid from
B b group by b.tid) b,
jshb c
where a.fid=b.tid and a.fid=c.tid
and a.sno>b.sno
A a group by a.fid) a,
(select count(b.sno) as sno,tid from
B b group by b.tid) b,
jshb c
(select count(a.sno) as sno,fid from
A a group by a.fid) a,
(select count(b.sno) as sno,tid from
B b group by b.tid) b,
jshb c
where a.fid=b.tid and a.fid=c.tid
and count(a.sno)>count(b.sno)
(select count(a.sno) as sno,fid from
A a group by a.fid) a,
(select count(b.sno) as sno,tid from
B b group by b.tid) b,
jshb c
where a.fid=b.tid and a.fid=c.tid
and a.sno>b.sno
你看你的记录是不是对呀!!
select a.fid,c.tna from
(select count(a.sno) as sno,a.fid from
A a group by a.fid) a,
(select count(b.sno) as sno,b.tid from
B b group by b.tid) b,
jshb c
where a.fid=b.tid and a.fid=c.tid
and a.sno>b.sno
其中,A 是A班的表,B是B班的表,jshb是教师的表
Sno fid
1 1
2 1
3 2
4 3
5 5
6 3
B班表记录:
Sno Tid
1 1
2 1
3 2
4 2
5 3
6 4
7 3
8 3
9 5
jshb表记录:
Tid Tna
1 A
2 B
3 C
4 D
5 E