题48:
1、寻找用户推荐人
根据下表写一个查询语句,返回一个客户列表,列表中客户的推荐人的编号都不是2。
解题思路:
这个题需要注意的是referee_id中包含null值,任何值与null直接做运算的话,结果都是unknown。因此,筛选条件需要有两个,一个是选出推荐人不是2,二是推荐人是null的。
select name
from customer
where referee_id is null or referee_id !=2;
2、统计实验的数量
根据下表写一个 SQL 查询语句,以报告在给定三个实验平台中每种实验完成的次数。请注意,每一对(实验平台、实验名称)都应包含在输出中,包括平台上实验次数是零的。
其中:
- experiment_id 是这个表的主键;
- platform 是枚举类型的,取值是这三种 (‘Android’, ‘IOS’, ‘Web’) 之一;
- experiment_name 也是枚举类型的,取值是这三种 (‘Reading’, ‘Sports’, ‘Programming’) 之一;
- 这个表包含有关随机实验人员进行的实验的 ID、用于做实验的平台以及实验名称的信息。
解题思路:
(1)平台和实验,笛卡尔连接生成交叉完整集;
(2)基于生成表,左连接表Experiments;
(3)分组计数。
with a as
(select 'Android' platform
union
select 'IOS' platform
union
select 'Web' platform),
b as
(select 'Reading' experiment_name
union
select 'Sports' experiment_name
union
select 'Programming' experiment_name)
select a.platform, b.experiment_name, count(experiment_id) num_experiments
from a
join b
left join experiments e on a.platform = e.platform and b.experiment_name = e.experiment_name
group by a.platform, b.experiment_name
order by 1;