复杂select，生成结果像报表一样

Question

没说得很清楚，不好意思
根据Student表中的ClassID可以得出一个班的所有学生，
在training中，一个PSetID可以对应有多个CaseID,就是一个问题集合中有多个问题，一个班可以有多个问题集合，CaseID是不重复的，
其实这些ID值都是GUID,为了增加数据方便，先就用char代替了。一个班、一个问题集合、一个问题确定一条记录。
每个学生做问题的操作都会记录在Performance表中，其中TimeLine记录的是操作的时刻，ClassCaseID是记录的这个学生做的是哪个问题，
对应training表的ID，Score是分数，每一步操作后还剩下的分数
Action记录的是操作的类型，分为五种，'finished' 'correct' 'incorrect' 'abort' 和除了这四种外的情况，就是完成、正确、不正确、中止和其它的操作。
只要问题做完，就会有一条Action为finished的记录在里面，比如他第一次就答对了，
就会有两条记录在里面，一条是correct，一条是finished。
没有Action为finished的记录表示这道题没做完，没有上面这四种情况的表示这个学生有参与解答这个问题，但不一定做完了，里面没有相关记录的表示这个学生还没开始解答这个问题。
每个学生只能做一次一个问题。
要统计的就是这五种操作的情况。
统计出来的数据能不能是这样：学生ID  问题ID    分数  答题结果  回答次数  答题情况
学生1   问题1  
学生1   问题2  
学生1   问题3  其中答题结果为没做完，做对，做错
回答次数就是Action为correct或incorrect的个数
答题情况为一次答对（一条correct和一条finished），多次答对（一条或多条incorrect，一条correct和一条finished），多次答错（一条或多条incorrect，一条finished），

Answer 1

学生ID  问题ID    分数  答题结果  回答次数  答题情况
0001    0001      50    做对      1         一次答对
0001    0002      90    做对      2         多次答对
0001    0003      50    做错      2         多次答错
0001    0004      60    没做完    1         无
0001    0005      70    没做完    0         无
0001    0006      70    做对      1         一次答对
0001    0007      70    没做完    1         无
0001    0008      0     没做      0         无
0001    0009      0     没做      0         无
0001    0010      0     没做      0         无
0002    0001      0     没做      0         无
0002    0002      0     没做      0         无
0002    0003      0     没做      0         无
0002    0004      0     没做      0         无
0002    0005      70    没做完    0         无
0002    0006      70    做对      1         一次答对   
0002    0007      70    没做完    1         无
0002    0008      0     没做      0         无
0002    0009      0     没做      0         无
0002    0010      0     没做      0         无

Answer 2

--你的这段代码有错,而且是不是把表搞错了,请你检查一下.
create table Performance (
   StudentID          char(4)     not null,
   TimeLine           datetime    not null,
   ClassCaseID        char(4)     not null,
   Action             tinyint     not null,
   Score              tinyint     not null,
   State              tinyint     not null
)
insert into Student
select '0001','2008-09-30 12:00:00','00001','查看',90,0 union
select '0001','2008-09-30 12:01:00','00001','查看帮助1',80,0 union
select '0001','2008-09-30 12:03:00','00001','查看帮助2',70,0 union
select '0001','2008-09-30 12:05:00','00001','查看帮助3',60,0 union
select '0001','2008-09-30 12:07:00','00001','correct',50,0 union
select '0001','2008-09-30 12:07:01','00001','finished',50,0 union
select '0001','2008-09-30 12:09:01','00002','查看',90,0 union
select '0001','2008-09-30 12:10:01','00002','查看帮助1',80,0 union
select '0001','2008-09-30 12:11:01','00002','incorrect',70,0 union
select '0001','2008-09-30 12:12:01','00002','查看帮助2',60,0 union
select '0001','2008-09-30 12:13:01','00002','correct',50,0 union
select '0001','2008-09-30 12:13:02','00002','finished',90,0 union
select '0001','2008-09-30 12:14:01','00003','查看',90,0 union
select '0001','2008-09-30 12:15:01','00003','查看帮助1',80,0 union
select '0001','2008-09-30 12:16:01','00003','查看帮助2',70,0 union
select '0001','2008-09-30 12:17:01','00003','incorrect',60,0 union
select '0001','2008-09-30 12:18:01','00003','查看帮助3',50,0 union
select '0001','2008-09-30 12:19:01','00003','incorrect',40,0 union
select '0001','2008-09-30 12:20:01','00003','查看帮助3',50,0 union
select '0001','2008-09-30 12:20:02','00003','finished',50,0 union
select '0001','2008-09-30 12:21:02','00004','查看',90,0 union
select '0001','2008-09-30 12:22:02','00004','查看帮助1',80,0 union
select '0001','2008-09-30 12:23:02','00004','查看帮助2',70,0 union
select '0001','2008-09-30 12:24:02','00004','incorrect',60,0 union
select '0001','2008-09-30 12:25:02','00005','查看',90,0 union
select '0001','2008-09-30 12:26:02','00005','查看帮助1',80,0 union
select '0001','2008-09-30 12:27:02','00005','查看帮助2',70,0 union
select '0001','2008-09-30 12:28:02','00006','查看',90,0 union
select '0001','2008-09-30 12:29:02','00006','查看帮助1',80,0 union
select '0001','2008-09-30 12:30:02','00006','correct',70,0 union
select '0001','2008-09-30 12:30:03','00006','finished',70,0 union
select '0001','2008-09-30 12:31:02','00007','查看',90,0 union
select '0001','2008-09-30 12:32:02','00007','查看帮助1',80,0 union
select '0001','2008-09-30 12:33:02','00007','incorrect',80,0 union
select '0001','2008-09-30 12:34:02','00007','查看帮助2',70,0 union
select '0002','2008-09-30 12:35:02','00005','查看',90,0 union
select '0002','2008-09-30 12:36:02','00005','查看帮助1',80,0 union
select '0002','2008-09-30 12:37:02','00005','查看帮助2',70,0 union
select '0002','2008-09-30 12:38:02','00006','查看',90,0 union
select '0002','2008-09-30 12:39:02','00006','查看帮助1',80,0 union
select '0002','2008-09-30 12:40:02','00006','correct',70,0 union
select '0002','2008-09-30 12:40:03','00006','finished',70,0 union
select '0002','2008-09-30 12:41:02','00007','查看',90,0 union
select '0002','2008-09-30 12:42:02','00007','查看帮助1',80,0 union
select '0002','2008-09-30 12:43:02','00007','incorrect',80,0 union
select '0002','2008-09-30 12:44:02','00007','查看帮助2',70,0

