MYSQL 连接

高频 SQL 50 题（基础版） - 学习计划 - 力扣（LeetCode）全球极客挚爱的技术成长平台

1378. 使用唯一标识码替换员工ID

SELECT COALESCE(unique_id, NULL) AS unique_id,name
FROM Employees
LEFT JOIN EmployeeUNI ON  Employees.id = EmployeeUNI.id;

左连接（Left Join）是一种用于联接两个或多个表的操作，它返回左表中的所有行以及与右表中满足联接条件的匹配行。如果右表中没有与左表匹配的行，则对应的结果列将填充为 NULL 值。

左连接的语法如下：

SELECT 列名
FROM 左表
LEFT JOIN 右表 ON 连接条件;

1068. 产品销售分析 I

SELECT P.product_name,year,price
FROM Sales S
LEFT JOIN  Product P ON P.product_id = S.product_id;

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

SELECT customer_id,COUNT(customer_id) AS count_no_trans
FROM Visits V
LEFT JOIN Transactions T ON T.visit_id = V.visit_id 
WHERE transaction_id IS NULL
GROUP BY customer_id

197. 上升的温度

TIMESTAMPDIFF函数，小的日期前面

SELECT w1.id Id
FROM Weather AS w1,Weather AS w2
WHERE TIMESTAMPDIFF(DAY,w2.RecordDate,w1.RecordDate) = 1 AND w1.Temperature > w2.Temperature;

DATE_ADD(w2.RecordDate, INTERVAL 1 DAY)是一个日期函数，在给定的日期上添加指定的时

如果w2.RecordDate的值是'2023-10-17'，那么DATE_ADD(w2.RecordDate, INTERVAL 1 DAY)将返回'2023-10-18'。

连接后的表如下（左w1，右w2）：

 

SELECT w1.id Id
FROM Weather w1
LEFT JOIN(
    SELECT RecordDate,Temperature
    FROM Weather
)w2
ON w1.RecordDate = DATE_ADD(w2.RecordDate,INTERVAL 1 day)
WHERE w1.Temperature > w2.Temperature

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

子查询的表如下

 SUM(time)：算出来总运行时间

(COUNT(*)/2)：一个进程包括start和end两个时间戳，所以要除以2

SELECT machine_id,ROUND(SUM(time)/(COUNT(*)/2),3) processing_time
FROM (
    SELECT machine_id,process_id,IF(activity_type = 'start', round(-`timestamp`, 3), round(`timestamp` , 3)) time
    FROM Activity 
)t
GROUP BY machine_id;

577. 员工奖金

SELECT name,bonus
FROM (
    SELECT name,bonus
    FROM Employee E
    LEFT JOIN Bonus B ON E.empId = B.empId
) t
WHERE bonus < '1000' OR bonus IS NULL;

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

    SELECT student_id, subject_name, COUNT(*) AS attended_exams
    FROM Examinations
    GROUP BY student_id, subject_name

直接统计Examinations表

SELECT 
    *
FROM
    Students s
CROSS JOIN
    Subjects sub

用交叉连接CORSS JOIN将两张表连接

把成绩表左连接到做了自然连接的表，保证每个学生各门课字段都在，不会因为没参加考试就不算

SELECT stu.student_id,stu.student_name,s.subject_name,IFNULL(grouped.attended_exams,0) AS attended_exams
FROM Students stu 
CROSS JOIN Subjects s 
LEFT JOIN(
    SELECT student_id, subject_name, COUNT(*) AS attended_exams
    FROM Examinations
    GROUP BY student_id, subject_name
)grouped
ON grouped.student_id = stu.student_id AND grouped.subject_name = s.subject_name
ORDER BY stu.student_id,s.subject_name

570. 至少有5名直接下属的经理

SELECT name
FROM Employee
WHERE id IN (
    SELECT managerId
    FROM Employee
    GROUP BY managerId 
    HAVING COUNT(*) >= 5
) 

1934. 确认率

SELECT sign.user_id,IFNULL(ROUND(hit/total,2),0.00) confirmation_rate
FROM Signups sign
LEFT JOIN(
    SELECT user_id,time_stamp,COUNT(action) total
    FROM Confirmations
    GROUP BY user_id 
)grouped 
ON grouped.user_id = sign.user_id
LEFT JOIN(
    SELECT user_id,COUNT(action) hit
    FROM Confirmations
    WHERE action = 'confirmed'
    GROUP BY user_id 
)hitcount
ON hitcount.user_id = sign.user_id