题目地址

https://www.nowcoder.com/practice/f72d3fc27dc14f3aae76ee9823ccca6b

思路

加了3列标记位，来达成目的。不直观而且占用内存，但是是一种方法。

代码

我的代码，加了3列标记位

# 在不使用SQL过程化编程的情况下，实现一个条件结构：

# 请你筛选表中的数据，当有任意一个0级用户未完成试卷数大于2时，输出每个0级用户的试卷未完成数和未完成率（保留3位小数）；若不存在这样的用户，则输出所有有作答记录的用户的这两个指标。结果按未完成率升序排序。

with total_info as( # 先建立一张大宽表

    select ui.*,er.exam_id,er.start_time,er.submit_time,er.score

    from user_info ui

    join exam_record er

    on ui.uid=er.uid

)

,

total_ans as  # 包含答案的全部信息

(

    select ui.uid,ui.level,

    (case when t1.incomplete_cnt is null then 0 else t1.incomplete_cnt end) as incomplete_cnt,

    (case when t1.exam_record_cnt is null then 0 else t1.exam_record_cnt end) as exam_record_cnt,

    (case when t1.incomplete_rate is null then 0.000 else t1.incomplete_rate end) as incomplete_rate  # 未完成率默认填0，保留3位小数后是0.000

    from user_info ui left join

   (select uid,

    count((case when submit_time is null then 1 else null end)) as incomplete_cnt,

    count(*) as exam_record_cnt,

    ROUND(count((case when submit_time is null then 1 else null end))/count(*),3) as incomplete_rate

    from total_info

    group by uid,level) t1

    on t1.uid=ui.uid

)

,

ans_flaged as (

    select *,

    (case when level=0 then 1 else 0 end) as out1_flag,  #如果走条件1，输出out1_flag为1的那些行。

    (case when exam_record_cnt>0 then 1 else 0 end) as out2_flag #如果走条件2，输出out2_flag为1的那些行。

    ,

    count(case when level=0 and incomplete_cnt>2 then 1 else null end) over() as if_flag  # 每一行的if_flag都是相同的值，如果if_flag>0走条件1，如果if_flag=0走条件2

    from total_ans

)

,

ans_to_be_refined as (  # 需要进一步提纯，得到最终结果

    select

    (case when if_flag>0 and out1_flag=1 then uid

    when if_flag=0 and out2_flag=1 then uid

    else  null

    end) as for_null_filter, # 为了之后where过滤使用

    uid,	incomplete_cnt,	incomplete_rate

    from ans_flaged

)

select uid,	incomplete_cnt,	incomplete_rate

from ans_to_be_refined

where for_null_filter is not null

order by incomplete_rate asc

等价的EXISTS代码，别人写的

### 等价的EXISTS代码，别人写的

# select ui.uid,count(*)-count(submit_time) incomplete_cnt,round(1-count(submit_time)/count(*),3) incomplete_rate

# from exam_record er

# left join user_info ui using(uid)

# where level = 0

# group by uid

# order by incomplete_rate

-- 第一步 做出所有人的两个指标

with t as

(

    select t.uid,level,sum(start_time is not null and submit_time is null) as incomplete_cnt,

        round(sum(start_time is not null and submit_time is null)/count(1),3) as incomplete_rate,

        count(exam_id) as num

    from user_info t

    left join exam_record t1 on t.uid = t1.uid

    group by t.uid

)

-- 第二步 冲

select uid,incomplete_cnt,incomplete_rate

from t

where EXISTS (

    select uid from t where level=0 and incomplete_cnt>2

) and level=0

union ALL

select uid,incomplete_cnt,incomplete_rate

from t

where not EXISTS (

    select uid from t where level=0 and incomplete_cnt>2

) and num>0

order by incomplete_rate

巴特西

在不使用SQL过程化编程的情况下，实现一个条件结构【SQL149 根据指定记录是否存在输出不同情况】

题目地址

思路

代码

我的代码，加了3列标记位

等价的EXISTS代码，别人写的

最新文章

热门文章