题解 | #根据指定记录是否存在输出不同情况#

思路：

1. 筛选变量

• 未完成数：count(start_time)-count(submit_time)
• 未完成率：ifnull(1-count(submit_time)/count(start_time),0) /if(count(start_time) =0,0,1-count(submit_time)/count(start_time))

2. 筛选对象

• 存在level=0 and incomplete_cnt>2: 输出所有level=0用户
• 不存在level=0 and incomplete_cnt>2: 输出所有count(start_time)>0用户

3. 语法选择

• 变量池中除题目所需变量外也应容纳进等级、作答数用于筛选。
• 无作答记录的用户也有可能被输出，故两表连接以user_info为准
• 存在level=0 and incomplete_cnt>2的判断

方法1—— max()over与if结合，既不改变原表结构，又能增加一个判断全表状态的变量，再用where语句加以筛选

方法2——利用exists判断输出与否，但既然输出的衡量维度不一（等级/作答数），则用union all结合

代码1

with q as
(select ui.uid,level,count(start_time)-count(submit_time) as incomplete_cnt,
round(ifnull(1-count(submit_time)/count(start_time),0),3) as incomplete_rate,
 count(start_time) as complete_cnt
from user_info as ui
left join exam_record as er
on ui.uid=er.uid
group by uid)

select uid,incomplete_cnt,incomplete_rate
from q
where exists (select uid from q where level=0 and incomplete_cnt>2)
and level=0

union all
select uid,incomplete_cnt,incomplete_rate
from q
where not exists (select uid from q where level=0 and incomplete_cnt>2)
and complete_cnt>0
order by incomplete_rate

代码2

select uid,incomplete_cnt,incomplete_rate
from 
(select ui.uid,level,count(start_time) as complete_cnt,
count(start_time)-count(submit_time) as incomplete_cnt,
round(ifnull(1-count(submit_time)/count(start_time),0),3) 
as incomplete_rate ,
max(if(level=0 and count(start_time)-count(submit_time)>2,1,0)) over()
as flag
from user_info as ui
left join exam_record as er
 on ui.uid=er.uid
group by uid,level) as q

where (flag=1 and level=0) or (flag=0 and complete_cnt>0)
order by incomplete_rate