题解 | #某乎问答高质量的回答中用户属于各级别的数量#

# 思路：
# 1、两表连接，挑选回答数大于100的记录，并用case when生成等级区间字段
# 2、用group by 计算每个区间的人数（不去重复）
select t1.level_cut,count(t1.author_id) as num from (
select (case when a1.author_level >= 1 and a1.author_level <=2 then '1-2级' 
       when a1.author_level >= 3 and a1.author_level <=4 then '3-4级' 
       when a1.author_level >=5 and a1.author_level <=6 then '5-6级' 
       end ) as level_cut,a2.author_id from 
       answer_tb as a2 left join author_tb as a1 
       on a2.author_id = a1.author_id 
       where a2.char_len >= 100
) t1 group by t1.level_cut order by num desc