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

select t1.device_id,question_id,result
from question_practice_detail t1 left JOIN user_profile t2 on t1.device_id = t2.device_id
where university = '浙江大学'

select a.device_id,question_id,result
from (select id,device_id from user_profile where university='浙江大学') a left join question_practice_detail b on a.device_id=b.device_id
order by question_id

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

select t1.device_id,question_id,result
from question_practice_detail t1 inner JOIN user_profile t2 on t1.device_id = t2.device_id and university='浙江大学'

这题有三种解法

1.直接暴力连接两表后，筛选出浙江大学

2.先从user中筛选出浙江大学的device_id,再与question表做连接，相当于是半连接操作

3.where in子句判断question表中device_id是否在挑选出来的浙江大学device_id中

4.inner join连接，省去连接后再判断