求助一条多表SQL 求和语句!

[kmlftp2008]

表1:banji
banji kemu
高三1 语文
高三1 数学
高三1 外语
高三1 综合
高三1 物理
高三2 语文
高三2 数学
高三2 外语
高三2 综合
高三1 化学

表2: chengji
banji kemu chengji
高三1 语文 67
..............
--------------------------------------
现在有sql查询 求出: 各科目分数,语数外总分,语数外+各个班不同的第四门科目的总分,5门总分 在一个查询中输出
请问应该怎么写

[liangCK]

看看.

[昵称被占用了]

select banji,
sum(case kemu when '语文' then chengji end) as 语文,
sum(case kemu when '数学' then chengji end) as 数学,
sum(case kemu when '外语' then chengji end) as 外语,
sum(case kemu when '综合' then chengji end) as 综合,
sum(case kemu when '物理' then chengji end) as 物理,
sum(case kemu when '化学' then chengji end) as 化学,

sum(case when kemu in ('语文','数学','外语') then chengji end) as 语数外总分,
sum(case when kemu in ('语文','数学','外语','物理','化学') then chengji end) as [3+1总分],
sum(chengji) as 总分
from chengji 
group by banji

[懒牛科技]

create table banji
(
	banji varchar(20),
	kemu varchar(30)
)
create table chengji 
(
	banji varchar(20),
	kemu varchar(30),
	chengji int
)
insert into banji 
select N'高三1',N'语文' union all
select N'高三1',N'数学'union all
select N'高三1',N'外语'union all
select N'高三1',N'综合'union all
select N'高三1',N'物理'union all
select N'高三1',N'化学'union all

select N'高三2',N'语文'union all
select N'高三2',N'数学'union all
select N'高三2',N'外语'union all
select N'高三2',N'综合'

insert into chengji 
select N'高三1',N'语文' ,67


select banji,
sum(case kemu when '语文' then chengji end) as 语文,
sum(case kemu when '数学' then chengji end) as 数学,
sum(case kemu when '外语' then chengji end) as 外语,
sum(case kemu when '综合' then chengji end) as 综合,
sum(case kemu when '物理' then chengji end) as 物理,
sum(case kemu when '化学' then chengji end) as 化学,

sum(case when kemu in ('语文','数学','外语') then chengji end) as 语数外总分,
sum(case when kemu in ('语文','数学','外语','物理','化学') then chengji end) as [总分],
sum(chengji) as 总分
from chengji 
group by banji

[huangqing_80]

楼上各位已经回答了

[kmlftp2008]

谢谢 各位大虾 放分了

[kmlftp2008]

请问要是再加一列选课列该怎么弄
比如
banji 语文 数学 外语 选课 选课分数 语数外 3+1 总分
这样的格式输出该这么写啊

[ouyang156]

select sum(case when kemu='语文' then chegnji end) as 语文成绩,
sum(case when kumu='数学' then chengji end) as 数学成绩,
.
.
.
sum(case when kemu in ('语文','数学','外语') then chengji end) as 语数外总分,

sum(case when kumu in ('语文','数学','外语','化学') then chengji end) as 语数外化总分,
sum(case when kumu in ('语文','数学','外语','物理') then chengji end) as 语数外物总分,

sum(chengji) as 五门总分
from chengji