首先,本题中的示例中未显示全部信息,缺少日期date的信息;
其次,存在两张表的联结,匹配出相关信息;
第三,回答正确的题目数,涉及到case when ...then ...end多条件判断后再用count()聚合函数进行统计;
SELECT q.device_id, u.university, COUNT(q.id) as question_cnt, COUNT(case when q.result="right" then q.id end) as right_question_cnt FROM user_profile AS u JOIN question_practice_detail as q ON u.device_id=q.device_id WHERE u.university="复旦大学" AND month(q.date)="08" ;