MySQL数据库技术（18)

【 tulaoshi.com - 编程语言 】

　　3.8 解决方案随笔

    本节内容相当杂；介绍了怎样编写解决各种问题的查询。多数内容是在邮件清单上看到的解决问题的方案（谢谢清单上的那些朋友，他们为解决方案作了很多工作）。

    3.8.1 将子选择编写为连接

    MySQL自3.24版本以来才具有子选择功能。这项功能的缺少是MySQL 中一件常常令人惋惜的事，但有一件事很多人似乎没有认识到，那就是用子选择编写的查询通常可以用连接来编写。实事上，即使MySQL 具有了子查询，检查用子选择编写的查询也是一件苦差事；用连接而不是用子选择来编写会更为有效。

    1. 重新编写选择匹配值的子选择

    下面是一个包含一个子选择查询的样例，它从score 表中选择所有测试的学分（即，忽略测验的学分）：

    可通过将其转换为一个简单的连接，不用子选择也可以编写出相同的查询，如下所示：

    下面的例子为选择女学生的学分：

    可将其转换为连接，如下所示：

    这里是一个模式，子选择查询如下形式：

    这样的查询可转换为如下形式的连接：

    2. 重新编写选择非匹配值的子选择查询

　　另一种常用的子选择查询是查找一个表中有的而另一个表中没有的值。正如以前所看到的那样，“那些未给出的值”这一类的问题是LEFT JOIN 可能有用的一个线索。下面的查询包含一个子选择（它寻找那些全勤的学生）：

    3.8.2 检查表中未给出的值

    我们已经在3 . 6节“检索记录”中看到，在要想知道一个表中哪些值不出现在另一表中时，可对两个表使用LEFT JOIN 并查找那些从第二个表中选中NULL 的行。并用下列两个表举例：

    现在让我们来考虑一种更为困难的情况，“缺了哪些值”。对于第1 章中提到的学分保存方案中，有一个列出学生的student 表，一个列出已经出现过的学分事件的event 表，以及列出每个学生的每次学分事件学分的一个score 表。但是，如果一个学生在某个测试或测验的同一天病了，那么score 表中将不会有这个学生的该事件的学分，因此，要进行测验或测试的补考。我们怎样查找这些缺少了的记录，以便能保证让这些学生进行补考？问题是要对所有的学分事件确定哪些学生没有某个学分事件的学分。换个说法，就是我们希望知道学生和事件的哪些组合不出现在学分表中。这就是我们希望LEFT JOIN 所做的事。这个连接不像前例中那样简单，因为我们不仅仅要查找不出现在单列中的值；还需要查找两列的组合。

    我们想要的这种组合是所有学生/事件的组合，它们由student 表与event 表的叉积产生：

    FROM student, event

  　然后我们取出此连接的结果，与score 表执行一个LEFT JOIN 语句找出匹配者：

    FROM student, event

    LEFT JOIN score ON student.student_id = score.student.id

                    AND event.event_id = score.event_id

    请注意，ON 子句使得score 表中的行根据不同表中的匹配者进行连接。这是解决本问题的关键。LEFT JOIN 强制为由student 和event 表的叉连接生成的每行产生一个行，即使没有相应的score 表记录也是这样。这些缺少的学分记录的结果行可通过一个事实来识别，就是来自score 表的列将全是NULL 的。我们可在WHERE 子句中选出这些记录。来自score 表的任何列都是这样，但因为我们查找的是缺少的学分，测试score 列从概念上可能最为清晰：

    WHERE score.score IS NULL

    可利用ORDER BY 子句对结果进行排序。两种最合理的排序分别是按学生和按事件进行，我们选择第一种：

    ORDER BY student.student_id, event.event_id

    现在需要做的就是命名我们希望在输出结果中看到的列。最终的查询如下：

    SELECT
        student.name, student.student_id,
        event.date, event,event_id, event.type
    FROM
        student,event
        LEFT JOIN score ON student.student_id = score.student_id
                        AND event.event_id = score.event_id
    WHERE
        score.score IS NULL
    ORDER BY
        student.student_id, event.event_id