题解 124 | #统计作答次数#

【场景】：不同条件下做统计

【分类】：select if、多表连接

分析思路

难点：

1.在不同的条件下使用count做统计

在不同的条件下做统计

方法一：

where 中写条件，每次只能得到一列，分别得到每列，然后用join做连接

方法二：

使用select if，把条件写在select里面

作答次数

• [条件]： count(idid)

已完成的作答次数

• [条件]： count(if(submit_time is not null and score is not null, 1, null))

已完成的试卷份数

• [条件]：count(distinct if(submit_time is not null and score is not null, exam_id, null))

求解代码

方法一：

with子句 + where

with
    temp as(
        #作答次数
        select
            count(id) as total_pv
        from exam_record
    )
    ,main as(
        #已完成的作答次数
        select
            count(if(submit_time is not null and score is not null, 1, null)) as complete_pv
        from exam_record
    )
    ,attr as(
        #已完成的试卷份数
        select
            count(distinct if(submit_time is not null and score is not null, exam_id, null)) as complete_exam_cnt
        from exam_record
    )

#多表连接
select *
from temp,main,attr

方法二：

多表连接 + where

#多表连接
select *
from(
    #作答次数
    select
        count(id) as total_pv
    from exam_record
) temp,(
    #已完成的作答次数
    select
        count(if(submit_time is not null and score is not null, 1, null)) as complete_pv
    from exam_record
)main,(
    #已完成的试卷份数
    select
        count(distinct if(submit_time is not null and score is not null, exam_id, null)) as complete_exam_cnt
    from exam_record
)attr

方法三：

使用select if

select
    count(id) as total_pv,
    count(if(submit_time is not null and score is not null, 1, null)) as complete_pv,
    count(distinct if(submit_time is not null and score is not null, exam_id, null)) as complete_exam_cnt
from exam_record