【实践篇】连接查询

用户信息表 user_profile 如下：

用户信息表 user_profile 和下方的答题详情表 question_practice_detail 为一对多的关系，答题详情表记录了用户回答的问题以及结果。

除此之外，还有一个问题详情表，记录了问题 ID 和问题问题难度。问题详情表 question_detail 的结构如下：

1. 内连接

首先，我们检索每个大学参与过答题的用户平均答题数量。

我们将用户信息表和答题明细表通过 device_id 进行内连接，选择两个表 device_id 相等的行级别匹配，这就是参与过答题的所有用户。

最后，根据大学 university 字段进行分组，在该维度上聚合问题的数量以及用户数量，其中计算人数时需要对 device_id 去重，因为连接之后一个用户对应多个回答记录， device_id 会发生重复。

SELECT 
    u.university, 
    COUNT(q.question_id)/COUNT(DISTINCT q.device_id) AS avg_answer_cnt
FROM 
    user_profile AS u, 
    question_practice_detail AS q
WHERE 
    u.device_id=q.device_id
GROUP BY 
    u.university;

内连接查询结果：

除了使用 WHERE 子句指定过滤条件外，还可以使用更加显示化的 内连接语法 INNER JOIN ... ON ... 。

SELECT 
    u.university, 
    COUNT(*)/COUNT(DISTINCT q.device_id) AS avg_answer_cnt
FROM 
    user_profile AS u
INNER JOIN 
    question_practice_detail AS q
ON 
    u.device_id=q.device_id
GROUP BY 
    u.university;

2. 内连接、左外连接和分组

下面，我们来连接更多的表，检索所有参与答题的用户中不同学校、不同题目难度下的平均答题数量。

问题的复杂度是由维度字段决定的。比如问题一中我们只是分学校，但问题二是分学校、分难度。

聚合字段 avg_answer_cnt 的计算还是和前文一样的，即用每个分组的答题总数除以每个分组的答题人数计算得出。维度字段中，我们增加了问题难度 difficult_level ，该字段位于问题明细表 question_detail 。

为此，我们需要在之前连接的基础上，增加一个左外连接，即保留左边（原来）的所有查询结果，将问题明细表中 question_id 相同的行保留到连接查询结果中。

SELECT 
    up.university, 
    qd.difficult_level, 
    COUNT(*)/COUNT(DISTINCT up.device_id) AS avg_answer_cnt
FROM 
    user_profile AS up
INNER JOIN 
    question_practice_detail AS qpd
ON 
    up.device_id = qpd.device_id
LEFT JOIN 
    question_detail AS qd
ON 
    qpd.question_id = qd.question_id
GROUP BY 
    up.university, qd.difficult_level;

检索结果如下：

3. 过滤和连接

最后，检索参加了答题的山东大学的用户在不同难度下的平均答题题目数。

由于指定了山东大学，因此不需要对大学再进行分组了，在所有连接子句之后，GROUP BY 子句之前增加一个 WHERE 子句，过滤学校为 “山东大学” 的连接表记录即可。

SELECT 
    up.university, 
    qd.difficult_level, 
    COUNT(*)/COUNT(DISTINCT qpd.device_id) AS avg_answer_cnt
FROM 
    user_profile AS up
INNER JOIN 
    question_practice_detail AS qpd
ON 
     up.device_id = qpd.device_id
LEFT JOIN 
    question_detail AS qd
ON 
    qpd.question_id = qd.question_id
WHERE
    up.university = '山东大学'
GROUP BY
    qd.difficult_level;

检索结果如下：