题解 135 | #每个6/7级用户活跃情况#

【场景】：列合并

【分类】：连接查询、多表连接、select条件语句

分析思路

难点：

1.建立列合并的思想 2.在select使用条件语句

（1）统计用户总活跃月份数 如果日期重复算一个月份

​ [使用]：[年月]：date_format(exrp,'%y%m') ; 去重：distinct

（2）统计用户2021年活跃天数 如果日期重复算一天

​ [使用]：[2021年]: year(exrp) = 2021; [年月日]：date(exrp) ; 去重：distinct；

注意： 判断是否是2021年应该放在select里面而不是where中

（3）统计2021年试卷作答活跃天数

​ [使用]： [2021年]: year(exrp) = 2021; [年月日]：date(exrp) ;

（4）统计2021年答题活跃天数

​ [使用]：多表连接使用 join using( )

（5）合并列

​ [使用]： [2021年]: year(exrp) = 2021; [年月日]：date(exrp) ;

最终结果

select 查询结果 [总活跃月份数; 2021年活跃天数; 2021年试卷作答活跃天数; 2021年答题活跃天数]
from 从哪张表中查询数据[多个join连接的表]
where 查询条件 [level等级是6/7]
order by 对查询结果排序 [按照总活跃月份数、2021年活跃天数降序];

实现过程

（1）需要一个临时表：

with 
    main as(
		#试卷作答记录和题目练习记录
        select distinct
            a.uid,
            date(start_time) as days,
            'exam' as tag
        from user_info a
        left join exam_record b
        using(uid)
        union
        select distinct
            a.uid,
            date(submit_time) as days,
            'question' as tag
        from user_info a
        left join practice_record c
        using(uid)
    ) 

注意：mysql版本在8.0之前不支持with。如需配置mysql的8.0版本参考

（2）求select列

• 总活跃月份数

#总活跃月份数 attr
select 
    uid,
    count(distinct date_format(days,'%y%m')) as act_month_total
from main
group by uid

• 2021年活跃天数

#2021年试卷作答活跃天数 attr1
select
	uid,
	count(distinct(if(year(start_time) = 2021,start_time,null))) as act_days_2021_exam
from main
group by uid

• 2021年试卷作答活跃天数

• count(distinct(if(year(date(act_date)) = 2021 and tag = 'exam',act_date,null)))

  利用tag标记是试卷作答记录还是答题作答记录。

#2021年试卷作答活跃天数 attr2
select
    uid,
    count(distinct(if(year(days) = 2021 and tag = 'exam',days,null))) as act_days_2021_exam
from main1
group by uid

• 2021年答题活跃天数

#2021年答题活跃天数 attr3
select
    uid,
    count(distinct(if(year(days) = 2021 and tag = 'question', days, null))) as act_days_2021_question
from main1
group by uid

（3）合并列

select
    a.uid,
    act_month_total,
    act_days_2021,
    act_days_2021_exam,
    act_days_2021_question
from user_info a
left join attr using(uid) 
left join attr1 using(uid)
left join attr2 using(uid)
left join attr3 using(uid)
where level between 6 and 7
order by act_month_total desc,act_days_2021 desc

扩展：

前往查看MySQL 合并查询 join 查询出的不同列合并到一个表中

求解代码

方法一：

with子句 + 多表连接

with 
    main as(
		#试卷作答记录和题目练习记录
        select distinct
            a.uid,
            date(start_time) as days,
            'exam' as tag
        from user_info a
        left join exam_record b
        using(uid)
        union
        select distinct
            a.uid,
            date(submit_time) as days,
            'question' as tag
        from user_info a
        left join practice_record c
        using(uid)
    ) 
#合并列
select
    a.uid,
    act_month_total,
    act_days_2021,
    act_days_2021_exam,
    act_days_2021_question
from user_info a
left join(
    #总活跃月份数指的是所有年
    select 
        uid,
        count(distinct date_format(days,'%y%m')) as act_month_total
    from main
    group by uid
) attr using(uid)
left join(
    #2021年活跃天数
    select 
        uid,
        count(distinct if(year(days) = 2021,days,null)) as act_days_2021	
    from main
    group by uid
) attr1 using(uid)

left join(
    #2021年试卷作答活跃天数
    select
        uid,
        count(distinct(if(year(days) = 2021 and tag = 'exam',days,null))) as act_days_2021_exam
    from main
    group by uid
) attr2 using(uid)

left join(
    #2021年答题活跃天数
    select
        uid,
        count(distinct(if(year(days) = 2021 and tag = 'question',days,null))) as act_days_2021_question
    from main
    group by uid
) attr3 using(uid)
where level between 6 and 7
order by  act_month_total desc,act_days_2021 desc#按照总活跃月份数、2021年活跃天数降序排序

方法二：

select条件语句

select
    uid,
    count(distinct date_format(days,'%Y%m')) as act_month_total,#总活跃月份数指的是所有年
    count(distinct if(year(days) = 2021,days,null)) as act_days_2021,#2021年活跃天数
    count(distinct(if(year(days) = 2021 and tag = 'exam',days,null))) as act_days_2021_exam,#2021年试卷作答活跃天数
    count(distinct(if(year(days) = 2021 and tag = 'question',days,null))) as act_days_2021_question#试卷作答记录和题目练习记录
from user_info
left join(
    #试卷作答记录和题目练习记录
    select distinct
        uid,
        date(start_time) as days,
        'exam' as tag
    from user_info
    left join exam_record using(uid)
    union
    select distinct
        uid,
        date(submit_time) as days,
        'question' as tag
    from user_info
    left join practice_record using(uid)
) main using(uid)
where level between 6 and 7
group by uid
order by  act_month_total desc,act_days_2021 desc#按照总活跃月份数、2021年活跃天数降序排序