高频sql 50 题（基础2）

二、连接

1.使用唯一标识码的员工

题目描述

代码编写

select unique_id,name 
from Employees ee left join EmployeeUNI en 
on ee.id=en.id

LEFT JOIN 来确保将所有 Employees 表中的行都包含在结果中，如果 EmployeeUNI 表中没有匹配的行,则默认用 null 填充。

2.产品销售分析

题目描述

代码编写

select product_name,year,price
from Sales s left join Product p 
on s.product_id=p.product_id

3.进店却从未进行过交易的顾客

题目描述

代码编写

select v.customer_id, count(v.customer_id) as count_no_trans 
from Visits v left join Transactions t
on v.visit_id=t.visit_id
where v.visit_id not in (select visit_id from Transactions)
group by v.customer_id

题目的意思理解起来有点绕，简而言之是筛选出那些进店却没有任何交易的顾客（之前进行过交易的顾客在再次进店无任何交易也包括在内），利用左连接列出所有进店的顾客信息，并筛掉有交易的信息，剩下的信息进行分组统计。

4.上升的温度

题目描述

代码编写

select w.id from Weather w  join Weather wh 
on datediff(w.recordDate,wh.recordDate)=1
where w.Temperature>wh.Temperature

通过自连接的方式，我们可以把表格当成两个不同的表格去看，通过DATEDIFF函数将每一天和前一天的温度进行比较，从而找出温度比前一天的温度高的那天。

Tip：

DATEDIFF函数是一个日期函数，用于计算两个日期之间的差距，并以指定的时间单位返回结果，函数形式为DATEDIFF（end_date,start_date）。

5.机器进程平均运行时间

题目描述

代码编写

做法一（比较繁琐的思路）：

select a.machine_id,round(avg(a.timestamp-b.timestamp),3) as processing_time 
from Activity a join Activity b 
on a.machine_id=b.machine_id
where a.machine_id=b.machine_id and a.process_id=b.process_id and a.activity_type="end" and b.activity_type="start"
group by a.machine_id

通过自连接的方式把表格看成两个不同的表格a,b,当a表格和b表格的机器编码、进程相同，且a状态为开始，b状态为结束，两者时间相减，得到结果相加之后取平均。

做法二（比较取巧且简便的做法，参考网友做法）

SELECT machine_id, round(avg(if(activity_type = 'start', -timestamp, timestamp))*2, 3)as processing_time 
from Activity 
group by machine_id

利用if判断，当状态为开始，将时间取反（不用自己做差，直接取反），得到所有结果相加之后取平均，实际得到的结果由四位数字相加取平均得到，即sum（）/4，而根据题意，机器有两个进程，每个进程的时间差相加再取平均，总的运行时间再除于2得到平均运行时间，即sum（）/2，因此结果要再乘上2。

6.员工奖金

题目描述

代码编写

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

7.学生参加各科测试的次数

题目描述

代码编写

select s.student_id , s.student_name , sub.subject_name , count(e.subject_name) as attended_exams 
from Students s cross join Subjects sub left join Examinations e 
on s.student_id = e.student_id and sub.subject_name = e.subject_name 
group by s.student_id, sub.subject_name 
order by s.student_id ,sub.subject_name

通过交叉联接将表 Student 中的每一行与表 Subject 中的每一行组合在一起，得到两个表中的 student_id 和 subject_name 的所有可能组合（Examinations表格只记录了参加了考试的学生，无缺考学生信息，无法统计出全部结果，因此要先补全考试信息），再通过左连接将应考学生的信息组合一起，并统计每个学生参加每科考试的次数。

8.有直属下属的经理

题目描述

代码编写

select e1.name from Employee e1 join Employee e2 on e1.id=e2.managerId
group by e1.id
having count(e1.id)>=5

通过自连接将经理id和对应的员工id组合一起，对经理id进行分类，并统计经理id次数（对应直属员工个数）。

9.确认率

题目描述

代码编写

做法一：

select s.user_id,round(count(case when action='confirmed' then s.user_id end)/count(s.user_id),2) as confirmation_rate 
from Signups s left join Confirmations c 
on s.user_id=c.user_id
group by s.user_id

Tip：

count（case when then else end）是可以写逻辑，在上述代码可以省略else，此时默认为null，信息也不计入统计范围。

做法二：（参考网友做法）

select s.user_id as 'user_id', round((sum(if (c.action = 'confirmed', 1, 0))) / count(*), 2) as 'confirmation_rate'
from Signups s left join Confirmations c 
on s.user_id = c.user_id
group by s.user_id

sum和if组合使用