学号、姓名、组号、自动阅卷评分、有问题的题目数

　　情况是这样的，有一个学生表（学号、姓名、组号、试卷号），试卷表（试卷号，题号，空号，分值），考题表（考题号，题干），考空表（考题号，考空号，参考答案），作答表（学号，题号，空号，作答）。

　　现在，试卷表是考空表的一部分，因为试卷上的题目是题库中考题的一部分；作答表是学生表和试卷表的一部分，因为考虑到有的学生会一道题都没有做（一般不会出现），以及有的考生只会做试卷上的部分题目，考生每作答一个考空就插入一条记录。

　　要获取本文题目所述的目的（这只是目的之一），我利用视图技术。

 

　　首先第一个视图是v作答（学号，题号，空号，分值，作答，人工判对），以考生表，试卷表，作答表为基本表。因为有的考生没被分配到试卷（此时考生还没开始考试，一般不会有这种情况），有的考生不会作答试卷上的全部题目，所以要依次左连接以上表：

SELECT s.id AS sid, t.qid, t.bid, t.score, r.respondText, r.isRight FROM dbo.Student s LEFT OUTER JOIN dbo.Test t ON s.testID = t.id LEFT OUTER JOIN dbo.Responds r ON r.qid = t.qid AND r.bid = t.bid AND r.sid = s.id 

　　顺序不能错，一定是母集放在前面，子集放在后面。这样，存在于母集同时不存在于子集的记录也会被显示在结果集中。

 

　　然后，第二个视图v_StuNameScore（这里不翻译了，你懂的）以第一个视图为基础，另外获取另一些基本表中的信息：

SELECT rr.sid, s.name, s.groupID, ISNULL(SUM(r.score), 0) AS allScore FROM dbo.v_respond r INNER JOIN dbo.Answers a ON a.id = r.bid AND a.qid = r.qid AND r.respondText = a.answer RIGHT OUTER JOIN (SELECT DISTINCT sid FROM v_respond) rr ON rr.sid = r.sid INNER JOIN dbo.Student s ON rr.sid = s.id GROUP BY rr.sid, s.name, s.groupID 

　　数据表连接对于我个人来说是个不容易思考的问题，也许是我的抽象能力不太行吧！上面代码中，子查询不能放在最上面，因为r.respondText = a.answer这个条件会筛掉r.respondText为null的记录，而如果这位考生没有一个作答正确，或者是一道题都没有做，那么该考生的记录就会被完全筛掉，得不到自动评阅得分为0的结果。所以正确的做法应该是，先让r.respondText = a.answer筛掉会被筛掉的结果，然后RIGHT JOIN子查询（所有的学号），保证每个学号记录都能存在，最后连接考生表，获取考生姓名、组号等信息。

　　我由此得出的个人经验是：先筛掉会被筛掉的记录，然后右连接所有必须呈现的记录。不知道这么想对不对！？

 

　　第三个视图，或许说是“第二层视图”，和第二个视图一样。视图v_sid_wrongNum的作用是查询出所有考生经过系统自动评阅后，仍有疑问，需要教师人工评阅的题目数，筛选条件是作答不等于参考答案。要注意的是，如果作答为null，那么该题该空肯定做错。

SELECT rr.sid, COUNT(DISTINCT r.qid) AS wrongNum FROM dbo.v_respond r INNER JOIN dbo.Answers a ON r.qid = a.qid AND r.bid = a.id AND r.respondText <> a.answer RIGHT OUTER JOIN (SELECT DISTINCT sid FROM v_respond) rr ON r.sid = rr.sid GROUP BY rr.sid 

　　上面代码中，右连接的子查询不能省略。因为如果respondText为null，那么条件r.respondText <> a.answer也不会成立，相关记录会被筛掉（貌似是这样子的）。然后依据同样的原则：先筛掉会被筛掉的记录，然后右连接所有必须呈现的记录。

 

　　最后是第四个视图（第三层视图）。忘了在哪里看了，好像有这么一个说法，那就是视图定义最好不要超过3层，这里刚好是第三层，不算糟糕的设计。视图v_sid_name_score_wrongNum就是本文所要得到的视图，得到的方法很简单——连接第二层的两个视图就行：

SELECT w.sid, w.name, w.groupID, w.allScore, s.wrongNum FROM dbo.v_sid_wrongNum s INNER JOIN dbo.v_StuNameScore w ON s.sid = w.sid 

 

　　通过这个例子，我认为视图的一个具有普遍性的用处是：当需要在用户页面中呈现某些复杂数据，而这些复杂数据可以通过连接或计算若干个基本表得出，为了避免编写复杂的SQL语句，所以写成视图。在这个例子中，第一层视图不会包含太多信息，一般只包含主键和一些核心信息（比如分值，考生作答，是否通过人工评阅判对），而不会包含考生姓名、题干等信息。因此，第一层视图一般不会被直接呈现在用户页面，但是会尽可能多的被上层视图引用。然后在第二层或更高层的视图中就可以引用第一层视图，再连接若干个基本表，得到需要在用户页面内呈现的表格数据。

 

　　以上仅为个人一点实践心得，并不保证正确性，请读者们辩证地对待。

 

　　多说一句，怎么CSDN博客上和论坛上的“插入代码”工具有点不一样，写博客时找不到插入SQL代码的选项。