Answer 3

Action记录的是操作的类型，分为五种，'finished' 'correct' 'incorrect' 'abort' 
和除了这四种外的情况，就是完成、正确、不正确、中止和其它的操作。这和你提供的数据是否不符合?你给出的数据中存在查看帮助1,查看帮助2,查看帮助3,这些内容是不是你说的"其他的操作"?

Answer 4

确实很复杂@_@
;with tmp as
(
select a.StudentID,c.ClassCaseID,c.Score,c.Action
from student a join training b on a.ClassID=b.Classid
join Performance c on a.Studentid=c.Studentid and b.caseid=c.ClassCaseID
) select StudentID,ClassCaseID,
答题结果=case ds when 3 then '做对' when 2 then '做对' when 1 then '做错' else '没做完' end,
回答次数=dc,
答题情况=case ds when 3 then '一次答对' when 2 then '多次答对' when 1 then '多次答错' end 
from
(
select t.StudentID,t.ClassCaseID,
--x.cc,x.fc,x.inc,
dc=(select count(1) from tmp where StudentID=t.StudentID and ClassCaseID=t.ClassCaseID
and [Action] in('correct','incorrect'))
,ds=
max(case 
when x.inc=0 and x.cc=1 and x.fc=1 then 3--一次答对
when x.inc>=1 and x.fc=1 and x.cc=1 then 2--多次答对
when x.inc>=1 and x.fc=1 then 1--多次答错
end)
--,[Action] 
from tmp t
join
(
select Studentid,ClassCaseID,
cc=sum(case [Action] when 'correct' then 1 else 0 end),--正确次数
fc=sum(case [Action] when 'finished' then 1 else 0 end),--完成次数
inc=sum(case [Action] when 'incorrect' then 1 else 0 end)--错误次数
from tmp group by StudentID,ClassCaseID
) x
on t.StudentID=x.StudentID and t.ClassCaseID=x.ClassCaseID
group by t.StudentID,t.ClassCaseID
,x.cc,x.fc,x.inc
) m
/*
StudentID ClassCaseID 答题结果   回答次数        答题情况
--------- ----------- ------ ----------- --------
0001      0001        做对     1           一次答对
0001      0002        做对     2           多次答对
0001      0003        做错     2           多次答错
0001      0004        没做完    1           NULL
0001      0005        没做完    0           NULL
0001      0006        做对     1           一次答对
0001      0007        没做完    1           NULL
0002      0005        没做完    0           NULL
0002      0006        做对     1           一次答对
*/

Answer 5

create table Performance (
   StudentID          char(4)     not null,
   TimeLine           datetime    not null,
   ClassCaseID        char(5)     not null,
   Action             char(20)    not null,
   Score              tinyint     not null,
   State              tinyint     not null
)
insert into Performance
select '0001','2008-09-30 12:00:00.000','00001','查看',90,0 union
select '0001','2008-09-30 12:01:00','00001','查看帮助1',80,0 union
select '0001','2008-09-30 12:03:00','00001','查看帮助2',70,0 union
select '0001','2008-09-30 12:05:00','00001','查看帮助3',60,0 union
select '0001','2008-09-30 12:07:00','00001','correct',50,0 union
select '0001','2008-09-30 12:07:01','00001','finished',50,0 union
select '0001','2008-09-30 12:09:01','00002','查看',90,0 union
select '0001','2008-09-30 12:10:01','00002','查看帮助1',80,0 union
select '0001','2008-09-30 12:11:01','00002','incorrect',70,0 union
select '0001','2008-09-30 12:12:01','00002','查看帮助2',60,0 union
select '0001','2008-09-30 12:13:01','00002','correct',50,0 union
select '0001','2008-09-30 12:13:02','00002','finished',90,0 union
select '0001','2008-09-30 12:14:01','00003','查看',90,0 union
select '0001','2008-09-30 12:15:01','00003','查看帮助1',80,0 union
select '0001','2008-09-30 12:16:01','00003','查看帮助2',70,0 union
select '0001','2008-09-30 12:17:01','00003','incorrect',60,0 union
select '0001','2008-09-30 12:18:01','00003','查看帮助3',50,0 union
select '0001','2008-09-30 12:19:01','00003','incorrect',40,0 union
select '0001','2008-09-30 12:20:01','00003','查看帮助3',50,0 union
select '0001','2008-09-30 12:20:02','00003','finished',50,0 union
select '0001','2008-09-30 12:21:02','00004','查看',90,0 union
select '0001','2008-09-30 12:22:02','00004','查看帮助1',80,0 union
select '0001','2008-09-30 12:23:02','00004','查看帮助2',70,0 union
select '0001','2008-09-30 12:24:02','00004','incorrect',60,0 union
select '0001','2008-09-30 12:25:02','00005','查看',90,0 union
select '0001','2008-09-30 12:26:02','00005','查看帮助1',80,0 union
select '0001','2008-09-30 12:27:02','00005','查看帮助2',70,0 union
select '0001','2008-09-30 12:28:02','00006','查看',90,0 union
select '0001','2008-09-30 12:29:02','00006','查看帮助1',80,0 union
select '0001','2008-09-30 12:30:02','00006','correct',70,0 union
select '0001','2008-09-30 12:30:03','00006','finished',70,0 union
select '0001','2008-09-30 12:31:02','00007','查看',90,0 union
select '0001','2008-09-30 12:32:02','00007','查看帮助1',80,0 union
select '0001','2008-09-30 12:33:02','00007','incorrect',80,0 union
select '0001','2008-09-30 12:34:02','00007','查看帮助2',70,0 union
select '0002','2008-09-30 12:35:02','00005','查看',90,0 union
select '0002','2008-09-30 12:36:02','00005','查看帮助1',80,0 union
select '0002','2008-09-30 12:37:02','00005','查看帮助2',70,0 union
select '0002','2008-09-30 12:38:02','00006','查看',90,0 union
select '0002','2008-09-30 12:39:02','00006','查看帮助1',80,0 union
select '0002','2008-09-30 12:40:02','00006','correct',70,0 union
select '0002','2008-09-30 12:40:03','00006','finished',70,0 union
select '0002','2008-09-30 12:41:02','00007','查看',90,0 union
select '0002','2008-09-30 12:42:02','00007','查看帮助1',80,0 union
select '0002','2008-09-30 12:43:02','00007','incorrect',80,0 union
select '0002','2008-09-30 12:44:02','00007','查看帮助2',70,0

