还是上次你告诉我的那个问题

Question

create table StudentCase (
   StudentID              uniqueidentifier     not null,
   ActionTime             datetime             not null,
   CaseID                 uniqueidentifier     not null,
   QuestionID             char(4)              not null,
   Action                 tinyint              not null,
)insert into StudentCase values('4f197040-01c3-4d9c-aab4-2581db4e23c9','1900-01-01 00:00:00','5a857499-b936-4255-83e6-09bb58bc1f0a','0000','问题描述')
insert into StudentCase values('4F197040-01C3-4D9C-AAB4-2581DB4E23C9','1900-01-01 00:00:03.127','5A857499-B936-4255-83E6-09BB58BC1F0A','0001','查看目录')
insert into StudentCase values('4F197040-01C3-4D9C-AAB4-2581DB4E23C9','1900-01-01 00:00:01.377','5A857499-B936-4255-83E6-09BB58BC1F0A','0002','试图查看')
insert into StudentCase values('4F197040-01C3-4D9C-AAB4-2581DB4E23C9','1900-01-01 00:00:02.063','5A857499-B936-4255-83E6-09BB58BC1F0A','0002','确认查看')
insert into StudentCase values('4F197040-01C3-4D9C-AAB4-2581DB4E23C9','1900-01-01 00:00:01.640','5A857499-B936-4255-83E6-09BB58BC1F0A','0004','试图查看')
insert into StudentCase values('4F197040-01C3-4D9C-AAB4-2581DB4E23C9','1900-01-01 00:00:02.170','5A857499-B936-4255-83E6-09BB58BC1F0A','0004','取消查看')
insert into StudentCase values('4F197040-01C3-4D9C-AAB4-2581DB4E23C9','1900-01-01 00:00:02.217','5A857499-B936-4255-83E6-09BB58BC1F0A','0024','查看目录')
insert into StudentCase values('4F197040-01C3-4D9C-AAB4-2581DB4E23C9','1900-01-01 00:00:01.627','5A857499-B936-4255-83E6-09BB58BC1F0A','0025','试图查看')
insert into StudentCase values('4F197040-01C3-4D9C-AAB4-2581DB4E23C9','1900-01-01 00:00:02.500','5A857499-B936-4255-83E6-09BB58BC1F0A','0025','确认查看')create table ClassCase (
   ID                 uniqueidentifier       not null,
   Curriculum           uniqueidentifier     not null,
   "Case"               uniqueidentifier     not null,
   CaseID               char(4)              not null,
   State                tinyint              not null
)
insert into ClassCase values('c004d9b9-0ac5-4f1b-b3d1-2261e725726a','89db74d3-4c1a-44f1-9cb0-534e9012ec7a','5a857499-b936-4255-83e6-09bb58bc1f0a','0004,0)
insert into ClassCase values('900b6484-fbcb-4cd0-9545-4f7567b5c3fd','89db74d3-4c1a-44f1-9cb0-534e9012ec7a','5b37be5c-43a3-4325-9038-761069811fc9','0003,0)
insert into ClassCase values('b2b302b7-1a9b-49cf-bff1-d030ebda5587','3f328cb0-41ad-405c-9e1e-bd1b52dff743','3086d615-1bf6-4ebe-94c1-8f0fa02c5c76','0007,0)
insert into ClassCase values('6cb1a85f-9706-49e6-9da9-f470463405c2','3f328cb0-41ad-405c-9e1e-bd1b52dff743','5a857499-b936-4255-83e6-09bb58bc1f0a','0004,0)create table StudentClass (
   ID                   uniqueidentifier     not null,
   StudentID            uniqueidentifier     not null,
   State                tinyint              not null
)
insert into StudentClass values('c004d9b9-0ac5-4f1b-b3d1-2261e725726a','89db74d3-4c1a-44f1-9cb0-534e9012ec7a','5a857499-b936-4255-83e6-09bb58bc1f0a','0004,0)
insert into StudentClass values('900b6484-fbcb-4cd0-9545-4f7567b5c3fd','89db74d3-4c1a-44f1-9cb0-534e9012ec7a','5b37be5c-43a3-4325-9038-761069811fc9','0003,0)
insert into StudentClass values('b2b302b7-1a9b-49cf-bff1-d030ebda5587','3f328cb0-41ad-405c-9e1e-bd1b52dff743','3086d615-1bf6-4ebe-94c1-8f0fa02c5c76','0007,0)
insert into StudentClass values('6cb1a85f-9706-49e6-9da9-f470463405c2','3f328cb0-41ad-405c-9e1e-bd1b52dff743','5a857499-b936-4255-83e6-09bb58bc1f0a','0004,0)

Answer 1

SELECT a.StudentID,a.CaseGuid,d.CaseID,
State = CASE WHEN d.CaseID IS NULL THEN '没做' 
           WHEN d.Total<1 THEN '正在做' 
           WHEN d.Total>=1 THEN '已经做完' 
       END 
FROM  (SELECT aa.ID as ClassID,bb.ID as CaseGuid,aa.StudentID,bb.CaseID FROM StudentClass aa 
CROSS JOIN 
(SELECT ID,CaseID FROM ClassCase WHERE Curriculum='3F328CB0-41AD-405C-9E1E-BD1B52DFF743') bb
) a
left JOIN ( 
SELECT a.CaseID as tmp, c.StudentID,b.CaseID,SUM(CASE WHEN a.Action='finished' THEN 1 ELSE 0 END) as Total
 FROM StudentCase a,ClassCase b,StudentClass c WHERE a.CaseID=b.[Case] 
AND b.Curriculum=c.ID AND a.StudentID=c.StudentID GROUP BY a.CaseID,c.StudentID,b.CaseID ) d 
ON a.StudentID=d.StudentID and a.CaseGuid=d.tmp  我把SQL语句写成这样，但是d.CaseID总是为空。可是d里面有一条CaseID为0004的记录啊

Answer 2

不好意思。
表1：
ClassID StudentID
001      01
001      02
001      03
001      04
002      01
002      02
002      03
create table tb1(ClassID varchar(10) , StudentID varchar(10))
insert into tb1 values('001' , '01')
insert into tb1 values('001' , '02')
insert into tb1 values('001' , '03')
insert into tb1 values('001' , '04')
insert into tb1 values('002' , '01')
insert into tb1 values('002' , '02')
insert into tb1 values('002' , '03')表2：
StudentID CaseID State
01        1      0
01        2      0
01        3      0
02        1      0
02        2      0
02        3      0
03        1      0
03        2      0create table tb2(StudentID varchar(10) ,CaseID int,  State int)
insert into tb2 values('01' ,      1, 0)
insert into tb2 values('01' ,      2, 0)
insert into tb2 values('01' ,      3, 0)
insert into tb2 values('02' ,      1, 0)
insert into tb2 values('02' ,      2, 0)
insert into tb2 values('02' ,      3, 0)
insert into tb2 values('03' ,      1, 0)
insert into tb2 values('03' ,      2, 0)表3：
ID  StudentID Action
002 01        finished
002 02        aa
002 02        bb
create table tb3(ID varchar(10),StudentID varchar(10) , Action varchar(10))
insert into tb3 values('002','01' ,      'finished')
insert into tb3 values('002','02' ,      'aa')
insert into tb3 values('002','02' ,      'bb')表4：
ID  ClassID CaseID
001 001    1
002 001    2
003 001    3
004 001    4
005 001    5
006 002    1
007 002    2
008 002    3
009 002    4
create table tb4(ID varchar(10), ClassID varchar(10) , CaseID int)
insert into tb4 values('001','001' ,      1)
insert into tb4 values('002','001' ,      2)
insert into tb4 values('003','001' ,      3)
insert into tb4 values('004','001' ,      4)
insert into tb4 values('005','001' ,      5)
insert into tb4 values('006','002' ,      1)
insert into tb4 values('007','002' ,      2)
insert into tb4 values('008','002' ,      3)
insert into tb4 values('009','002' ,      4)要求查询结果：
根据tb1的ClassID得到已知班的学生名单，最终结果要更新tb2中State字段的内容。
tb4中ID为关键字段，根据ClassID得到该班下的所有CaseID,tb3中的ID为tb4中的CaseID集合的子集，测试数据中，tb3中学生01的Action中有finished，那么在tb2中第2条记录的State为1（StudentID为02的学生已经做完），第5条记录的 State为2（StudentID为02的学生没有做完），第8条记录的State为3（StudentID为02的学生没有做）.
这是我以前提的问题，现在表结构有点改动，我重新写了个，大家帮我看看有没有错，还有什么可以改进的地方，谢谢啦。
SELECT a.StudentID,a.CaseGuid,a.CaseID,
State = CASE WHEN d.CaseID IS NULL THEN '没做' 
           WHEN d.Total<1 THEN '正在做' 
           WHEN d.Total>=1 THEN '已经做完' 
       END 
FROM  (SELECT aa.ID as ClassID,bb.[Case] as CaseGuid,aa.StudentID,bb.CaseID FROM StudentClass aa 
CROSS JOIN 
(SELECT [Case],CaseID FROM ClassCase WHERE Curriculum='3F328CB0-41AD-405C-9E1E-BD1B52DFF743') bb
) a
left JOIN ( 
SELECT a.CaseID as tmp, c.StudentID,b.CaseID,SUM(CASE WHEN a.Action='finished' THEN 1 ELSE 0 END) as Total
 FROM StudentCase a,ClassCase b,StudentClass c WHERE a.CaseID=b.[Case] 
AND b.Curriculum=c.ID AND a.StudentID=c.StudentID GROUP BY a.CaseID,c.StudentID,b.CaseID ) d 
ON a.StudentID=d.StudentID and a.CaseGuid=d.tmp