高频SQL50题刷题笔记

 1SELECT id, revenue  # 某个用户2021年可能有多项记录
 2FROM users
 3Where year = 2021
 4
 5SELECT id, SUM(revenue) 
 6FROM users
 7Where year = 2021  # 1. 先筛选出此年份所有记录
 8Group By id        # 2. 再根据id分组 3. 必须结合SUM() 或者其他聚合函数，因此这个有多列，需要处理为单值。
 9
10SELECT id
11FROM users
12Where year = 2021          # 1. 先筛选记录
13Group By id                # 2. 分组
14HAVING SUM(revenue) > 1000  #  3. 聚合分组字段记录 并 判断

1# 1. NOT EXISTS
2SELECT `name` AS `Customers`
3FROM `Customers` AS c
4WHERE NOT EXISTS (
5    SELECT `customerId`
6    FROM `Orders` AS o
7    Where o.customerId = c.id    
8)

 1# 2. Left Join 
 2# example
 3SELECT c.id AS id, c.name, o.id AS oid
 4FROM customers AS c LEFT JOIN orders AS o
 5on c.id = o.customerId 
 6
 7SELECT `name` as `Customers`
 8FROM customers AS c LEFT JOIN orders AS o
 9on c.id = o.customerId 
10WHERE o.id is NULL

1select `employee_id`, IF(name not like 'M%' and employee_id%2 = 1, salary, 0) AS `bonus` 
2from Employees 
3Order by employee_id ASC

 1select customer_id, customer_name 
 2from Customers  as c
 3where exists (
 4    select o.customer_id
 5    from Orders as o
 6    where o.customer_id=c.customer_id and product_name = 'A'
 7) and exists (
 8    select o.customer_id
 9    from Orders as o
10    where o.customer_id=c.customer_id and product_name = 'B'
11) and not exists (
12    select o.customer_id
13    from Orders as o
14    where o.customer_id=c.customer_id and product_name = 'C'
15)
16Order By c.customer_id  ASC

1SELECT c.customer_id, c.customer_name 
2FROM `Orders` as o left join `Customers` as c
3on o.customer_id = c.customer_id
4Group By o.customer_id
5HAVING SUM(product_name = 'A') > 0 and SUM(product_name = 'B') > 0 and SUM(product_name = 'C') = 0
6Order By c.customer_id ASC

 1[o.id, o.customerId, o.product_name, c.customerId, c.name]
 2 1       1            'A'              1           X
 3 2       1            'B'              1           X
 4 3       2            'A'              2           Y
 5 4       2            'B'              2           Y
 6 5       2            'C'              2           Y
 7 
 8 Group By c.customerId
 9 1       1            'A'              1           X
10 2       1            'B'              1           X
11 
12 3       2            'A'              2           Y
13 4       2            'B'              2           Y
14 5       2            'C'              2           Y
15 
16 SUM(express condition) ? condition # 对于String是统计条数

1+---------------+---------+
2| Column Name   | Type    |
3+---------------+---------+
4| student_id    | int     |
5| course_id     | int     |
6| grade         | int     |
7+---------------+---------+

1# 查某学生成绩最好的那些科目，因为可能有重复的
2SELECT student_id, MAX(grade) AS grade
3From Enrollments
4Group By student_id

 1# 查某学生最好的那些科目中 courseId最小的
 2SELECT student_id, MIN(courseId) AS course_id, grade
 3From Enrollments
 4# 行 => 最好的那些科目
 5Where (student_id, grade) in (
 6	SELECT student_id, MAX(grade) AS grade
 7	From Enrollments
 8	Group By student_id
 9)       
10Group By student_id
11Order By student_id ASC

1SELECT *
2FROM `customers` as c
3LEFT JOIN orders as o
4ON c.id = o.customerId

1SELECT seller_name
2FROM Seller
3WHERE seller_id not in (
4    SELECT DISTINCT Orders.seller_id
5    FROM Orders  
6    WHERE Orders.sale_date like '2020%'
7)
8ORDER By seller_name ASC

1SELECT name, COALESCE(SUM(distance), 0) AS travelled_distance
2From Users Left join Rides
3On Rides.user_id = Users.id  
4Group By Rides.user_id 
5ORDER BY travelled_distance DESC, name ASC;  # 使用别名排序

