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;
