fname fclass fscore1 fscore2
=================
001 语文 80 75
001 数学 90 95
002 语文 75 80
002 数学 80 85select fname,fclass,sum(fscore11),sum(fscore12),sum(fscore21),sum(fscore22)
from
(
select fname,fclass ,
(case fclass when '语文' then fscore1 else 0 ) as fscore11,
(case fclass when '语文' then fscore2 else 0 ) as fscore12,
(case fclass when '数学' then fscore1 else 0 ) as fscore21,
(case fclass when '数学' then fscore2 else 0 ) as fscore22
) t1
group by fname,fclass
=================
001 语文 80 75
001 数学 90 95
002 语文 75 80
002 数学 80 85select fname,fclass,sum(fscore11),sum(fscore12),sum(fscore21),sum(fscore22)
from
(
select fname,fclass ,
(case fclass when '语文' then fscore1 else 0 ) as fscore11,
(case fclass when '语文' then fscore2 else 0 ) as fscore12,
(case fclass when '数学' then fscore1 else 0 ) as fscore21,
(case fclass when '数学' then fscore2 else 0 ) as fscore22
) t1
group by fname,fclass
姓名 科目 上学期分数 下学期分数
=================
001 语文 80 75
001 数学 90 95
002 语文 75 80
002 数学 80 85
是一个表,转换后希望是一个视图,但是查询设计器不支持CASE SQL构造。