select id

[aps22]

select id,classid,case courseclass.term when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
end as term, course.xuefen, b.total
from courseclass left join
(select term, sum(xuefen) as total from courseclass,course where courseclass.courseid=course.courseid group by term )
as b on courseclass.term = b.term,
course where courseclass.courseid=course.courseid order by classid,term




id classid term xuefen total
----------- -------- ----- ----------- -----------
1 M0012093 one 2 8
2 M0012093 one 3 8
3 M0012093 two 1 9
4 M0012093 two 3 9
5 Z8934902 one 2 8
6 Z8934902 one 1 8
7 Z8934902 two 2 9
8 Z8934902 two 3 9

上面的SQL语句会出现上面的显示结果，但是我还需要进行一些修改，就是需要根据classid进行分组统计xuefen ，需要显示的结果如下：

id classid term xuefen total
----------- -------- ----- ----------- -----------
1 M0012093 one 2 5
2 M0012093 one 3 5
3 M0012093 two 1 4
4 M0012093 two 3 4
5 Z8934902 one 2 3
6 Z8934902 one 1 3
7 Z8934902 two 2 5
8 Z8934902 two 3 5

拜托了。

[qybao]

你在group by term时,再添加按classid分组试试,在你的基础上修改试试
select id,classid,case courseclass.term when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
end as term, course.xuefen, b.total
from courseclass left join
(select term,classid,sum(xuefen) as total from courseclass,course where courseclass.courseid=course.courseid group by term,classid )
as b on courseclass.term = b.term and courseclass.classid = b.classid,
course where courseclass.courseid=course.courseid order by classid,term

[linyasa007]

同意楼上
select id,classid,case courseclass.term when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
end as term, course.xuefen, b.total
from courseclass left join
(select term,classid,sum(xuefen) as total from courseclass,course where courseclass.courseid=course.courseid group by term,classid )
as b on courseclass.term = b.term and courseclass.classid = b.classid,
course where courseclass.courseid=course.courseid order by classid,term

[愉快的登山者]

在原来的基础上：
select a.id, a.classid, a.term, a.xuefen, b.total
from tablename as a left join
(select claseid, term, sum(xuefen) as total from tablename) as b
on a.term = b.term and a.classid = b.classid

[aps22]

thanx all.
已经采用了 qybao的意见，并且得到了正确的结果。

[daviszhang]

非常同意楼上的，不过这样也行：
select id,classid,case courseclass.term when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
end as term, course.xuefen, c.total
from courseclass left join
course on courseclass.courseid=course.courseid
and courseclass.term =course.term
left join
(select term, sum(xuefen) as total from courseclass a,course b where a.courseid=b.courseid group by a.term,a.classid) c
on courseclass.term = c.term
and courseclass.classid=c.classid
order by classid,term