请高手帮修改下sql语句

[fyz95311138]

数据表xuesheng，字段：xuehao,name,banji;
数据表chengji,字段：xuehao,chengji,xueke,xueqi,haoxu;

通过以下sql语句：

select xuesheng.name as 姓名,max(case xueke when '语文' then chengji else 0 end) yuwen,max(case xueke when '数学' then chengji else 0 end) shuxue from xuesheng,chengji where xuesheng.xuehao=chengji.xuehao and xueqi=1 and haoxu=1 and banji='一(1)班' group by name

得到结果集:
姓名 yuwen shuxue
张三 85 90
李四 65 85

我想得到的结果是:
姓名 yuwen shuxue zongfen
张三 85 90 175
李四 65 85 150

就是在原来的sql语句(蓝色部分)的基础上增加一列(zongfen)，并且按zongfen进行降序排序，应该怎么修改这条sql语句呢？谢谢

[q107770540]

select xuesheng.name as 姓名,max(case xueke when '语文' then chengji else 0 end) yuwen,max(case xueke when '数学' then chengji else 0 end) shuxue,sum(chengji) zongfen from xuesheng,chengji where xuesheng.xuehao=chengji.xuehao and xueqi=1 and haoxu=1 and banji='一(1)班' group by name order by zongfen  desc

[wolf_410]

select 姓名,yuwen,shuxue, yuwen+shuxue as zhongfen from (
select xuesheng.name as 姓名,max(case xueke when '语文' then chengji else 0 end) yuwen,max(case xueke when '数学' then chengji else 0 end) shuxue from xuesheng,chengji where xuesheng.xuehao=chengji.xuehao and xueqi=1 and haoxu=1 and banji='一(1)班' group by name)s

[fyz95311138]

谢谢哦，两个都对了，，#2楼的我能看懂，但是#1楼的不太懂，sum(chengji) zongfen ，就是这一句，能说说这一句是怎么得到zongfen的吗？