1SELECT name
2FROM SalesPerson
3WHERE sales_id not in (
4    SELECT DISTINCT Orders.sales_id
5    FROM Orders LEFT JOIN Company 
6    ON Orders.com_id = Company.com_id
7    WHERE name = 'RED'
8)

 1SELECT e.left_operand, e.operator, e.right_operand,
 2CASE e.operator
 3    WHEN '>' THEN IF(v1.value>v2.value, 'true', 'false')
 4    WHEN '<' THEN IF(v1.value<v2.value, 'true', 'false') 
 5    ELSE IF(v1.value=v2.value, 'true', 'false') 
 6END value
 7
 8FROM Expressions e
 9LEFT JOIN Variables v1 ON e.left_operand = v1.name 
10LEFT JOIN Variables v2 ON e.right_operand = v2.name 

 1SELECT t.team_id, t.team_name, IFNULL(SUM(num_points), 0) as num_points
 2FROM Teams AS t
 3LEFT JOIN (
 4    SELECT host_team AS team_id,
 5    CASE
 6        WHEN host_goals > guest_goals THEN 3
 7        WHEN host_goals = guest_goals THEN 1 
 8    END AS num_points
 9    FROM Matches
10    UNION ALL
11    SELECT guest_team AS team_id,
12    CASE
13        WHEN guest_goals > host_goals THEN 3
14        WHEN host_goals = guest_goals THEN 1 
15    END AS num_points
16    FROM Matches
17) AS m ON t.team_id = m.team_id
18Group By t.team_id
19Order BY num_points DESC, t.team_id ASC 

1SELECT MAX(Feild)  # <= 只要组内最大的记录
2	   Min(Feild)  # <= 只要组内最小的记录
3...
4
5Group By

1SELECT Warehouse.name AS warehouse_name, 
2SUM(units*Products.Width*Products.Length*Products.Height) AS volume   # 聚合组内数据
3FROM Warehouse 
4LEFT JOIN Products ON Warehouse.product_id = Products.product_id  # 补齐信息
5Group By Warehouse.name                                           # 分组

 1SELECT customer_number 
 2FROM Orders 
 3GROUP BY customer_number
 4ORDER BY COUNT(*) DESC  # 对组进行排序
 5LIMIT 1                 # Top位置
 6
 7
 8SELECT o.customer_number
 9FROM (
10	SELECT customer_number, COUNT(*) AS count_number
11	FROM Orders 
12	GROUP BY customer_number
13) o
14ORDER BY o.count_number DESC
15LIMIT 1

1SELECT `day`, emp_id, SUM(out_time-in_time)
2FROM Employees
3GROUP BY emp_id, event_day 

1SELECT ROUND((SUM(IF(order_date = customer_pref_delivery_date, 1, 0)))/COUNT(delivery_id)*100, 2)  AS immediate_percentage 
2FROM Delivery

1SELECT sale_date,
2(SUM(IF(fruit='apples', sold_num, 0)) - SUM(IF(fruit='oranges', sold_num, 0))) AS diff
3FROM Sales
4Group By sale_date

 1SELECT from_id AS person1, to_id AS person2, COUNT(*) AS call_count,SUM(duration) AS total_duration 
 2
 3FROM (
 4
 5  SELECT IF(from_id < to_id, from_id, to_id) AS from_id, IF(from_id < to_id, to_id, from_id) AS to_id, duration
 6
 7  FROM Calls
 8
 9) AS c
10
11Group BY from_id, to_id

1SELECT name, balance
2FROM Users 
3LEFT JOIN (
4    SELECT account, SUM(amount) AS balance
5    FROM Transactions
6    Group BY account) AS trans On Users.account = trans.account
7Where balance > 10000

1SELECT email AS Email
2FROM Person
3Group By email
4HAVING count(*) > 1

1SELECT actor_id, director_id
2FROM ActorDirector
3GROUP BY actor_id, director_id
4HAVING COUNT(*) >= 3

 1SELECT o.customer_id, name
 2FROM () AS o
 3LEFT JOIN Customers ON o.customer_id = Customers.customer_id
 4
 5
 6# 其中
 7SELECT customer_id, order_date, (quantity*price) AS cost
 8FROM Orders 
 9LEFT JOIN Product ON Orders.product_id = Product.product_id
