【■■▲●★】这样的统计SQL语句该怎么写？？？？？？？？

[surfl]

T1: classID clasName
0001 01计网
0002 01计软
.....................

T2 : classID,studentID,studentName
0001 200001 王刚
0002 200002 李华
.....................

T3: studentID nWeek nState (其中nstate值为（0,1,2,3）四种情况)
200001 1 0
200001 2 1
200001 1 1
200001 2 2
200001 2 3
200002 3 0
200002 1 3
.....................

现在需要统计生成一张表为
classname studentName nState(0) nState(1) nState(2) nState(3)

【输入的条件为nWeek,例如：nWeek >= 1 And nWeek <=5】

现在需要统计在某一段时间内，所有人的nState四种状态的分别总和。
例如：

classname studentName nState(0) nState(1) nState(2) nState(3)
01计网 王刚 1 2 1 1
01计软 李华 1 0 0 1

这样的SQL的语句该怎么写？？？？

[txlicenhe]

Select T1.classname,t2.studentName,
sum(case when T3.nState = 0 then 1 else 0 end) as nState(0),
sum(case when T3.nState = 1 then 1 else 0 end) as nState(1),
sum(case when T3.nState = 2 then 1 else 0 end) as nState(2),
sum(case when T3.nState = 3 then 1 else 0 end) as nState(3)
from T2 join T1 on T2.classID = T1.classID
join T3 on T2.studentID = T3.studentID
group by T1.classname,t2.studentName

[tiny_yan]

Select T1.classname,t2.studentName,
sum(case when T3.nState = 0 then 1 else 0 end) as nState(0),
sum(case when T3.nState = 1 then 1 else 0 end) as nState(1),
sum(case when T3.nState = 2 then 1 else 0 end) as nState(2),
sum(case when T3.nState = 3 then 1 else 0 end) as nState(3)
from T1,T2,T3 where T2.classID = T1.classID and T2.studentID = T3.studentID and nWeek >= 1 And nWeek <=5
group by T1.classname,T2.studentName

[CCEO]

Select T1.classname,t2.studentName,
sum(case when T3.nState = 0 then 1 else 0 end) as 'nState(0)',
sum(case when T3.nState = 1 then 1 else 0 end) as 'nState(1)',
sum(case when T3.nState = 2 then 1 else 0 end) as 'nState(2)',
sum(case when T3.nState = 3 then 1 else 0 end) as 'nState(3)'
from T2 join T1 on T2.classID = T1.classID
join T3 on T2.studentID = T3.studentID
WHERE T3.nWeek >= 1 And T3.nWeek <=5
group by T1.classname,t2.studentName

[伍子V5]

Select T1.classname,t2.studentName,
sum(case when T3.nState = 0 then 1 else 0 end) as nState(0),
sum(case when T3.nState = 1 then 1 else 0 end) as nState(1),
sum(case when T3.nState = 2 then 1 else 0 end) as nState(2),
sum(case when T3.nState = 3 then 1 else 0 end) as nState(3)
from T1,T2,T3 where T2.classID = T1.classID and T2.studentID = T3.studentID and nWeek >= 1 And nWeek <=5
group by T1.classname,T2.studentName

[zjcxc]

select t1.classname,t2.studentname,
[nState(0)]=sum(case nstate when 0 then 1 end),
[nState(1)]=sum(case nstate when 1 then 1 end),
[nState(2)]=sum(case nstate when 2 then 1 end),
[nState(3)]=sum(case nstate when 3 then 1 end)
from t2
inner join t1 on t2.classid=t1.classid
inner join t3 on t2.studentid=t3.studentid
where t3.nweek between 1 and 5
group by t1.classname,t2.studentname

[CrazyFor]

select studentID
,case when nState=0 then nweek end nState(0)
,case when nState=1 then nweek end nState(1)
,case when nState=2 then nweek end nState(2)
,case when nState=3 then nweek end nState(3)
from T3
group by studentID

这样以后应该没有问题了吧.:)

[pengdali]

select t1.clasName,t2.studentName
,sum(case when nstate=0 then nWeek else 0 end) [nState(0)]
,sum(case when nstate=1 then nWeek else 0 end) [nState(1)]
,sum(case when nstate=2 then nWeek else 0 end) [nState(2)]
,sum(case when nstate=3 then nWeek else 0 end) [nState(3)]
from t3 join t2 on t2.studentID=t3.studentID join t1 on t2.classID=t1.classID
group by t1.classID,t1.classname,t2.studentID,t2.studentName

[wzh1215]

Select T1.classname,T2.studentName,
sum(case when T3.nState = 0 then 1 else 0 end) as 'nState(0)',
sum(case when T3.nState = 1 then 1 else 0 end) as 'nState(1)',
sum(case when T3.nState = 2 then 1 else 0 end) as 'nState(2)',
sum(case when T3.nState = 3 then 1 else 0 end) as 'nState(3)'
from T1 join T2 on T2.classID = T1.classID
join T3 on T2.studentID = T3.studentID
WHERE nWeek >= 1 And nWeek <=5
group by T1.classname,T2.studentName