连接join练习解答

count(null)对空值的处理

聚合函数对空值结果未null，统计null时会自动去除，直接计算count(null)为0

交易了却未消费的交易

通过金钱amount筛选未交易顾客存在漏洞，有的顾客交易了存在交易id但未消费，transaction有但amount为0，这时候应该根据交易id统计

on和where区别

日期函数计算天数、增加日期

连接

1. 1581.进店却未进行过交易的顾客

查找只光顾了购物中心但没有进行交易的顾客 ID ，以及他们只光顾不交易的次数。

交易表、顾客访问表
查询所有的访问顾客，即使没有交易；所以顾客访问表要全部记录；
采取左连接，右表交易表中没有的直接为null，
过滤出这些null的顾客，以顾客id分组，统计每位顾客的访问次数

以左连接的方式连接两张表，
customer_id,Visits.visit_id,amount

    select *
    from Visits left join Transactions
    on Visits.visit_id = Transactions.visit_id

过滤出这些null的记录，以顾客分组，统计每个顾客的null多少；

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

关于count对null的处理：
这里count(amount)或者count(transaction_id)等本身为空的内容，也就是count(null)，结果恒为0；这里应该count(visit_id)或count(customer_id)等非null的值；

大部分集合函数在进行计算时都去除NULL值，count(字段)也不计算null值；
但是count(1)与count(*)得到的结果一致，包含null值；
count(null)结果恒为0；

如何处理null值，或者进行合法的代替：

IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。
IFNULL(expr1,expr2) 函数用于判断第一个表达式是否为 NULL，如果为 NULL ，则返回第二个参数的值，如果不为 NULL， 则返回第一个参数的值
isnull(expr) 的用法
如expr 为null，那么isnull() 的返回值为 1，否则返回值为 0。

这里用amount is null进行筛选判断出错
原因：题目统计进店但未交易的顾客，有的顾客可能交易了但没花钱，所以不能用amount消费金钱来判断是否顾客交易；
或者说，有的顾客进店光顾了多次，有几次参观交易了金钱，但有的时候仅参观未交钱，如下面的顾客，参观了3次，有两次参观消费了，但有一次参观未消费；这样的顾客不能算进店却未交易的顾客；所以用amount is null判断不准确。用交易id或交易表中的visit_id都可以。

最后：

2. 使用唯一标识码替换员工身份id

关于on和where区别

ON 和 WHERE 在 SQL 中都用于设置条件，但它们在连接（JOIN）操作中的作用是不同的。
ON：在 JOIN 操作中，ON 关键字用于设置连接条件。例如，在你的查询中，ON Employees.id=EmployeeUNI.id 是连接 Employees 表和 EmployeeUNI 表的条件，即只有当两个表中的 id 相等时，才会将这两个表的行连接在一起。
WHERE：WHERE 关键字用于过滤结果集，即只返回满足特定条件的行。如果你在查询中使用 WHERE Employees.id=EmployeeUNI.id，那么只有当 Employees 表和 EmployeeUNI 表中的 id 相等时，才会在结果集中返回这些行。
总的来说，ON 关键字定义了如何连接两个表，而 WHERE 关键字定义了如何过滤这些连接后的结果。在某些情况下，使用 ON 和 WHERE 可能会得到相同的结果，但在其他情况下，结果可能会有所不同，特别是在执行外连接（如 LEFT JOIN 或 RIGHT JOIN）时。
内连接（INNER JOIN）只返回两个表中都有匹配的行。
左连接（LEFT JOIN）返回左表（这里是 Employees 表）的所有行，如果右表（这里是 EmployeeUNI 表）中没有匹配的行，则结果集中的对应列将为 NULL。
WHERE 子句返回满足特定条件的行，这里的条件是 Employees.id 等于 EmployeeUNI.id。

获取每位用户的 唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。这里没有用到case when| if，直接left join就处理了不匹配的null。

3. 上升的温度

找出今天比昨天温度高的id

• 思路：表连接起来，连接类型，笛卡尔积、内连接都可以，连接条件on是今天大于昨天1个day，过滤条件where是今天温度大于昨天；

• 连接类型： 表自连接，内连接inner join、join、笛卡尔积cross join、自然连接natural join；

• cross join 也就是交叉连接，直接两个表求笛卡尔积，而在求笛卡尔积的基础上，又可以分为内连接 inner join/join 和外 outer join，内外连接都是先求笛卡尔积，然后在此基础上满足一定的条件，内连接是满足条件的那部分，而外连接则要加上不满足条件的那部分。 总之，内连接和交叉连接其实可以互换，改下限制条件而已。J
• OIN 不加 ON 就是CROSS JOIN。JOIN 加ON 就是INNER JOIN

• 连接条件，日期的计算：

如何找到 “昨天”（前一天），这里为大家介绍两个时间计算的函数：

datediff(日期1, 日期2)：
得到的结果是日期1与日期2相差的天数。日期1-日期2