10WHERE month(order_date) in (6,7)
11Group BY customer_id
12HAVING SUM(IF(month(order_date) = 6, cost, 0)) >= 100 and
13       SUM(IF(month(order_date) = 7, cost, 0)) >= 100

 1SELECT name as country 
 2FROM 
 3(
 4    SELECT caller_id AS call_id, duration FROM Calls 
 5    UNION ALL
 6    SELECT callee_id AS call_id, duration FROM Calls
 7) a
 8LEFT JOIN 
 9(
10    SELECT id, Country.name
11    FROM Person 
12    LEFT JOIN Country on SUBSTR(phone_number, 1, 3) = country_code
13) b
14ON a.call_id = b.id
15Group BY name
16HAVING AVG(duration) > (SELECT AVG(duration) FROM Calls)

 1SELECT DISTINCT page_id as recommended_page 
 2FROM (SELECT user2_id as user_id
 3    FROM Friendship 
 4    WHERE user1_id = 1
 5    UNION
 6    SELECT user1_id as user_id
 7    FROM Friendship 
 8    WHERE user2_id = 1) AS fs
 9LEFT JOIN Likes ls ON fs.user_id = ls.user_id 
10WHERE page_id not in (
11    SELECT page_id
12    FROM Likes 
13    WHERE user_id = 1
14)
15ORDER BY recommended_page ASC

1SELECT id AS `id`, 
2CASE
3    WHEN id = 1 THEN "Root"
4    WHEN id in (SELECT atree.p_id FROM tree atree) THEN "Inner"
5    ELSE "Leaf"
6END AS type 
7FROM Tree 

1SELECT a1.player_id, a1.event_date, SUM(a2.games_played) AS games_played_so_far
2FROM Activity a1, Activity a2  # 笛卡儿积，o(n) x o(n) = o(n^2) 条纪录
3WHERE a1.player_id = a2.player_id and a1.event_date >= a2.event_date   # 只要自己和自己进行连接的记录
4GROUP BY a1.player_id, a1.event_date

 1SELECT res.player_id, ps.player_name, res.grand_slams_count 
 2FROM (
 3    SELECT player_id, COUNT(*) AS grand_slams_count
 4    FROM (
 5        SELECT Wimbledon player_id
 6        FROM Championships 
 7        UNION ALL
 8        SELECT Fr_open player_id
 9        FROM Championships 
10        UNION ALL
11        SELECT US_open player_id
12        FROM Championships 
13        UNION ALL
14        SELECT Au_open player_id
15        FROM Championships 
16    ) AS rec
17    GROUP BY rec.player_id 
18) AS res
19LEFT JOIN Players AS ps ON res.player_id = ps.player_id

1AND ((a.login between b.login and b.logout) OR (a.logout between b.login and b.logout))

1SELECT d.name AS Department, e1.name AS Employee, e1.salary AS Salary 
2FROM Employee e1 
3LEFT JOIN Department d ON e1.departmentId = d.id
4WHERE e1.salary = (
5    SELECT MAX(salary)
6    FROM Employee e2
7    GROUP BY e2.departmentId
8    HAVING e2.departmentId = e1.departmentId
9)

1SELECT d.name AS Department, e1.name AS Employee, e1.salary AS Salary 
2FROM Employee e1 
3LEFT JOIN Department d ON e1.departmentId = d.id
4WHERE (e1.departmentId,e1.salary) in (
5    SELECT e2.departmentId, MAX(salary)
6    FROM Employee e2
7    GROUP BY e2.departmentId
8)

1WITH MaxSalaries AS (
2    SELECT departmentId, MAX(salary) AS max_salary
3    FROM Employee
4    GROUP BY departmentId
5)

 1With LatestDate AS (
 2    SELECT product_id, MAX(order_date) AS order_date
 3    FROM Orders
 4    GROUP BY product_id
 5)
 6
 7
 8SELECT p.product_name, o.product_id, o.order_id, o.order_date
 9FROM Orders AS o
