目录
一、力扣原题链接
二、题目描述
三、建表语句
四、题目分析
五、SQL解答
六、最终答案
七、验证
八、知识点
一、力扣原题链接
1990. 统计实验的数量
二、题目描述
表:
Experiments+-----------------+------+ | Column Name | Type | +-----------------+------+ | experiment_id | int | | platform | enum | | experiment_name | enum | +-----------------+------+experiment_id 是这个表的主键. platform 是枚举类型的，取值是这三种 ('Android', 'IOS', 'Web') 之一. experiment_name 也是枚举类型的，取值是这三种 ('Reading', 'Sports', 'Programming') 之一. 这个表包含有关随机实验人员进行的实验的 ID、用于做实验的平台以及实验名称的信息。写一个 SQL 查询语句，以报告在给定三个实验平台中每种实验完成的次数。请注意，每一对（实验平台、实验名称）都应包含在输出中，包括平台上实验次数是零的。
结果可以以任意顺序给出。
查询的结果如下所示：
示例：
输入： Experiments table: +---------------+----------+-----------------+ | experiment_id | platform | experiment_name | +---------------+----------+-----------------+ | 4 | IOS | Programming | | 13 | IOS | Sports | | 14 | Android | Reading | | 8 | Web | Reading | | 12 | Web | Reading | | 18 | Web | Programming | +---------------+----------+-----------------+ 输出： +----------+-----------------+-----------------+ | platform | experiment_name | num_experiments | +----------+-----------------+-----------------+ | Android | Reading | 1 | | Android | Sports | 0 | | Android | Programming | 0 | | IOS | Reading | 0 | | IOS | Sports | 1 | | IOS | Programming | 1 | | Web | Reading | 2 | | Web | Sports | 0 | | Web | Programming | 1 | +----------+-----------------+-----------------+ 解释： 在安卓平台上, 我们只做了一个"Reading" 实验. 在 "IOS" 平台上，我们做了一个"Sports" 实验和一个"Programming" 实验. 在 "Web" 平台上，我们做了两个"Reading" 实验和一个"Programming" 实验.
三、建表语句
drop table if exists Experiments;
Create table If Not Exists Experiments (experiment_id int, platform ENUM('Android', 'IOS', 'Web'), experiment_name ENUM('Reading', 'Sports', 'Programming'));
Truncate table Experiments;
insert into Experiments (experiment_id, platform, experiment_name) values ('4', 'IOS', 'Programming');
insert into Experiments (experiment_id, platform, experiment_name) values ('13', 'IOS', 'Sports');
insert into Experiments (experiment_id, platform, experiment_name) values ('14', 'Android', 'Reading');
insert into Experiments (experiment_id, platform, experiment_name) values ('8', 'Web', 'Reading');
insert into Experiments (experiment_id, platform, experiment_name) values ('12', 'Web', 'Reading');
insert into Experiments (experiment_id, platform, experiment_name) values ('18', 'Web', 'Programming');
四、题目分析
需求：
报告在给定三个实验平台中每种实验完成的次数，每一对（实验平台、实验名称）都应包含在输出中，包括平台上实验次数是零的
题解：
1、输出三个平台和三个实验的次数需要他们的所有组合（使用笛卡尔积）
2、统计现有已实验的次数
3、左外连接关联匹配出现有实验次数
4、没有实验的为null，使用ifnull将null赋值为0
五、SQL解答
with t_platform as (-- 1、生成平台表select 'Android' as platformunionselect 'IOS'unionselect 'Web'
),t_experiment as (-- 2、生成实验表select 'Reading' as experimentunionselect 'Programming'unionselect 'Sports'
),t as (select*,-- 3、统计现有实验的次数count(1) over(partition by platform,experiment_name) as num_experimentsfrom Experiments
)
selectdistincttp.platform,te.experiment as experiment_name,-- 6、没有实验的为null，使用ifnull将null赋值为0ifnull(num_experiments,0) as num_experiments
from t_platform tp
-- 4、笛卡尔积关联实验表和平台表生成所有的组合
join t_experiment te
-- 5、左连接关联匹配现有实验次数
left join t on t.platform = tp.platform and t.experiment_name = te.experiment
;六、最终答案
with t_platform as (-- 1、生成平台表select 'Android' as platformunionselect 'IOS'unionselect 'Web'
),t_experiment as (-- 2、生成实验表select 'Reading' as experimentunionselect 'Programming'unionselect 'Sports'
),t as (select*,-- 3、统计现有实验的次数count(1) over(partition by platform,experiment_name) as num_experimentsfrom Experiments
)
selectdistincttp.platform,te.experiment as experiment_name,-- 6、没有实验的为null，使用ifnull将null赋值为0ifnull(num_experiments,0) as num_experiments
from t_platform tp
-- 4、笛卡尔积关联实验表和平台表生成所有的组合
join t_experiment te
-- 5、左连接关联匹配现有实验次数
left join t on t.platform = tp.platform and t.experiment_name = te.experiment
;七、验证
八、知识点
在SQL（Structured Query Language）中，笛卡尔积（Cartesian product）是指两个或多个表通过查询连接时，如果没有指定有效的连接条件（ON子句），那么查询结果将包含所有可能的行组合。这意味着第一个表的每一行都会与第二个表的每一行配对，形成一个巨大的结果集，其中包含所有可能的组合。