Answer 6

;with tmp as
(
select a.StudentID,c.ClassCaseID,c.Score,c.Action
from Student a join training b on a.ClassID=b.ClassID
join Performance c on a.StudentID=c.StudentID and b.ID=c.ClassCaseID
) select StudentID,ClassCaseID,
答题结果=case ds when 3 then '做对' when 2 then '做对' when 1 then '做错' else '没做完' end,
回答次数=dc,
答题情况=case ds when 3 then '一次答对' when 2 then '多次答对' when 1 then '多次答错' end 
from
(
select t.StudentID,t.ClassCaseID,
--x.cc,x.fc,x.inc,
dc=(select count(1) from tmp where StudentID=t.StudentID and ClassCaseID=t.ClassCaseID
and [Action] in('correct','incorrect'))
,ds=
max(case 
when x.inc=0 and x.cc=1 and x.fc=1 then 3--一次答对
when x.inc>=1 and x.fc=1 and x.cc=1 then 2--多次答对
when x.inc>=1 and x.fc=1 then 1--多次答错
end)
--,[Action] 
from tmp t
join
(
select StudentID,ClassCaseID,
cc=sum(case [Action] when 'correct' then 1 else 0 end),--正确次数
fc=sum(case [Action] when 'finished' then 1 else 0 end),--完成次数
inc=sum(case [Action] when 'incorrect' then 1 else 0 end)--错误次数
from tmp group by StudentID,ClassCaseID
) x
on t.StudentID=x.StudentID and t.ClassCaseID=x.ClassCaseID
group by t.StudentID,t.ClassCaseID
,x.cc,x.fc,x.inc
) m0001 00001 做对 1 一次答对
0001 00002 做对 2 多次答对
0001 00003 做错 2 多次答错
0001 00004 没做完 1 NULL
0001 00005 没做完 0 NULL
0001 00006 做对 1 一次答对
0001 00007 没做完 1 NULL
0002 00005 没做完 0 NULL
0002 00006 做对 1 一次答对
0002 00007 没做完 1 NULL结果是有了，代码正在研究当中，非常感谢了。
还有一是分数没有出来，二是没有列出所有问题出来，学生0001的8、9问题没有做，学生0002的1、2、3、4、8、9没有做，能不能做到也列出来呢，如果不能就算了，怎么样才能按Performance的TimeLine排序呢。
再次感谢了。

Answer 7

;with tmp as--三表关联
(
select a.StudentID,ClassCaseID=b.CaseID,c.Score,c.Action
from student a join training b on a.ClassID=b.Classid
join Performance c on a.Studentid=c.Studentid and b.caseid=right(c.ClassCaseID,4)--ClassCaseID用5位的截取后面4位进行关联
) select StudentID,ClassCaseID,
--score,
答题结果=case ds when 3 then '做对' when 2 then '做对' when 1 then '做错' else '没做完' end,
回答次数=dc,
答题情况=case ds when 3 then '一次答对' when 2 then '多次答对' when 1 then '多次答错' end 
from
(
select t.StudentID,t.ClassCaseID,
--t.score,
--x.cc,x.fc,x.inc,
dc=(select count(1) from tmp where StudentID=t.StudentID and ClassCaseID=t.ClassCaseID
and [Action] in('correct','incorrect'))
,ds=
max(case 
when x.inc=0 and x.cc=1 and x.fc=1 then 3--一次答对
when x.inc>=1 and x.fc=1 and x.cc=1 then 2--多次答对
when x.inc>=1 and x.fc=1 then 1--多次答错
end)
--,[Action] 
from tmp t
join
(
select Studentid,ClassCaseID,
cc=sum(case [Action] when 'correct' then 1 else 0 end),--正确次数
fc=sum(case [Action] when 'finished' then 1 else 0 end),--完成次数
inc=sum(case [Action] when 'incorrect' then 1 else 0 end)--错误次数
from tmp group by StudentID,ClassCaseID
) x
on t.StudentID=x.StudentID and t.ClassCaseID=x.ClassCaseID
group by t.StudentID,t.ClassCaseID
,x.cc,x.fc,x.inc
--,t.score
) m
/*
StudentID ClassCaseID 答题结果   回答次数        答题情况
--------- ----------- ------ ----------- --------
0001      0001        做对     1           一次答对
0001      0002        做对     2           多次答对
0001      0003        做错     2           多次答错
0001      0004        没做完    1           NULL
0001      0005        没做完    0           NULL
0001      0006        做对     1           一次答对
0001      0007        没做完    1           NULL
0002      0005        没做完    0           NULL
0002      0006        做对     1           一次答对
0002      0007        没做完    1           NULL
*/

