SQL语句实战之表联接

个人认为学习SQL语句,最容易的就是SELECT语句,最难的也是SELECT语句，而SELECT 语句中比较关键的就是多表联合查询。本文通过一个实例，探讨表联接。

表联接很简单吗？就看你能否在工作中灵活运用了。
先复习下基础，看看微软的帮助：

联接可分为以下几类：
内部联接（典型的联接运算，使用类似于 = 或 <> 的比较运算符）。内部联接包括同等联接和自然联接。

内部联接使用比较运算符根据每个表的通用列中的值匹配两个表中的行。例如，检索 students 和 courses 表中学生标识号相同的所有行。

外部联接。外部联接可以是左向外部联接、右向外部联接或完整外部联接。

在 FROM 子句中可以用下列某一组关键字来指定外部联接：

LEFT JOIN 或 LEFT OUTER JOIN。

左向外部联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某一行在右表中没有匹配行，则在关联的结果集行中，来自右表的所有选择列表列均为空值。

RIGHT JOIN 或 RIGHT OUTER JOIN

右向外部联接是左向外部联接的反向联接。将返回右表的所有行。如果右表的某一行在左表中没有匹配行，则将为左表返回空值。

FULL JOIN 或 FULL OUTER JOIN

完整外部联接将返回左表和右表中的所有行。当某一行在另一个表中没有匹配行时，另一个表的选择列表列将包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

交叉联接

交叉联接将返回左表中的所有行。左表中的每一行均与右表中的所有行组合。交叉联接也称作笛卡尔积。

复习完里基础就来看看下面的实际场景来练练手吧。
这是我工作中的实际遇到的场景，这里就使用上面的学生与课程表做个类比吧。

假设现在有几个表，分别是：

学生信息表(student：student_id, student_name,......)、
课程表(courses:courses_id,courses_name,...... )、
还有学生选课表(student_elective_courses：student_id, courses_id)、
学生感兴趣的课程表(student_interest_courses：student_id, courses_id)

现在的需求是：找出关注某个课程的学生关注的其他哪些课程。(这里的关注包括选了课及感兴趣两个概念)

程序化表达：通过某个courses_id可以在两个关系表student_elective_courses、student_interest_courses中得到一些student_id，通过这些student_id又可以在两个表中得到一些courses_id。要求是找出来之后还要按照课程的“人气”排序(关注人数多的排前面，关注人数多一样的时候选课人多的排前面)

举个例子：对于课程“离散数学”，有些同学已经选了这门课在学了，有些同学还没选，但是对这门课有兴趣。当然两类同学本身可能有选修了其他的课程或对其他的课程有兴趣。我们就是想统计这些“其他”的课程。

这种类型的统计在商业上是很有必要挖掘的信息。例如某个网上书城，如果能在用户浏览某本书的时候给出其他浏览或购买这本书的用户还在浏览其他的一些什么书籍，相信会很好的引导用户浏览更多的书。当然销售量也会跟着上去咯，因为“臭味相投”的人还是很多的。


回到原来的问题，这个问题的难点在于要统计的关注人数分布在两个表中。分两次查询再合并结果也是可以的，就是比较麻烦。看看下面的解决脚本：

SELECT                                                                            
               a.courses_id,                                                                       
               a.courses_name,                                                                       
               COUNT(DISTINCT b.student_id)+COUNT(DISTINCT c.student_id) as attentionCount,              
               COUNT(DISTINCT b.student_id) as interestCount                                               
                                                                                                                                                                            
  FROM student a LEFT JOIN                                                                 
               student_elective_courses b ON a.courses_id = b.courses_id LEFT JOIN                                                   
               student_interest_courses c ON a.courses_id = c.courses_id LEFT JOIN                       
               student_elective_courses b1 ON b.student_id = b1.student_id OR c.student_id = b1.student_id LEFT JOIN                   student_interest_courses c1 ON b.student_id = c1.student_id OR c.student_id = c1.student_id              
WHERE (b1.courses_id = 1 OR c1.courses_id = 1) AND a.courses_id <> 1  --这里“1”是可变参数                       
 GROUP BY a.courses_id,a.courses_name                                                      
  HAVING COUNT(DISTINCT b.student_id)+COUNT(DISTINCT c.student_id) >0                             
    ORDER BY attentionCount DESC, interestCount DESC, a.courses_id

分析说明：
看看关联student_elective_courses b1 的条件：

b.student_id = b1.student_id OR c.student_id = b1.student_id

这个条件得到：选了某些课的学生(表b1)还选了哪些课(表b，条件b.student_id = b1.student_id)以及还关心了哪些课(表c，条件c.student_id = b1.student_id)，关联表c1也是同样道理。最后加上个聚和就得到了想要的结果。

有兴趣研究的朋友注意一下这里表的自联接及联接条件，联接的条件常常可以做到一些不错的效果，而不仅仅是父子表同字段的关联。

刚开始写的时候觉得满有意思的，虽然写完之后仔细分析却又发现其实满简单的。 有味道的东西就写出来跟大家分享，也免得这里荒废掉了^_^