SQL刷题笔记——高级条件语句

目录

1题目：SQL149 根据指定记录是否存在输出不同情况

2 作答解析

3 知识点

3.1 count函数

3.2 内连接与左连接

1题目：SQL149 根据指定记录是否存在输出不同情况

2 作答解析

#正确答案
select 
uid, incomplete_cnt, incomplete_rate
from (
    select 
      ui.uid as uid, ui.level as `level`
    , count(er.start_time) as answer_cnt #每个用户的作答数量 判断是否有作答记录
    , count(if(er.submit_time is null, er.start_time, null)) as incomplete_cnt 
#可是count(*)会包括null计数,count(表达式/字段)不会计入null
#count 要注意后面0和null的区别！！！！
    , round( if( count(er.start_time) = 0, 0,count(if(er.submit_time is null, er.start_time, null))/count(er.start_time)), 3) as incomplete_rate
    #外面这个if是为了防止分母为0的错误,count(表达式/字段)不会计入null
    , max(if(ui.level=0 and count(if(er.submit_time is null, er.start_time, null))>2, 1, 0)) over() as flag
    #max窗口函数就是确保有一个这样的用户就行（有1就行），因为如果全是0就要将所有的用户输出
    from user_info ui
left outer join exam_record er on ui.uid = er.uid
# 这里是左连接，而不是内连接，要保证每个用户都有在，而表er中只有3种uid
    group by ui.uid, ui.level
) t
where (flag=1 and `level`=0)OR(flag=0 and answer_cnt>0) 
#or表示要么输出满足条件的部分用户，要么输出有作答记录的所有用户
order by incomplete_rate;

3 知识点

3.1 count函数

在 SQL 中，COUNT() 函数的计算规则是：

• COUNT(*) 会计算所有行，不管其中的值是否为 NULL。
• COUNT(表达式) 或 COUNT(字段) 只会计算非 NULL 的值，即 NULL 值会被排除在计数之外。

3.2 内连接与左连接

 1）内连接：只两个表保留相同的

2）左连接：合并后左边的表所有行都保留，若左边的表有空值则删除（即删除右边没有匹配上的）

此题中，如果是内连接，只能得到ui和er都有的数据表：

由题可知，er中只有1001,1002,1003这几个uid，如用内连接则丢失了1004,1005,1006这几个uid。

但如果是左连接，则会保证左边的表即ui中的数据都保留，：