Answer 8

现在数据不完整，连表改成left join ,
分数还没出来，也没用到时间。。
;with tmp as--三表关联
(
select a.StudentID,ClassCaseID=b.CaseID,c.Score,c.Action
from student a join training b on a.ClassID=b.Classid
left join Performance c on a.Studentid=c.Studentid and b.caseid=right(c.ClassCaseID,4)
) 

Answer 9

20道题的结果/*
StudentID ClassCaseID 答题结果   回答次数        答题情况
--------- ----------- ------ ----------- --------
0001      0001        做对     1           一次答对
0001      0002        做对     2           多次答对
0001      0003        做错     2           多次答错
0001      0004        没做完    1           NULL
0001      0005        没做完    0           NULL
0001      0006        做对     1           一次答对
0001      0007        没做完    1           NULL
0001      0008        没做完    0           NULL
0001      0009        没做完    0           NULL
--...
0001      0018        没做完    0           NULL
0001      0019        没做完    0           NULL
0001      0020        没做完    0           NULL
0002      0001        没做完    0           NULL
0002      0002        没做完    0           NULL
0002      0003        没做完    0           NULL
0002      0004        没做完    0           NULL
0002      0005        没做完    0           NULL
0002      0006        做对     1           一次答对
0002      0007        没做完    1           NULL
0002      0008        没做完    0           NULL
0002      0009        没做完    0           NULL
--...
0002      0018        没做完    0           NULL
0002      0019        没做完    0           NULL
0002      0020        没做完    0           NULL
0003      0001        没做完    0           NULL
0003      0002        没做完    0           NULL
0003      0003        没做完    0           NULL
0003      0004        没做完    0           NULL
--...
*/

Answer 10

;with tmp as--三表关联
(
select a.StudentID,ClassCaseID=b.CaseID,c.Score,c.Action,c.TimeLine
from student a join training b on a.ClassID=b.Classid
left join Performance c on a.Studentid=c.Studentid and b.caseid=right(c.ClassCaseID,4)
) select StudentID,ClassCaseID,
score=
case when ds in(3,2) then --做对
(select isnull(score,0) from tmp where Studentid=m.Studentid and ClassCaseID=m.ClassCaseID
and [action]='finished')
when ds=1 then --做错
(select min(isnull(score,0)) from tmp where Studentid=m.Studentid and ClassCaseID=m.ClassCaseID
and [action] ='incorrect')
else --没做完
(select top 1 isnull(score,0) from tmp where Studentid=m.Studentid and ClassCaseID=m.ClassCaseID
order by TimeLine desc)
end,答题结果=case ds when 3 then '做对' when 2 then '做对' when 1 then '做错' else '没做完' end,
回答次数=dc,
答题情况=case ds when 3 then '一次答对' when 2 then '多次答对' when 1 then '多次答错' end 
from
(
select t.StudentID,t.ClassCaseID,
--t.score,
--x.cc,x.fc,x.inc,
dc=(select count(1) from tmp where StudentID=t.StudentID and ClassCaseID=t.ClassCaseID
and [Action] in('correct','incorrect'))
,ds=
max(case 
when x.inc=0 and x.cc=1 and x.fc=1 then 3--一次答对
when x.inc>=1 and x.fc=1 and x.cc=1 then 2--多次答对
when x.inc>=1 and x.fc=1 then 1--多次答错
end)
--,[Action] 
from tmp t
join
(
select Studentid,ClassCaseID,
cc=sum(case [Action] when 'correct' then 1 else 0 end),--正确次数
fc=sum(case [Action] when 'finished' then 1 else 0 end),--完成次数
inc=sum(case [Action] when 'incorrect' then 1 else 0 end)--错误次数
from tmp group by StudentID,ClassCaseID
) x
on t.StudentID=x.StudentID and t.ClassCaseID=x.ClassCaseID
group by t.StudentID,t.ClassCaseID
,x.cc,x.fc,x.inc
--,t.score
) m/*
StudentID ClassCaseID score 答题结果   回答次数        答题情况
--------- ----------- ----- ------ ----------- --------
0001      0001        50    做对     1           一次答对
0001      0002        90    做对     2           多次答对
0001      0003        40    做错     2           多次答错
0001      0004        60    没做完    1           NULL
0001      0005        70    没做完    0           NULL
0001      0006        70    做对     1           一次答对
0001      0007        70    没做完    1           NULL
0001      0008        0     没做完    0           NULL
0001      0009        0     没做完    0           NULL
--...
0001      0018        0     没做完    0           NULL
0001      0019        0     没做完    0           NULL
0001      0020        0     没做完    0           NULL
0002      0001        0     没做完    0           NULL
0002      0002        0     没做完    0           NULL
0002      0003        0     没做完    0           NULL
0002      0004        0     没做完    0           NULL
0002      0005        70    没做完    0           NULL
0002      0006        70    做对     1           一次答对
0002      0007        70    没做完    1           NULL
0002      0008        0     没做完    0           NULL
0002      0009        0     没做完    0           NULL
--...
0002      0019        0     没做完    0           NULL
0002      0020        0     没做完    0           NULL
0003      0001        0     没做完    0           NULL
0003      0002        0     没做完    0           NULL
0003      0003        0     没做完    0           NULL
0003      0004        0     没做完    0           NULL
0003      0005        0     没做完    0           NULL
0003      0006        0     没做完    0           NULL
--...
*/