10LEFT JOIN LatestDate AS ld ON o.product_id = ld.product_id
11LEFT JOIN Products AS p ON o.product_id = p.product_id
12WHERE o.order_date = ld.order_date
13ORDER BY p.product_name ASC, o.product_id ASC, o.order_id ASC

1ROW_NUMBER() OVER (
2	PARTITION BY ?
3    ORDER BY ?
4)

 1WITH ORDER_INFO AS (
 2    SELECT c.name, c.customer_id, o.order_id, o.order_date,
 3    ROW_NUMBER() OVER (
 4        PARTITION BY o.customer_id
 5        ORDER BY o.order_date DESC
 6    ) AS row_num
 7    FROM Orders AS o
 8    LEFT JOIN Customers AS c ON o.customer_id = c.customer_id
 9)
10
11SELECT name AS customer_name, customer_id, order_id, order_date
12FROM ORDER_INFO AS o
13WHERE o.row_num <= 3
14ORDER BY customer_name ASC, customer_id ASC, order_date DESC

 1WITH MAX_AMOUNT AS (
 2    SELECT DATE(`day`) AS `day`, MAX(amount) max_amount 
 3    FROM Transactions AS t
 4    GROUP BY DATE(`day`)
 5)
 6
 7
 8SELECT transaction_id
 9FROM Transactions AS t
10LEFT JOIN MAX_AMOUNT AS ma ON DATE(t.day) = ma.day
11WHERE (DATE(t.day), t.amount) = (ma.day, ma.max_amount)
12ORDER BY transaction_id ASC

1RANK() OVER (
2) 

 1WITH Temp AS (
 2    SELECT transaction_id, `day`, amount,
 3    rank() OVER (
 4        PARTITION BY DATE(`day`)
 5        ORDER BY amount DESC
 6    ) AS idx
 7    FROM Transactions AS t
 8)
 9
10SELECT transaction_id
11FROM Temp as t
12WHERE idx = 1
13ORDER BY transaction_id ASC

 1WITH Temp AS (
 2    SELECT p.project_id, p.employee_id,
 3    RANK() OVER (
 4        PARTITION BY p.project_id
 5        ORDER BY e.experience_years DESC
 6    ) AS idx
 7    FROM Project  AS p
 8    LEFT JOIN Employee as e ON p.employee_id = e.employee_id
 9)
10
11
12SELECT project_id, employee_id
13FROM Temp
14WHERE idx = 1

1SELECT 
2    customer_id,
3    product_id,
4    COUNT(*) AS order_count,
5    RANK() OVER (ORDER BY COUNT(*) DESC) AS RK
6FROM Orders
7GROUP BY customer_id, product_id;

1SELECT 
2    customer_id,
3    product_id,
4    COUNT(*) AS order_count,
5    RANK() OVER (PARTITION BY customer_id ORDER BY COUNT(*) DESC) AS RK
6FROM Orders
7GROUP BY customer_id, product_id;

 1WITH Temp AS (
 2    SELECT 
 3        customer_id,
 4        product_id,
 5        COUNT(*) AS order_count,
 6        RANK() OVER (PARTITION BY customer_id ORDER BY COUNT(*) DESC) AS RK  
 7    FROM Orders
 8    GROUP BY customer_id, product_id
 9)
10
11SELECT t.customer_id, t.product_id, p.product_name 
12FROM Temp AS t
13LEFT JOIN Products AS p ON t.product_id = p.product_id 
14WHERE RK = 1  # 最高的 NO.1

