力扣高频sql50题(基础版):NULL,表连接,子查询,casewhen和avg的结合

NULL的处理  nvl(字段,num)

和数字进行比较需要先使用nvl(字段,num)函数处理空值 

思路: 没有被id = 2 的客户推荐>> 过滤条件 referee_id !=2

    没有被id = 2 的客户推荐>>被其他客户推荐, 但是也有可能没有被任何客户推荐>>NULL

考点:  NULL是 不一个具体的数值，而是一个特殊的标记，表示缺失值或未知值。因此,不能将 NULL 与数字进行直接比较  

oracle

select name from Customer where nvl(referee_id,0) !=2 空值判断 

要求NULL出现时,使用空值判断条件>> 字段 is null

思路:  结果集要求出现的字段>>姓名(name) ---表Employee  ; 奖金(Bonus) ----表Bonus >>表连接

empId 是 Employee 表中 empId 的外键(reference 列)>>连接字段 empId

连接方式>> 表Bonus的empID 来自表Employee, 表Employee拥有最完整的empID, 即全部的员工姓名, 而表Bonus在该员工没有奖金时,可能没有该empID >>左连接: 表Employee为主表

where 过滤条件: 每个奖金 少于 1000

注意: 当该员工没有奖金时,在表连接中Bonus为NULL>>使用or 以及空值判断条件

为什么使用nvl函数处理空值是错误查询?

因为sql的执行顺序是:  from (从表中取数据)>> on (根据连接条件进行表连接) >> where(对连接后的表数据按照条件进行过滤) >>select (展示数据集)

select 是最后才执行的, 把nvl函数用在select的字段上, 对数据过滤起不到作用,

where  bonus <1000 无法把NULL 过滤到结果集,因为NULL不属于一个数值,无法比较

表连接

思路

查询出每个学生参加门每一科目测试的次数>>每个学生(Students表); 每一科目(Subjects表) ; 科目测试(Examinations表)

多表连接: 每个学生的每一门科目>>Students表和Subjects表交叉连接

每一门科目测试的次数>>学生不一定参加每一门科目的测试>> left join  Examinations表(副表)

主表为前面的表>>保证每一个学生,每一门科目都出现在结果集中

连接条件: Examinations表的 student_id 和 Students表的 Students

Examinations表的 Subjects 和 Subjects 表的 Subjects

结果集: 学生id ; 学生名字; 科目测试的次数

科目测试的次数>>count(e.subject_name); 不适用count(*)是因为如果有学生没有参加某一门科目的考试,那么e.subject_name中为NULL, 不需要计入

oracle

select st.student_id ,st.student_name ,sb.subject_name ,count(e.subject_name) as attended_exams from Students st cross join Subjects sb left join Examinations e on e.student_id = st.student_id and e.subject_name = sb.subject_name group by st.student_id, st.student_name , sb.subject_name order by st.student_id , st.student_name

错误使用left join 连接学生表和科目表>>学生表是主表>>每个学生都会出现,无论有没有参加考试

但是不一定每个科目都出现,

子查询

思路: 求经理的name, 即经理的id >> 经理的id=员工的managerid ,

过滤条件: 每个经理有5个下属 >>分组 (managerid) + count()

select name from employee where id in(select managerid from employee group by managerid having count(id) >= 5) case when和avg

思路: 'confirmed' 消息的数量, 请求的确认消息的总数>>Confirmations表; 

每个用户的 确认率>>每个用户:  Signups表

Confirmations表的user_id是一个引用到注册表的外键>>注册表(Signups)做主表

注意: 求比例>>分母不为0

1.case  when 里面再嵌套一个case when 

表: Confirmations 是副表,当某个注册用户没有发送请求确认信息时,c.user_id是NULL, 即COUNT(b.user_id)=0, 在使用CASE WHEN b.action = 'confirmed' THEN 1 ELSE 0 END) / COUNT(b.user_id)时,需要处理分母为0的情况

SELECT a.user_id, ROUND( CASE WHEN COUNT(b.user_id) = 0 THEN 0 ELSE SUM(CASE WHEN b.action = 'confirmed' THEN 1 ELSE 0 END)/ COUNT(b.user_id) END, 2 ) AS confirmation_rate FROM Signups a LEFT JOIN Confirmations b ON a.user_id = b.user_id GROUP BY a.user_id;

2.case when 加  avg >>可以忽略NULL>>不需要处理NULL, sql更简洁

把(CASE WHEN action = 'confirmed' THEN 1 ELSE 0 END)作为avg()函数的字段, 不涉及COUNT(b.user_id)>>忽略表Confirmations的 user_id 字段的NULL, 这些注册用户没有发送请求确认信息, 也不属于需要计算确认率的用户

用户的 确认率 是 'confirmed' 消息的数量除以请求的确认消息的总数>>暗含: 发送请求确认信息的用户才需要计算确认率

SELECT a.user_id, ROUND(AVG(CASE WHEN action = 'confirmed' THEN 1 ELSE 0 END), 2) AS confirmation_rate FROM Signups a LEFT JOIN Confirmations b ON a.user_id = b.user_id GROUP BY a.user_id;