select f.school,f.grade,sum(f.cnt) from (select a.school,b.grade,count(*) as cnt from school a, grade b, class c, student d, stu_class e where a.id=b.school_id and b.id=c.grade_id and c.id=d.class_id and d.stu_no=e.stu_no group by a.school,b.grade,d.name having count(*)>1) f group by f.school,f.grade
你必须用一个SQL语句实现吗?分步来做的话,效率一定要比一条SQL语句快的多。
libin_ftsafe(子陌红尘) 谢谢你的SQL 我再等待下 看看还有什么其他思路^_^
select g.school_id, t.grade_id, sum(ct) from (select c.grade_id, s.name, count(*) ct from student s, stu_class sc, class c where s.stu_no=sc.stu_no and c.id=sc.class_id having count(*)>1) t, grade g where t.grade_id=g.id group by g.school_id, t.grade_id
1, to : libin_ftsafe(子陌红尘) 优化一下,不要用COUNT(*)2,建好索引,36万不算多
f.school,f.grade,sum(f.cnt)
from
(select
a.school,b.grade,count(*) as cnt
from
school a,
grade b,
class c,
student d,
stu_class e
where
a.id=b.school_id
and
b.id=c.grade_id
and
c.id=d.class_id
and
d.stu_no=e.stu_no
group by
a.school,b.grade,d.name
having count(*)>1) f
group by
f.school,f.grade
from (select c.grade_id, s.name, count(*) ct
from student s, stu_class sc, class c
where s.stu_no=sc.stu_no and c.id=sc.class_id
having count(*)>1) t,
grade g
where t.grade_id=g.id
group by g.school_id, t.grade_id
to : libin_ftsafe(子陌红尘)
优化一下,不要用COUNT(*)2,建好索引,36万不算多