sql-链接

1378. 使用唯一标识码替换员工ID - 力扣（LeetCode）

SELECT e2.unique_id as unique_id,
    e1.name as name
FROM Employees e1
-- LEFT JOIN EmployeeUNI e2
--     ON e1.id = e2.id 

LEFT join EmployeeUNI e2
    using (id)

当两个表中的字段一模一样时（名称，数据格式），可用using

 

1068. 产品销售分析 I - 力扣（LeetCode）

select product_name,
    year,
    price
from Sales s
left join Product p
    on s.product_id = p.product_id
-- 用using更好

1581. 进店却未进行过交易的顾客 - 力扣（LeetCode）

select v.customer_id as 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 t.transaction_id is null
group by v.customer_id 

注意：选取客户id的次数作为没有交易的次数

最后根据id分类一下

 

197. 上升的温度 - 力扣（LeetCode）

select w2.id 
from Weather w1
join Weather w2 
    on w2.recordDate  = date_add(w1.recordDate, interval 1 day)
where w1.Temperature < w2.Temperature

注意一下date_add(time, inertval x day/year/month/week)函数用法

date_sub也是一样

datediff(datepart, startdate, enddate) 时间间隔

 

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

select 
    a2.machine_id as machine_id,
    round(sum(a2.timestamp-a1.timestamp) / count(a1.machine_id),3) as processing_time 
from Activity a1
join Activity a2
on 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 a2.machine_id

　　分两个表连接

一个表示start，一个表示end，相减得到时间

两个连接时，注意process_id和machine_id 要一致，表示同一个机器的同一个程序

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

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

 

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

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

group by s.student_id,su.subject_name 
order by s.student_id,su.subject_name 

　　注意：以学生姓名和科目分类,因为要统计哪个学生参加什么科目的次数。

学生与科目的之间笛卡尔积 cross join

这边说一下count中不能选取s.student_id，因为s.student_id是总学生id，而不是参加考试的学生id，肯定以统计的参加考试的学生的id为准

count(e.student_id) as attended_exams

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

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

两张表连接

分清楚哪张表是下属，哪张表是经理

group by 分组时要带上id，不然测试时名字会重复:)，有亿点无语

同理 having中要计算id不能是名字，因为会重复

1934. 确认率 - 力扣（LeetCode）

select s.user_id,
    round(ifnull(avg(c.action='confirmed'),0),2) as confirmation_rate 
from Signups s
left join Confirmations c
    on s.user_id = c.user_id 
group by s.user_id

　round(value,x)保留小数点后x位小数

ifnull(x,value)若x为空，则返回值为value

avg()计算平均值

注意这种叠加函数的使用，round函数肯定放在最后，ifnull和avg写法前后顺序没讲究，因为分母肯定不为0

round(ifnull(avg(c.action='confirmed'),0),2) as confirmation_rate

• 先计算avg(c.action='confirmed')得到确认率

• 如果用户没有确认记录，avg会返回NULL

• ifnull将NULL替换为0

round(avg(ifnull(c.action='confirmed',0)),2) as confirmation_rate

• 对于每条记录，先判断c.action='confirmed'（结果为1或0）

• 如果没有记录匹配（由于LEFT JOIN），c.action为NULL，表达式结果也为NULL

• ifnull将这些NULL替换为0

• avg计算所有值的平均值