Answer 11

select StudentID,ClassCaseID,
score=
case when ds in(3,2,1) then --做完
(select isnull(score,0) from tmp where Studentid=m.Studentid and ClassCaseID=m.ClassCaseID
and [action]='finished')
else --没做完
(select top 1 isnull(score,0) from tmp where Studentid=m.Studentid and ClassCaseID=m.ClassCaseID
order by TimeLine desc)
end,答题结果=case ds when 3 then '做对' when 2 then '做对' when 1 then '做错' else '没做完' end,
回答次数=dc,
答题情况=case ds when 3 then '一次答对' when 2 then '多次答对' when 1 then '多次答错' end 
from
(
select t.StudentID,t.ClassCaseID,
--t.score,
--x.cc,x.fc,x.inc,
dc=(select count(1) from tmp where StudentID=t.StudentID and ClassCaseID=t.ClassCaseID
and [Action] in('correct','incorrect'))
,ds=
max(case 
when x.inc=0 and x.cc=1 and x.fc=1 then 3--一次答对
when x.inc>=1 and x.fc=1 and x.cc=1 then 2--多次答对
when x.inc>=1 and x.fc=1 then 1--多次答错
end)
--,[Action] 
from tmp t
join
(
select Studentid,ClassCaseID,
cc=sum(case [Action] when 'correct' then 1 else 0 end),--正确次数
fc=sum(case [Action] when 'finished' then 1 else 0 end),--完成次数
inc=sum(case [Action] when 'incorrect' then 1 else 0 end)--错误次数
from tmp group by StudentID,ClassCaseID
) x
on t.StudentID=x.StudentID and t.ClassCaseID=x.ClassCaseID
group by t.StudentID,t.ClassCaseID
,x.cc,x.fc,x.inc
--,t.score
) m/*
StudentID ClassCaseID score 答题结果   回答次数        答题情况
--------- ----------- ----- ------ ----------- --------
0001      0001        50    做对     1           一次答对
0001      0002        90    做对     2           多次答对
0001      0003        50    做错     2           多次答错
0001      0004        60    没做完    1           NULL
0001      0005        70    没做完    0           NULL
0001      0006        70    做对     1           一次答对
0001      0007        70    没做完    1           NULL
0001      0008        0     没做完    0           NULL
0001      0009        0     没做完    0           NULL
--...
0001      0018        0     没做完    0           NULL
0001      0019        0     没做完    0           NULL
0001      0020        0     没做完    0           NULL
0002      0001        0     没做完    0           NULL
0002      0002        0     没做完    0           NULL
0002      0003        0     没做完    0           NULL
0002      0004        0     没做完    0           NULL
0002      0005        70    没做完    0           NULL
0002      0006        70    做对     1           一次答对
0002      0007        70    没做完    1           NULL
0002      0008        0     没做完    0           NULL
0002      0009        0     没做完    0           NULL
--...
0002      0019        0     没做完    0           NULL
0002      0020        0     没做完    0           NULL
0003      0001        0     没做完    0           NULL
0003      0002        0     没做完    0           NULL
0003      0003        0     没做完    0           NULL
0003      0004        0     没做完    0           NULL
0003      0005        0     没做完    0           NULL
0003      0006        0     没做完    0           NULL
--...
*/

Answer 12

