各位牛人,我的表结构如下:
s#(学号) name grade(学期) tuition(学费) receipt#(收费号)
20060001 学一 2 1000
20060001 学一 2 290
20060001 学一 3 1290
20060002 学二 1 1000
20060002 学二 1 290
20060002 学二 2 1290
20060002 学二 3 1290
20060002 学二 4 1290
学生某学期的学费可能分几次交清,有学生后面的学期交了，前面学期没有交,也有学生前面学期交了后面没有交.
我现在想按学生分学期汇总学费,得到如下的结果:
s#(学号) name 一期金额 二期金额 三期金额 四期金额
20060001 学一 NULL 1290 1290 NULL
20060002 学二 1290 1290 1290 1290
请问如何设计sql语句实现呢?软件班本:visual foxpro 8.0 sp1
------解决方案--------------------------------------------------------
select s#(学号),name,sum(iif(grade=1,tuition,0)) as 一期金额 ,
sum(iif(grade=2,tuition,0)) as 二期金额 ,
sum(iif(grade=3,tuition,0)) as 三期金额 ,
sum(iif(grade=4,tuition,0)) as 四期金额
from tt group by s#(学号) ,name
------解决方案--------------------------------------------------------
select sh,name,sum(iif(grade=1,tuition,0)) as 一期金额,sum(iif(grade=2,tuition,0)) as 二期金额,sum(iif(grade=3,tuition,0)) as 三期金额,sum(iif(grade=4,tuition,0)) as 四期金额 from test group by sh order by sh
------解决方案--------------------------------------------------------
select s#(学号),name,
sum(case when grade = 1 then tuition else 0 end) as 一期金额,
sum(case when grade = 2 then tuition else 0 end) as 二期金额,
sum(case when grade = 3 then tuition else 0 end) as 三期金额,
sum(case when grade = 4 then tuition else 0 end) as 四期金额
from 表
group by s#(学号),name