timestampdiff(时间类型, 日期1, 日期2)
日期2-日期1；在“时间类型”的参数位置，通过添加“day”, “hour”, “second”等关键词，来规定计算天数差、小时数差、还是分钟数差。

我这里用的date_add(日期，interval 1 day)，日期+1。左表日期=右边日期+1，左表日期-右表日期=1；这表示左表是今天，右表是昨天；如果左表日期=右边日期-1，表示右表大，右表是今天，那么后面比较温度、取值的时候，记得左表温度应该<右表，取右表的id

	select Id 
	from weather a join weather b
	on a.recordDate = date_add(b.recordDate,interval 1 day)
	where a.temperature > b.temperature

	select a.id
	from weather a cross join weather b
	on datediff(a.recordDate,b.recordDate)=1
	where a.temperature > b.temperature

注意：针对两个表共有的列，一定要用表.列指定
Column ‘Id’ in field list is ambiguous 直接写id列名具有二义性，是不确定的

4.每台机器的进程平均运行时间

解法一、avg计算平均

表自连接，通过机器id和进程id连接两张表，类似于笛卡尔积，每组(机器id,进程id)都会对应多组(机器id,进程id,…)，1对n，再n对n；但每组(机器id,进程id)都只有一组(机器id,进程id,start,end)

根据(机器id,进程id)自连接，查询连接后的所有行(笛卡尔积)
select *
from  Activity a1 inner JOIN Activity a2
WHERE a1.machine_id = a2.machine_id
      AND a1.process_id = a2.process_id 

找出每组(机器id,进程id)唯一的(机器id,进程id,start,end)
select *
from  Activity a1 inner JOIN Activity a2
WHERE a1.machine_id = a2.machine_id
      AND a1.process_id = a2.process_id 
      AND a1.activity_type = 'start'
      AND a2.activity_type = 'end'

过滤出机器id，计算出每组(机器id,进程id,start,end)的进程时间
select a1.machine_id , (a2.timestamp -a1.timestamp) processing_time 
from  Activity a1 inner JOIN Activity a2
WHERE a1.machine_id = a2.machine_id
      AND a1.process_id = a2.process_id 
      AND a1.activity_type = 'start'
      AND a2.activity_type = 'end'

以机器id分组，计算出每组机器的平均值
select a1.machine_id ,round(avg(a2.timestamp -a1.timestamp ),3)processing_time 
from  Activity a1 inner JOIN Activity a2
WHERE a1.machine_id = a2.machine_id
      AND a1.process_id = a2.process_id 
      AND a1.activity_type = 'start'
      AND a2.activity_type = 'end'
group by a1.machine_id

解法二、sum/count计算平均

针对这张表

以机器id分组，
计算和：进程时间=结束-开始。如果type是start，变为负值，-start；如果type是end，不变，
统计每个机器id的所有进程时间，=-start+end-start+end，即结束-开始之和；
统计进程数：直接count(distinct process_id)，统计每个机器有几个不同的进程Id；

SELECT machine_id,
    round(sum(if(activity_type='start',-timestamp,timestamp))
    /count(distinct(process_id )) ,3) as processing_time 
from Activity
group by machine_id

5. 577.员工奖金

查找每个奖金少于1000的员工姓名和奖金数额
不存在员工只出现在 Bonus 表中的情况，且Bonus 有空，所以左连接Employee表作主表

	select *
	from Employee e left join Bonus b
	on e.empId = b.empId 

解法一、is null和<处理过滤条件

除了判断奖金<1000的员工，还隐藏没有奖金的；
所以用is null 筛选处理null值，

	select name ,bonus  
	from Employee e left join Bonus b
	on e.empId = b.empId 
	where b.bonus < 1000 or bonus is null 

解法二、if null空值转为0

	select name ,bonus  
	from Employee e left join Bonus b
	on e.empId = b.empId 
	where ifnull(bonus,0)<1000

6. 1280. 学生们参加各科测试的次数

查询每个学生参加每一门科目测试的次数

解法一、子查询

1. 这里的考试表是学生id与考试科目的数量表，存在同名学生多次参加同一门学科、或者一次也没参加该学科的现象；以学生和科目进行分组，统计每名学生参加所选科目的次数，得到一个学生参加科目次数的表；

	select student_id,subject_name,count(1)attended_exams
	from Examinations
	group by student_id,subject_name

2. 这里是查询每一门科目测试的次数，就算没参见显示0也要列出；所以应该是学生、科目的一次交叉连接，或者叫笛卡尔积，获取学生与所有科目的一个全表；

	select *
	from Students cross join Subjects

3. 最后，这里肯定是左连接学生科目的全表，科目参加次数表中存在null的情况，即一次也没参加的科目，处理null值为0；最后还要按id和课程进行排序

	select a.student_id,a.student_name,b.subject_name,ifnull(c.attended_exams,0)attended_exams
	from Students a
	cross join Subjects b
	left join (
	    select student_id,subject_name,count(1)attended_exams
	    from Examinations
	    group by student_id,subject_name
	)c
	on a.student_id = c.student_id and b.subject_name = c.subject_name
	order by student_id,subject_name