根据Student表中的ClassID可以得出一个班的所有学生，
在training中，一个PSetID可以对应有多个CaseID,就是一个问题集合中有多个集合，一个班可以有多个问题集合，CaseID是不重复的，
其实这些ID值都是GUID,为了增加数据方便，先就用char代替了。这个表中的ID可以唯一确定一个问题集合里的一个问题。
每个学生做问题的操作都会记录在Performance表中，其中TimeLine记录的是操作的时刻，ClassCaseID是记录的这个学生做的是哪个问题，
对应training表的ID，Score是分数，每一步操作后还剩下的分数
Action记录的是操作的类型，分为五种，'finished' 'correct' 'incorrect' 'abort' 和除了这四种外的情况，就是完成、正确、不正确、中止和其它的操作。
不是这四种情况的就表示这个问题没做完。只要问题做完，就会有一条Action为finished的记录在里面，比如他第一次就答对了，
就会有两条记录在里面，一条是correct，一条是finished。
没有Action为finished的记录表示这道题没做完，没有上面这四种情况的表示这个学生没有解答这个问题。每个学生只能做一次一个问题。
要统计的就是这五种操作的情况。
统计出来的数据能不能是这样：学生ID  问题ID    分数  答题结果  回答次数  答题情况
学生1   问题1  
学生1   问题2  
学生1   问题3  其中答题结果为没做完，做对，做错
回答次数就是Action为correct或incorrect的个数
答题情况为一次答对（一条correct和一条finished），多次答对（一条或多条incorrect，一条correct和一条finished），多次答错（一条或多条incorrect，一条finished），
create table StudentClass (
   ClassID            char(4)     not null,
   StudentID          char(4)     not null,
   Online             tinyint     not null,
   State              tinyint     not null
)
insert into StudentClass
select '0001','0001',0,0 union
select '0001','0002',0,0 union 
select '0001','0003',0,0 union 
select '0001','0004',0,0 union 
select '0001','0005',0,0 union 
select '0002','0001',0,0 union 
select '0002','0002',0,0 union 
select '0002','0003',0,0 union 
select '0002','0004',0,0 union 
select '0002','0005',0,0  create table ClassCase (
   ID                 char(5)     not null,
   ClassID            char(4)     not null,
   CaseID             char(4)     not null,
   PSetID             char(4)     not null,
   State              tinyint     not null
)
insert into ClassCase
select '00001','0001','0001','0001',0 union
select '00002','0001','0002','0001',0 union
select '00003','0001','0003','0001',0 union
select '00004','0001','0004','0001',0 union
select '00005','0001','0005','0001',0 union
select '00006','0001','0006','0001',0 union
select '00007','0001','0007','0001',0 union
select '00008','0001','0008','0001',0 union
select '00009','0001','0009','0001',0 union
select '00010','0001','0010','0001',0 union
select '00011','0001','0011','0002',0 union
select '00012','0001','0012','0002',0 union
select '00013','0001','0013','0002',0 union
select '00014','0001','0014','0002',0 union
select '00015','0001','0015','0002',0 union
select '00016','0001','0016','0002',0 union
select '00017','0001','0017','0002',0 union
select '00018','0001','0018','0002',0 union
select '00019','0001','0019','0002',0 union
select '00020','0002','0020','0002',0 create table StudentCase (
   StudentID          char(4)     not null,
   TimeLine           datetime    not null,
   ClassCaseID        char(5)     not null,
   Action             char(20)    not null,
   Score              tinyint     not null,
   State              tinyint     not null
)
insert into StudentCase
select '0001','2008-09-30 12:00:00','00001','查看',90,0 union
select '0001','2008-09-30 12:01:00','00001','查看帮助1',80,0 union
select '0001','2008-09-30 12:03:00','00001','查看帮助2',70,0 union
select '0001','2008-09-30 12:05:00','00001','查看帮助3',60,0 union
select '0001','2008-09-30 12:07:00','00001','correct',50,0 union
select '0001','2008-09-30 12:07:01','00001','finished',50,0 union
select '0001','2008-09-30 12:09:01','00002','查看',90,0 union
select '0001','2008-09-30 12:10:01','00002','查看帮助1',80,0 union
select '0001','2008-09-30 12:11:01','00002','incorrect',70,0 union
select '0001','2008-09-30 12:12:01','00002','查看帮助2',60,0 union
select '0001','2008-09-30 12:13:01','00002','correct',50,0 union
select '0001','2008-09-30 12:13:02','00002','finished',40,0 union
select '0001','2008-09-30 12:14:01','00003','查看',90,0 union
select '0001','2008-09-30 12:15:01','00003','查看帮助1',80,0 union
select '0001','2008-09-30 12:16:01','00003','查看帮助2',70,0 union
select '0001','2008-09-30 12:17:01','00003','incorrect',60,0 union
select '0001','2008-09-30 12:18:01','00003','查看帮助3',50,0 union
select '0001','2008-09-30 12:19:01','00003','incorrect',40,0 union
select '0001','2008-09-30 12:20:01','00003','查看帮助3',50,0 union
select '0001','2008-09-30 12:20:02','00003','finished',50,0 union
select '0001','2008-09-30 12:21:02','00004','查看',90,0 union
select '0001','2008-09-30 12:22:02','00004','查看帮助1',80,0 union
select '0001','2008-09-30 12:23:02','00004','查看帮助2',70,0 union
select '0001','2008-09-30 12:24:02','00004','incorrect',60,0 union
select '0001','2008-09-30 12:25:02','00005','查看',90,0 union
select '0001','2008-09-30 12:26:02','00005','查看帮助1',80,0 union
select '0001','2008-09-30 12:27:02','00005','查看帮助2',70,0 union
select '0001','2008-09-30 12:28:02','00006','查看',90,0 union
select '0001','2008-09-30 12:29:02','00006','查看帮助1',80,0 union
select '0001','2008-09-30 12:30:02','00006','correct',70,0 union
select '0001','2008-09-30 12:30:03','00006','finished',70,0 union
select '0001','2008-09-30 12:31:02','00007','查看',90,0 union
select '0001','2008-09-30 12:32:02','00007','查看帮助1',80,0 union
select '0001','2008-09-30 12:33:02','00007','incorrect',80,0 union
select '0001','2008-09-30 12:34:02','00007','查看帮助2',70,0 union
select '0002','2008-09-30 12:35:02','00005','查看',90,0 union
select '0002','2008-09-30 12:36:02','00005','查看帮助1',80,0 union
select '0002','2008-09-30 12:37:02','00005','查看帮助2',70,0 union
select '0002','2008-09-30 12:38:02','00006','查看',90,0 union
select '0002','2008-09-30 12:39:02','00006','查看帮助1',80,0 union
select '0002','2008-09-30 12:40:02','00006','correct',70,0 union
select '0002','2008-09-30 12:40:03','00006','finished',70,0 union
select '0002','2008-09-30 12:41:02','00007','查看',90,0 union
select '0002','2008-09-30 12:42:02','00007','查看帮助1',80,0 union
select '0002','2008-09-30 12:43:02','00007','incorrect',80,0 union
select '0002','2008-09-30 12:44:02','00007','查看帮助2',70,0 
;with tmp as--三表关联
(
select a.StudentID,ClassCaseID=b.CaseID,c.Score,c.Action,c.TimeLine
from StudentClass a join ClassCase b on a.ClassID=b.ClassID
left join StudentCase c on a.StudentID=c.StudentID and b.ID=c.ClassCaseID
) select StudentID,ClassCaseID,
score=
case when ds in(3,2) then --做对
(select isnull(Score,0) from tmp where StudentID=m.StudentID and ClassCaseID=m.ClassCaseID
and [Action]='finished')
when ds = 1 then --做错
(select min(isnull(Score,0)) from tmp where StudentID=m.StudentID and ClassCaseID=m.ClassCaseID
and [Action] ='incorrect')
else --没做完
(select top 1 isnull(Score,0) from tmp where StudentID=m.StudentID and ClassCaseID=m.ClassCaseID
order by TimeLine desc)
end,
--
答题结果=case ds when 3 then '做对' when 2 then '做对' when 1 then '做错' else '没做完' end,
回答次数=dc,
答题情况=case ds when 3 then '一次答对' when 2 then '多次答对' when 1 then '多次答错' end 
from
(
select t.StudentID,t.ClassCaseID,
--t.score,
--x.cc,x.fc,x.inc,
dc=(select count(1) from tmp where StudentID=t.StudentID and ClassCaseID=t.ClassCaseID
and [Action] in('correct','incorrect'))
,ds=
max(case 
when x.inc=0 and x.cc=1 and x.fc=1 then 3--一次答对
when x.inc>=1 and x.fc=1 and x.cc=1 then 2--多次答对
when x.inc>=1 and x.fc=1 then 1--多次答错
end)
--,[Action] 
from tmp t
join
(
select StudentID,ClassCaseID,
cc=sum(case [Action] when 'correct' then 1 else 0 end),--正确次数
fc=sum(case [Action] when 'finished' then 1 else 0 end),--完成次数
inc=sum(case [Action] when 'incorrect' then 1 else 0 end)--错误次数
from tmp group by StudentID,ClassCaseID
) x
on t.StudentID=x.StudentID and t.ClassCaseID=x.ClassCaseID
group by t.StudentID,t.ClassCaseID
,x.cc,x.fc,x.inc
--,t.score
) m
稍微修改了一下，你用上面的数据帮我看一下，没什么变化的，就是改了你说的几个错误。我想统计出没做和没做完的数据，你把没做和没做完都归在了没做完里面，我弄了好久也没弄出来，你再帮我看看吧，谢谢了。
国庆快乐！！！

