题解 | #浙江大学用户题目回答情况#

select question_practice_detail.device_id,question_practice_detail.question_id,question_practice_detail.result 
from question_practice_detail
where device_id=(select user_profile.device_id from user_profile where university='浙江大学')

先在question_practice_detail筛选出device_id，question_id，result，然后与user_profile中学校为浙江大学的设备号进行对比，两个device_id相同的时候保留

select user_profile.device_id as device_id,question_practice_detail.question_id as question_id,question_practice_detail.result as result
from user_profile
LEFT JOIN question_practice_detail on user_profile.device_id=question_practice_detail.device_id
where university='浙江大学'
order by question_id

使用left join在user_profile左边拼接question_practice_detail，按照.device_id进行拼接，使用order by排序

select question_practice_detail.device_id,question_practice_detail.question_id,question_practice_detail.result from question_practice_detail 
inner join user_profile on user_profile.device_id=question_practice_detail.device_id
where user_profile.university='浙江大学'
order by question_id

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

inner join(等值连接) 只返回两个表中联结字段相等的行