高频SQL 50题（基础版）：连接

SELECT <select_list>
FROM TableA A
INNER JOIN TableB B
ON A.Key = B.Key

SELECT <select_list>
FROM TableA A
LEFT JOIN TableB B
ON A.Key = B.Key

SELECT <select_list>
FROM TableA A
RIGHT JOIN TableB B
ON A.Key = B.Key

SELECT <select_list>
FROM TableA A
FULL OUTER JOIN TableB B
ON A.Key = B.Key

SELECT <select_list>
FROM TableA A
FULL OUTER JOIN TableB B
ON A.Key = B.Key
WHERE B.Key IS NULL;

SELECT <select_list>
FROM TableA A
RIGHT JOIN TableB B
ON A.Key = B.Key
WHERE A.Key IS NULL;

SELECT <select_list>
FROM TableA A
FULL OUTER JOIN TableB B
ON A.Key = B.Key
WHERE A.Key IS NULL
OR B.Key IS NULL;

SELECT EmployeeUNI.unique_id, Employees.name
FROM Employees
LEFT JOIN EmployeeUNI
ON Employees.id = EmployeeUNI.id;

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

SELECT customer_id, count(customer_id) as count_no_trans
FROM Visits
LEFT JOIN Transactions
ON Visits.visit_id = Transactions.visit_id
WHERE transaction_id IS NULL
GROUP BY customer_id;

SELECT customer_id, count(visit_id) as count_no_trans
FROM Visits
WHERE visit_id NOT IN (SELECT DISTINCT visit_id FROM Transactions)
GROUP BY customer_id;

# Write your MySQL query statement below
SELECT 
    id
FROM 
    (SELECT
        id,
        temperature,
        recordDate,
        LAG(recordDate,1) OVER(ORDER BY recordDate) as last_date,
        LAG(temperature,1) OVER(ORDER BY recordDate) as last_temperature
    FROM
        weather) a
WHERE temperature > last_temperature and DATEDIFF(recordDate, last_date) = 1;

SELECT
    b.id
FROM
    weather a
INNER JOIN
    weather b
WHERE
    DATEDIFF(b.recordDate, a.recordDate)=1
　　 AND b.temperature > a.temperature;

SELECT
    a.id
FROM
    weather a
INNER JOIN
    weather b
ON 
    (a.recordDate = adddate(b.recordDate, INTERVAL 1 day))
WHERE
    a.temperature > b.temperature;

SELECT
    b.id
FROM
    weather a, weather b
WHERE
    TIMESTAMPDIFF(DAY, a.RecordDate, b.RecordDate) = 1
    AND b.temperature > a.temperature;

SELECT
    a.id
FROM
    weather a
JOIN(
    SELECT
        recordDate, temperature
    FROM
        weather
) b
ON
    a.recordDate = adddate(b.recordDate, INTERVAL 1 day)
WHERE
    a.temperature > b.temperature;

SELECT
    a1.machine_id,
    ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time
FROM
    Activity AS a1
    JOIN Activity AS 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
    machine_id;

SELECT
    a1.name, a2.bonus
FROM
    employee a1
    LEFT JOIN bonus a2 ON
        a1.empId = a2.empId
WHERE a2.bonus < 1000 OR a2.bonus IS NULL;

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

SELECT
    *
FROM
    Students s
CROSS JOIN
    Subjects sub

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

select name
from (
    select 
        Manager.name as name,
        count(Report.Id) as cnt
    from
        Employee as Manager
    join
        Employee as Report
    on Manager.id = Report.ManagerId
    group by Manager.id
) as ReportCount
where cnt >= 5;

select 
    Manager.name as name
from
    Employee as Manager
join
    Employee as Report
on Manager.id = Report.ManagerId
group by Manager.id
having count(Manager.id) >= 5

select Employee.name
from (
    select managerId as id
    from Employee
    group by managerId
    having count(id) >= 5
) as Manager
join Employee
on Manager.id = Employee.id;

select
  s.user_id,
  round(sum(if(action='confirmed', 1, 0)) / 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;

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