题解 | #未完成试卷数大于1的有效用户#

select a.uid,   
    sum(incomplete) as incomplete_cnt,
    sum(complete) as complete_cnt,
    group_concat(distinct a.detail0  separator';') as detail
from(
    select re.uid as uid,
       if(re.submit_time is null,1,0) as incomplete,
       if(re.submit_time is not null,1,0) as complete,
       concat_ws(':',date(re.start_time),tag) as detail0
    from exam_record re
    left join examination_info info
    on  re.exam_id=info.exam_id
    where year(re.start_time)='2021'
    order by re.start_time
    ) a
group by a.uid
having complete_cnt>=1 and incomplete_cnt between 2 and 4  # 或者用between的左右都包含
order by incomplete_cnt desc
;

	

解题思路

# 步骤分解：
# 1.先筛选作答记录中2021年的数据；   where
# 2.答题的类型进行分类：完成和未完成；   if
# 3.筛选有效用户；     where and
# 4.表连接，补充对应
# 5.detail的试卷tag集合 (多列字段合集，行转列合集) 
#  '''
 
#  --1.先筛选作答记录中2021年的数据
#  select *
#  from exam_record
#  where year(start_time)='2021'
 
#  --2.答题的类型进行分类：完成和未完成
# select *,
#    if(submit_time is null,0,1) as sub_ty
# from exam_record
# where year(start_time)='2021'


# --3.左连接试卷信息表examinat——info，查询
# select re.uid,
#    if(re.submit_time is null,0,1) as sub_ty,
#    concat_ws(':',date_format(re.submit_time,'%Y%m'),tag) as detail0   
#    #时间格式需要处理
# from exam_record re
# left join examination_info info
# on  re.exam_id=info.exam_id
# where year(re.start_time)='2021'


# --4.查询有效用户（有效用户指完成试卷作答数至少为1且未完成数小于5）
#且有效用户进一步筛选——未完成试卷作答数大于1
# select a.uid,   
#     sum(incomplete) as incomplete_cnt,
#     sum(complete) as complete_cnt
# from(
#     select re.uid as uid,
#        if(re.submit_time is null,1,0) as incomplete,
#        if(re.submit_time is not null,1,0) as complete,
#        concat_ws(':',date(re.submit_time),tag) as detail0
#     from exam_record re
#     left join examination_info info
#     on  re.exam_id=info.exam_id
#     where year(re.start_time)='2021'
#     ) a
# group by a.uid
# #having complete_cnt>=1 and incomplete_cnt<5;  # 此为有效用户的判断条件
# having complete_cnt>=1 and incomplete_cnt>1 and incomplete_cnt<5 # 有效用户进一步筛选——未完成试卷作答数大于1
#;

#--5.实现detail的多元素行变列连接,用group_concat()
# 按未完成试卷数量由多到少排序。
select a.uid,   
    sum(incomplete) as incomplete_cnt,
    sum(complete) as complete_cnt,
    group_concat(distinct a.detail0  separator';') as detail
from(
    select re.uid as uid,
       if(re.submit_time is null,1,0) as incomplete,
       if(re.submit_time is not null,1,0) as complete,
       concat_ws(':',date(re.start_time),tag) as detail0
    from exam_record re
    left join examination_info info
    on  re.exam_id=info.exam_id
    where year(re.start_time)='2021'
    order by re.start_time
    ) a
group by a.uid
#having complete_cnt>=1 and incomplete_cnt<5;  # 此为有效用户的判断条件
#having complete_cnt>=1 and incomplete_cnt>1 and incomplete_cnt<5 # 有效用户进一步筛选——未完成试卷作答数大于1
having complete_cnt>=1 and incomplete_cnt between 2 and 4  # 或者用between的左右都包含
order by incomplete_cnt desc
;