1SELECT user_id, MAX(DATEDIFF(after_day, visit_date)) AS biggest_window
2FROM (
3    SELECT user_id, visit_date,
4    LEAD(visit_date, 1, '2021-1-1') OVER (PARTITION BY user_id ORDER BY visit_date ASC) AS after_day
5    FROM UserVisits AS uv
6) AS t
7GROUP BY user_id

 1SELECT employee_id
 2FROM (
 3    SELECT employee_id
 4    FROM Employees 
 5    WHERE manager_id = 1 and employee_id != 1
 6    UNION
 7    SELECT employee_id
 8    FROM Employees
 9    WHERE manager_id in (
10        SELECT employee_id
11        FROM (SELECT employee_id
12              FROM Employees 
13              WHERE manager_id = 1 and employee_id != 1) AS e1
14    )
15    UNION 
16    SELECT employee_id
17    FROM Employees
18    WHERE manager_id in (
19        SELECT employee_id
20        FROM (SELECT employee_id
21            FROM Employees
22            WHERE manager_id in (
23                SELECT employee_id
24                FROM (SELECT employee_id
25                      FROM Employees 
26                      WHERE manager_id = 1 and employee_id != 1) AS e2
27            )) e3
28    )
29) AS e

 1SELECT employee_id
 2FROM (
 3    SELECT employee_id
 4    FROM Employees 
 5    WHERE manager_id = 1 and employee_id != 1  # ✔️
 6    UNION
 7    
 8    SELECT employee_id
 9    FROM Employees
10    WHERE manager_id in (
11        SELECT employee_id
12        FROM Employees 
13        WHERE manager_id = 1 and employee_id != 1
14    )
15    UNION 
16    
17    SELECT employee_id
18    FROM Employees
19    WHERE manager_id in (
20        SELECT employee_id
21        FROM Employees
22        WHERE manager_id in (
23            SELECT employee_id
24            FROM Employees 
25            WHERE manager_id = 1 and employee_id != 1
26        )
27    )
28) AS e

1SELECT DISTINCT e4.employee_id
2FROM Employees e1
3LEFT JOIN Employees e2 ON e1.employee_id = e2.manager_id  
4LEFT JOIN Employees e3 ON e2.employee_id = e3.manager_id  
5LEFT JOIN Employees e4 ON e3.employee_id = e4.manager_id  
6WHERE e1.employee_id = 1 and e4.employee_id != 1

 11 Boss => {
 2    	1 Boss     => {
 3                       1 Boss                 => {
 4                           					     1 Boss 
 5                                                 2 Bob
 6       											 77 Robert
 7                       }
 8                       2 Bob                  => 4 Daniel
 9        			   77 Robert              => null
10        }
11        2 Bob       =>  4 Daniel              => null   
12        77 Robert   => null                   => null   
13}

1data:  1,2,3,7,8,10
2idx :  1,2,3,4,5, 6
3diff:  0,0,0,3,3, 4   <= GROUP BY diff => MIN(idx) AS Start_ID, MAX(idx) AS End_ID

 1WITH Temp AS (
 2    SELECT log_id,
 3    ROW_NUMBER() OVER (ORDER BY log_id ASC) AS idx
 4    FROM Logs 
 5)
 6
 7SELECT MIN(log_id) AS start_id, MAX(log_id) AS end_id
 8FROM (
 9    SELECT log_id, (log_id-idx ) AS diff
10    FROM Temp 
11) AS t
12GROUP BY diff

 1WITH MAXMIN AS (
 2    SELECT exam_id, MIN(score) min_score, MAX(score) max_score
 3    FROM Exam 
 4    GROUP BY exam_id  
 5)  
 6
 7
 8SELECT s.student_id, s.student_name  
 9FROM (
10    SELECT e.exam_id, e.student_id, 
11    CASE
12        WHEN score != min_score and score != max_score THEN 1
13        ELSE 0
14    END AS is_medium
15    FROM Exam AS e
16    LEFT JOIN MAXMIN m ON e.exam_id = m.exam_id
17) AS t
18LEFT JOIN Student AS s ON t.student_id = s.student_id
19GROUP BY t.student_id
20HAVING SUM(t.is_medium) = COUNT(t.is_medium)
21ORDER BY student_id ASC

 1with recursive all_subtask(task_id, subtask_id) as (
 2    SELECT task_id, subtasks_count 
 3		FROM Tasks
 4    UNION ALL
 5		
 6    SELECT task_id, subtask_id-1 
 7		FROM all_subtask 
 8		where subtask_id-1 > 0
 9)
10
11
12SELECT task_id, subtask_id
13From all_subtask 
14WHERE (task_id, subtask_id) not in (SELECT task_id, subtask_id FROM Executed)

1with recursive TableName(param1, param2) AS (
2	SELECT param1, param2  // 基础结果
3	FROM BaseTable
4    UNION ALL
5    // 下面式子会被重复递归
6    SELECT param1, param2-1  // 新的结果
7    FROM TableName         // 上一轮的结果
8    where param2-1 > 0  // 递归终止条件
9)