Answer 13

;with tmp as--三表关联
(
select a.StudentID,ClassCaseID=b.CaseID,c.Score,c.Action,c.TimeLine
from StudentClass a join ClassCase b on a.ClassID=b.ClassID
left join StudentCase c on a.StudentID=c.StudentID and b.ID=c.ClassCaseID
) select StudentID,ClassCaseID,
score=
case when ds in(3,2,1) then --做完
(select isnull(Score,0) from tmp where StudentID=m.StudentID and ClassCaseID=m.ClassCaseID
and [Action]='finished')
else --没做完或没做
(select top 1 isnull(Score,0) from tmp where StudentID=m.StudentID and ClassCaseID=m.ClassCaseID
order by TimeLine desc)
end,
--
答题结果=case ds when 3 then '做对' when 2 then '做对' when 1 then '做错' when 0 then '没做完' else '没做' end,
回答次数=dc,
答题情况=case ds when 3 then '一次答对' when 2 then '多次答对' when 1 then '多次答错' else '' end 
from
(
select t.StudentID,t.ClassCaseID,
--t.score,
--x.cc,x.fc,x.inc,
dc=(select count(1) from tmp where StudentID=t.StudentID and ClassCaseID=t.ClassCaseID
and [Action] in('correct','incorrect'))
,ds=
max(case 
when x.inc=0 and x.cc=1 and x.fc=1 then 3--一次答对
when x.inc>=1 and x.fc=1 and x.cc=1 then 2--多次答对
when x.inc>=1 and x.fc=1 then 1--多次答错
when x.inc+x.cc+x.ac+x.oc>0 and x.fc=0 then 0--没做完
when x.inc+x.cc+x.fc+x.ac=0 and x.oc>=0 then -1--没做
end)
--,[Action] 
from tmp t
join
(
select StudentID,ClassCaseID,
cc=sum(case [Action] when 'correct' then 1 else 0 end),--正确次数
fc=sum(case [Action] when 'finished' then 1 else 0 end),--完成次数
inc=sum(case [Action] when 'incorrect' then 1 else 0 end),--错误次数
ac=sum(case [Action] when 'abort' then 1 else 0 end),--中止次数
oc=sum(case when [Action] not in('correct','finished','incorrect','abort') then 1 else 0 end)--其它操作次数
from tmp group by StudentID,ClassCaseID
) x
on t.StudentID=x.StudentID and t.ClassCaseID=x.ClassCaseID
group by t.StudentID,t.ClassCaseID
,x.cc,x.fc,x.inc
--,t.score
) m/*
StudentID ClassCaseID score 答题结果   回答次数        答题情况
--------- ----------- ----- ------ ----------- --------
0001      0001        50    做对     1           一次答对
0001      0002        40    做对     2           多次答对
0001      0003        50    做错     2           多次答错
0001      0004        60    没做完    1           
0001      0005        70    没做完    0           
0001      0006        70    做对     1           一次答对
0001      0007        70    没做完    1           
0001      0008        0     没做     0           
0001      0009        0     没做     0           
--...        
0001      0018        0     没做     0           
0001      0019        0     没做     0           
0001      0020        0     没做     0           
0002      0001        0     没做     0           
0002      0002        0     没做     0           
0002      0003        0     没做     0           
0002      0004        0     没做     0           
0002      0005        70    没做完    0           
0002      0006        70    做对     1           一次答对
0002      0007        70    没做完    1           
0002      0008        0     没做     0           
0002      0009        0     没做     0           
--...          
0002      0018        0     没做     0           
0002      0019        0     没做     0           
0002      0020        0     没做     0           
0003      0001        0     没做     0           
0003      0002        0     没做     0           
0003      0003        0     没做     0           
0003      0004        0     没做     0           
0003      0005        0     没做     0           
0003      0006        0     没做     0           
--...
*/

Answer 14

