【SQL高频练习带刷】day2：连接查询

题目一：使用唯一标识码替换员工ID

题目要求：

展示每位用户的 唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。

你可以以 任意 顺序返回结果表。

表结构：

 运行结果示例：

思路：

本题比较简单，只需要使用id字段连接两表即可，由于没有标识码的员工需要使用null代替，因此我们将员工表最为主表进行外连接

运行代码示例：

select unique_id, name
from Employees 
left join EmployeeUNI 
using (id)

题目二：产品销售分析

题目要求：

编写解决方案，以获取 Sales 表中所有 sale_id 对应的 product_name 以及该产品的所有 year 和 price 。

返回结果表 无顺序要求 。

表结构：

 运行结果示例：

思路：

同样使用left join即可实现

运行代码示例：

select product_name,year,price
from Sales
left join Product 
using(product_id)

题目三：进店却未进行过交易的顾客

题目要求：

有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个解决方案，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。

返回以 任何顺序 排序的结果表。

表结构：

 运行结果示例：

思路：

本题难点在于对题目的理解上，其实仔细读题就会发现依旧考察的是外连接，将Visits作为主表进行连接，连接完成后再筛选出transaction_id为空的行即可。查询只光顾不交易的次数可以使用count（）函数聚合统计，按customer_id分组即可。

运行代码示例：

select v.customer_id ,count(*) as count_no_trans 
from Visits v
left join Transactions t
on v.visit_id = t.visit_id
where t. transaction_id is null
group by customer_id

题目四：上升的温度

题目要求：

编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。

返回结果 无顺序要求 。

表结构：

 运行结果示例：

思路：

这道题使用自连接就可以，外连接也行，唯一需要注意的点是对于日期的处理，不能直接使用+-符号，而是要使用日期函数去进行处理，可以使用的函数有很多。

运行代码示例：

select t1.id  as Id
from Weather t1
join Weather t2
on DATEDIFF(t1.recordDate, t2.recordDate)=1
where t1.temperature > t2.temperature

题目五：每台机器的进程平均运行时间

题目要求：

现在有一个工厂网站由几台机器运行，每台机器上运行着 相同数量的进程 。编写解决方案，计算每台机器各自完成一个进程任务的平均耗时。

完成一个进程任务的时间指进程的'end' 时间戳 减去 'start' 时间戳。平均耗时通过计算每台机器上所有进程任务的总耗费时间除以机器上的总进程数量获得。

结果表必须包含machine_id（机器ID） 和对应的 average time（平均耗时） 别名 processing_time，且四舍五入保留3位小数。

以 任意顺序 返回表。

表结构：

 运行结果示例：

思路：

这道题整体上难度不高，但是很绕，需要考虑的点比较多。首先是对于开始时间和结束时间的处理，我们可以使用自连接，使用1.machine_id=a2.machine_id 和 a1.process_id=a2.process_id两个条件为表额外添加一份时间戳数据，然后限定条件，两个时间戳作差，限定 a1.activity_type ='start' and a2.activity_type ='end'即可。均值的计算使用avg（）函数就能计算出来了，按machine_id进行分组。结果的四舍五入使用round（数值，保留位数）得到。

运行代码示例：

select 
a1.machine_id,
round(avg(a2.timestamp -a1.timestamp ),3) as processing_time 
from  Activity as a1 join Activity as a2 
on a1.machine_id=a2.machine_id and a1.process_id=a2.process_id 
where a1.activity_type ='start' and a2.activity_type ='end' 
group by machine_id;