题解|表的连接|#0级用户高难度试卷的平均用时和平均得分#

## 首先判断是否完成：
## 记录未完成和完成的用时：IF(submit_time IS NULL, duration, timestampdiff(minute,start_time,submit_time)) AS new_time
## 记录未完成和完成的分数：IF(score IS NULL, 0, score) AS new_score

## 计算平均用时和平均分数：AVG(new_score) AS avg_score & AVG(new_time) AS avg_time_took

# SELECT a.uid,a.exam_id,a.start_time,a.submit_time,a.score,b.difficulty,b.duration,c.uid,c.level
# FROM exam_record a,examination_info b,user_info c
# WHERE a.uid = c.uid AND a.exam_id = b.exam_id AND c.level = 0 AND b.difficulty = hard

SELECT uid, ROUND(AVG(IF(score IS NULL, 0, score)),0) AS avg_score,
ROUND(AVG(IF(submit_time IS NULL, duration, timestampdiff(minute,start_time,submit_time))),1) AS avg_time_took
FROM(
    SELECT a.uid,a.exam_id,a.start_time,a.submit_time,a.score,b.difficulty,b.duration,c.level
FROM exam_record a,examination_info b,user_info c
WHERE a.uid = c.uid AND a.exam_id = b.exam_id AND c.level = 0 AND b.difficulty = 'hard')t1
GROUP BY uid

现有三张表A\B\C，需要查询A表和C表中的某些字段，但是A表和C表并没有相同的字段，无法直接关联，此时恰好B表有两个字段，一个字段和A表中的一个字段相同，另一个字段和C表中的一个字段相同，可以称表B为中间表，然后通过B表将A表和C表关联起来

方法一（非常推荐，就用这种）：

SELECT A1,A2,C1,C2,B1,B2
#(还可以继续加上B中的字段B1,B2。这时候各个字段是不需要加上前面的表名的，如果担心的话还是可以加上的）
FROM B
INNER JOIN A ON A.A1 = B.B1
INNER JOIN C ON C.C1 = B.B1
WHERE XXXXX