题解 | #月均完成试卷数不小于3的用户爱作答的类别#

select t2.tag,count(*) as tag_cnt
from exam_record as t1 left join examination_info as t2 on 
t1.exam_id = t2.exam_id 
where uid in 
    (select uid from
        (select uid,date_format(submit_time,'%Y%m') as mth
        from exam_record
        where submit_time is not null 
        group by uid,date_format(submit_time,'%Y%m')
        having count(submit_time)>=3) as tep1) 
group by t2.tag 
order by tag_cnt desc

考查子查询:

• 标量子查询:子查询返回的是单个值.

SELECT name, age FROM students

WHERE age = (SELECT MAX(age) FROM students);

• 列子查询,返回一列值,通常用在where xxx in (select xxx from table where *** >#)
• 行子查询,返回一行多列值.

SELECT name, age FROM students

WHERE (name, age) = (SELECT name, MAX(age) FROM students);

• 表子查询,返回多行多列值的子查询,通常用在FROM子句中,作为一个临时表.(表子查询需要将查询出来的结果 as tb.

SELECT s.name FROM (SELECT name FROM students WHERE age > 18) AS s;

题目解析:

“当月均完成试卷数”不小于3的用户们爱作答的类别及作答次数，按次数降序输出.

看到该题目思路就是:

1. 先筛选出 "当月均完成试卷数”不小于3的用户们" 的id.

关键是当月

(select uid,date_format(submit_time,'%Y%m') as mth
        from exam_record
        where submit_time is not null 
        group by uid,date_format(submit_time,'%Y%m')
        having count(submit_time)>=3)

2.然后再去join两张表查对应id的作答类别和作答次数.

select t2.tag,count(*) as tag_cnt
from exam_record as t1 left join examination_info as t2 on 
t1.exam_id = t2.exam_id 
where uid in 
    (select uid from
        (select uid,date_format(submit_time,'%Y%m') as mth
        from exam_record
        where submit_time is not null 
        group by uid,date_format(submit_time,'%Y%m')
        having count(submit_time)>=3) as tep1) 
group by t2.tag 
order by tag_cnt desc