力扣sql五十题——连接

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

题目

1661. 每台机器的进程平均运行时间 - 力扣（LeetCode）

思路

首先应该了解下面几个函数

• round：把数值字段舍入为指定的小数位数
• avg：用于计算一组值或表达式的平均值

然后将activity根据题目要求连接起来

代码

# Write your MySQL query statement below
select a.machine_id,
round(avg(a.timestamp-b.timestamp),3) as processing_time
from Activity as a
join Activity as b
on a.machine_id=b.machine_id
and a.process_id=b.process_id
and b.activity_type = 'start'
and a.activity_type = 'end'
group by a.machine_id

员工奖金

题目

577. 员工奖金 - 力扣（LeetCode）

思路

注意点在于不能写成b.bonus = null而要写成b.bonus is null

代码

# Write your MySQL query statement below
select e.name,b.bonus
from employee as e
left join bonus as b
on e.empId=b.empId
where b.bonus<1000
or b.bonus is null

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

题目

1280. 学生们参加各科测试的次数 - 力扣（LeetCode）

思路

1. Student表和Subjects表进行笛卡尔积连接(在第一点的基础上拼接Examinations中的每个学生参加每门科目的数量。
2. 学生名单必须完整，在Examinations表中不存在则为0。所以使用左连接LEFT JOIN进行连接

代码

# Write your MySQL query statement below
select s.student_id,s.student_name,su.subject_name,
count(e.subject_name) as attended_exams
from students as s
join subjects as su
left join examinations as e 
on s.student_id=e.student_id
and e.subject_name = su.subject_name
group by s.student_name,su.subject_name
order by s.student_id,su.subject_name

至少有五名直接下属的经理

题目

570. 至少有5名直接下属的经理 - 力扣（LeetCode）

思路

使用子查询先找出有五个直接下属的经理id，再找出具体的员工名字

了解having函数

mysql中，当我们用到聚合函数，如sum，count后，又需要筛选条件时，having就派上用场了，因为WHERE是在聚合前筛选记录的，having和group by是组合着用的

注意where之后要用in，不能写等于号

代码

# Write your MySQL query statement below
select name from employee
where id in 
(select managerId from employee 
group by managerId
having count(managerId)>=5)

确认率

题目

1934. 确认率 - 力扣（LeetCode）

思路

主要在于avg函数的应用

ifnull函数：把null值转化成对应的值

代码

# Write your MySQL query statement below
select
    s.user_id,
    ROUND(IFNULL(AVG(c.action='confirmed'), 0), 2) AS confirmation_rate
from
    Signups as s
left join
    Confirmations as c
on
    s.user_id = c.user_id
group by
    s.user_id