字段自已再改下别名。
/*
学生ID 问题ID 分数   答题结果   回答次数        答题情况
---- ---- ---- ------ ----------- --------
0001      0001        50    做对     1           一次答对
0001      0002        40    做对     2           多次答对
0001      0003        50    做错     2           多次答错
0001      0004        60    没做完    1           
0001      0005        70    没做完    0           
0001      0006        70    做对     1           一次答对
0001      0007        70    没做完    1           
0001      0008        0     没做     0           
0001      0009        0     没做     0           
--...        
0001      0018        0     没做     0           
0001      0019        0     没做     0           
0001      0020        0     没做     0           
0002      0001        0     没做     0           
0002      0002        0     没做     0           
0002      0003        0     没做     0           
0002      0004        0     没做     0           
0002      0005        70    没做完    0           
0002      0006        70    做对     1           一次答对
0002      0007        70    没做完    1           
0002      0008        0     没做     0           
0002      0009        0     没做     0           
--...          
0002      0018        0     没做     0           
0002      0019        0     没做     0           
0002      0020        0     没做     0           
0003      0001        0     没做     0           
0003      0002        0     没做     0           
0003      0003        0     没做     0           
0003      0004        0     没做     0           
0003      0005        0     没做     0           
0003      0006        0     没做     0           
--...
*/

Answer 15

;with tmp as--三表关联
(
select a.StudentID,ClassCaseID=b.CaseID,c.Score,c.Action,c.TimeLine
from StudentClass a join ClassCase b on a.ClassID=b.ClassID
left join StudentCase c on a.StudentID=c.StudentID and b.ID=c.ClassCaseID
) select 学生ID=StudentID,问题ID=ClassCaseID,
分数=
case when ds in(3,2,1) then --做完
(select isnull(Score,0) from tmp where StudentID=m.StudentID and ClassCaseID=m.ClassCaseID
and [Action]='finished')
else --没做完或没做
(select top 1 isnull(Score,0) from tmp where StudentID=m.StudentID and ClassCaseID=m.ClassCaseID
order by TimeLine desc)
end,
答题结果=case ds when 3 then '做对' when 2 then '做对' when 1 then '做错' when 0 then '没做完' else '没做' end,
回答次数=cc+inc,
答题情况=case ds when 3 then '一次答对' when 2 then '多次答对' when 1 then '多次答错' else '' end 
from
(
select t.StudentID,t.ClassCaseID,x.cc,x.inc,
ds=
max(case 
when x.inc=0 and x.cc=1 and x.fc=1 then 3--一次答对
when x.inc>=1 and x.fc=1 and x.cc=1 then 2--多次答对
when x.inc>=1 and x.fc=1 then 1--多次答错
when x.inc+x.cc+x.ac+x.oc>0 and x.fc=0 then 0--没做完
when x.inc+x.cc+x.fc+x.ac=0 and x.oc>=0 then -1--没做
end)
from tmp t
join
(select StudentID,ClassCaseID,
cc=sum(case [Action] when 'correct' then 1 else 0 end),--正确次数
fc=sum(case [Action] when 'finished' then 1 else 0 end),--完成次数
inc=sum(case [Action] when 'incorrect' then 1 else 0 end),--错误次数
ac=sum(case [Action] when 'abort' then 1 else 0 end),--中止次数
oc=sum(case when [Action] not in('correct','finished','incorrect','abort') then 1 else 0 end)--其它操作次数
from tmp group by StudentID,ClassCaseID
) x
on t.StudentID=x.StudentID and t.ClassCaseID=x.ClassCaseID
group by t.StudentID,t.ClassCaseID,x.cc,x.inc
) m

Answer 16

;with tmp as--三表关联
(
select a.StudentID,ClassCaseID=b.CaseID,c.Score,c.Action,c.TimeLine
from StudentClass a join ClassCase b on a.ClassID=b.ClassID
left join StudentCase c on a.StudentID=c.StudentID and b.ID=c.ClassCaseID
) select 学生ID=StudentID,问题ID=ClassCaseID,
分数=case 
when ds in(3,2,1) then --做完
(select isnull(Score,0) from tmp where StudentID=m.StudentID and ClassCaseID=m.ClassCaseID
and [Action]='finished')
else --没做完或没做
(select top 1 isnull(Score,0) from tmp where StudentID=m.StudentID and ClassCaseID=m.ClassCaseID
order by TimeLine desc)
end,
答题结果=case ds when 3 then '做对' when 2 then '做对' when 1 then '做错' when 0 then '没做完' else '没做' end,
回答次数=cc+inc,
答题情况=case ds when 3 then '一次答对' when 2 then '多次答对' when 1 then '多次答错' else '' end 
from
(select StudentID,ClassCaseID,cc,inc,
ds=max(case 
when inc=0 and cc=1 and fc=1 then 3--一次答对
when inc>=1 and fc=1 and cc=1 then 2--多次答对
when inc>=1 and fc=1 then 1--多次答错
when inc+cc+ac+oc>0 and fc=0 then 0--没做完
when inc+cc+fc+ac=0 and oc>=0 then -1--没做
end)
from 
(select StudentID,ClassCaseID,
cc=sum(case [Action] when 'correct' then 1 else 0 end),--正确次数
fc=sum(case [Action] when 'finished' then 1 else 0 end),--完成次数
inc=sum(case [Action] when 'incorrect' then 1 else 0 end),--错误次数
ac=sum(case [Action] when 'abort' then 1 else 0 end),--中止次数
oc=sum(case when [Action] not in('correct','finished','incorrect','abort') then 1 else 0 end)--其它操作次数
from tmp group by StudentID,ClassCaseID
) t group by StudentID,ClassCaseID,cc,inc
) m order by StudentID