1
FROM + JOIN     → 先确定数据源和关联
2
WHERE           → 行级过滤
3
GROUP BY        → 分组
4
HAVING          → 组级过滤
5
SELECT          → 选择列 / 聚合 / 窗口函数
6
ORDER BY        → 排序
7
LIMIT           → 截取

1
SELECT name, population, area
2
FROM World
3
WHERE area >= 3000000
4
   OR population >= 25000000;

1
SELECT name
2
FROM Customer
3
WHERE referee_id != 2 OR referee_id IS NULL;

1
SELECT tweet_id
2
FROM Tweets
3
WHERE LENGTH(content) > 15;

1
SELECT (SELECT DISTINCT salary
2
        FROM Employee
3
        ORDER BY salary DESC LIMIT 1 OFFSET 1 ) AS SecondHighestSalary;

1
原始 Employee 表          → DISTINCT 去重        → ORDER BY DESC    → LIMIT 1 OFFSET 1
2
┌────┬────────┐          ┌────────┐             ┌────────┐         ┌────────┐
3
│ id │ salary │          │ salary │             │ salary │         │ salary │
4
├────┼────────┤          ├────────┤             ├────────┤         ├────────┤
5
│ 1  │ 100    │          │ 100    │             │ 400    │         │ 200    │
6
│ 2  │ 200    │   →      │ 200    │     →       │ 300    │   →     │        │
7
│ 3  │ 300    │          │ 300    │             │ 200    │         │        │
8
│ 4  │ 300    │          │ 400    │             │ 100    │         │        │
9
│ 5  │ 400    │          └────────┘             └────────┘         └────────┘
10
└────┴────────┘

1
SELECT *
2
FROM cinema
3
WHERE description != 'boring' AND id % 2 = 1
4
ORDER BY rating DESC;

1
 INNER（内连接）              LEFT（左连接）
2
┌────┐  ┌────┐            ┌────┐  ┌────┐
3
│    │  │    │            │████│  │    │
4
│  ██│  │██  │            │████│  │██  │
5
│  ██│  │██  │            │████│  │██  │
6
│    │  │    │            │████│  │    │
7
└────┘  └────┘            └────┘  └────┘
8
 左  右    左  右            左  右    左  右
9

10
用同一组数据看结果差异（A=左表 Person，B=右表 Address，连接键 id）：
11

12
A 表（Person）              B 表（Address）
13
┌────┬──────┐              ┌────┬──────┐
14
│ id │ name │              │ id │ city │
15
├────┼──────┤              ├────┼──────┤
16
│ 1  │ Allen│              │ 2  │ NYC  │
17
│ 2  │ Bob  │              │ 3  │ Boston│
18
│ 3  │ Zack │              └────┴──────┘
19
└────┴──────┘
20

21
① INNER JOIN（内连接）——只保留 A、B 都能按 id 匹配的行
22
┌────┬──────┬──────┐
23
│ id │ name │ city │
24
├────┼──────┼──────┤
25
│ 2  │ Bob  │ NYC  │   ← A.2 = B.2 ✅
26
│ 3  │ Zack │ Boston│  ← A.3 = B.3 ✅
27
└────┴──────┴──────┘
28
（Allen 在 B 中无匹配 → 不出现）
29

30
② LEFT JOIN（左连接）——保留 A 的全部行，B 无匹配用 NULL 填充
31
┌────┬──────┬──────┐
32
│ id │ name │ city │
33
├────┼──────┼──────┤
34
│ 1  │ Allen│ NULL │   ← B 无匹配 → city 为 NULL
35
│ 2  │ Bob  │ NYC  │
36
│ 3  │ Zack │ Boston│
37
└────┴──────┴──────┘
38

39
③ 自连接（SELF JOIN）——同一张表当两张用，用别名区分角色
40
   Employee 里：员工.managerId = 经理.id（详见 0181 / 1731 / 0570）

1
SELECT p.firstName, p.lastName, a.city, a.state
2
FROM Person p
3
         LEFT JOIN Address a ON p.personId = a.personId;

1
SELECT e1.name AS Employee
2
FROM Employee e1
3
         JOIN Employee e2 ON e1.managerId = e2.id
4
WHERE e1.salary > e2.salary;

1
SELECT name, bonus
2
FROM Employee e
3
         LEFT JOIN Bonus b ON e.empId = b.empId
4
WHERE b.bonus < 1000
5
   OR b.bonus IS NULL;

1
SELECT euni.unique_id, e.name
2
FROM Employees e
3
         LEFT JOIN EmployeeUNI euni ON e.id = euni.id;

1
SELECT p.product_name, s.year, s.price
2
FROM Sales s
3
         JOIN Product p ON s.product_id = p.product_id;

1
SELECT v.customer_id, COUNT(v.customer_id) AS count_no_trans
2
FROM Visits v
3
         LEFT JOIN Transactions t ON v.visit_id = t.visit_id
4
WHERE t.transaction_id IS NULL
5
GROUP BY v.customer_id;

1
SELECT s.student_id,
2
       s.student_name,
3
       su.subject_name,
4
       COUNT(e.subject_name) AS attended_exams
5
FROM Students s
6
         JOIN Subjects su -- 先做笛卡尔积
7
         LEFT JOIN Examinations e -- 再左连考试表
8
                   ON e.student_id = s.student_id AND e.subject_name = su.subject_name
9
GROUP BY s.student_id, su.subject_name
10
ORDER BY s.student_id, su.subject_name;

1
Students × Subjects（笛卡尔积）：
2
┌─────────────┬──────────┐
3
│ student_id  │ subject  │
4
├─────────────┼──────────┤
5
│ 1, Alice    │ Math     │  ← LEFT JOIN Exam: 找到1条 → count=1
6
│ 1, Alice    │ Physics  │  ← LEFT JOIN Exam: 找到0条 → count=0
7
│ 1, Alice    │ Math     │  ...
8
│ 2, Bob      │ Math     │
9
│ 2, Bob      │ Physics  │
10
└─────────────┴──────────┘
11

12
→ LEFT JOIN Examinations（保留所有学生×科目组合）
13
→ GROUP BY (student_id, subject_name)
14
→ COUNT(e.subject_name) 只计非 NULL

1
SELECT e2.employee_id,
2
       e2.name,
3
       COUNT(e1.reports_to)  AS reports_count,
4
       ROUND(AVG(e1.age), 0) AS average_age
5
FROM Employees e1
6
         JOIN Employees e2 ON e1.reports_to = e2.employee_id
7
GROUP BY e1.reports_to
8
ORDER BY e2.employee_id;

1
SELECT e1.employee_id
2
FROM Employees e1
3
         LEFT JOIN Employees e2 ON e1.manager_id = e2.employee_id
4
WHERE e1.salary < 30000
5
  AND e2.employee_id IS NULL
6
  AND e1.manager_id IS NOT NULL
7
ORDER BY e1.employee_id;

1
WHERE：在分组前过滤行          HAVING：在分组后过滤组
2
┌─────────────┐              ┌─────────────┐
3
│  原始数据     │              │ 分组后的结果  │
4
│  WHERE 过滤  │ → GROUP BY → │ HAVING 过滤  │ → 最终结果
5
│  （行级）     │              │ （组级）      │
6
└─────────────┘              └─────────────┘
7
不能用聚合函数                  可以用聚合函数

1
SELECT player_id, MIN(event_date) AS first_login
2
FROM Activity
3
GROUP BY player_id;

1
SELECT email AS Email
2
FROM Person
3
GROUP BY email
4
HAVING COUNT(email) > 1;

1
SELECT class
2
FROM Courses
3
GROUP BY class
4
HAVING COUNT(DISTINCT student) >= 5;

1
SELECT e1.name
2
FROM Employee e1
3
         JOIN Employee e2 ON e1.Id = e2.managerId
4
GROUP BY e1.Id
5
HAVING COUNT(e2.Id) >= 5;

1
Employee 表（自连接）
2
e1=经理, e2=下属
3
┌─────────────┬──────────────────┐
4
│ e1.name(经理) │ e2.Id(下属)       │
5
├─────────────┼──────────────────┤
6
│ Stephen     │ 1, 2, 3, 4, 5    │  → COUNT=5 ✅
7
│ Alice       │ 6, 7             │  → COUNT=2 ❌
8
└─────────────┴──────────────────┘
9
→ GROUP BY e1.Id → HAVING COUNT >= 5 → Stephen

1
SELECT customer_id
2
FROM Customer
3
GROUP BY customer_id
4
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product);

1
Product 表共 3 个产品（product_key: 1, 2, 3）
2
→ 子查询: SELECT COUNT(*) FROM Product = 3
3

4
Customer 表:
5
┌─────────────┬────────────┐
6
│ customer_id │ product_key│
7
├─────────────┼────────────┤
8
│ 1           │ 1          │
9
│ 1           │ 2          │
10
│ 1           │ 3          │   → COUNT(DISTINCT)=3 = 3 ✅
11
│ 2           │ 1          │
12
│ 2           │ 2          │   → COUNT(DISTINCT)=2 ≠ 3 ❌
13
│ 3           │ 1          │
14
│ 3           │ 2          │
15
│ 3           │ 3          │
16
│ 3           │ 1(重复)    │   → COUNT(DISTINCT)=3 = 3 ✅
17
└─────────────┴────────────┘
18

19
最终结果: customer_id = 1, 3

1
SELECT p.product_id, p.product_name
2
FROM Product p
3
         JOIN Sales s ON p.product_id = s.product_id
4
GROUP BY s.product_id
5
HAVING MIN(s.sale_date) >= '2019-01-01'
6
   AND MAX(s.sale_date) <= '2019-03-31';

1
SELECT u.name, SUM(t.amount) AS balance
2
FROM Users u
3
         JOIN Transactions t ON u.account = t.account
4
GROUP BY t.account
5
HAVING SUM(t.amount) > 10000;

1
SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month, country,
2
       COUNT(state) AS trans_count,
3
       SUM(IF(state = 'approved', 1, 0)) AS approved_count,
4
       SUM(amount) AS trans_total_amount,
5
       SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
6
FROM Transactions
7
GROUP BY month, country;

1
SELECT stock_name,
2
       SUM(CASE
3
               WHEN operation = 'Buy' THEN -price
4
               WHEN operation = 'Sell' THEN price END) AS capital_gain_loss
5
FROM Stocks
6
GROUP BY stock_name;

1
SELECT sell_date,
2
       COUNT(DISTINCT product)                         AS num_sold,
3
       GROUP_CONCAT(DISTINCT product ORDER BY product) AS products
4
FROM Activities
5
GROUP BY sell_date;

1
SELECT date_id,
2
       make_name,
3
       COUNT(DISTINCT lead_id)    AS unique_leads,
4
       COUNT(DISTINCT partner_id) AS unique_partners
5
FROM DailySales
6
GROUP BY date_id, make_name;

1
SELECT MAX(num) AS num
2
FROM (SELECT num
3
      FROM MyNumbers
4
      GROUP BY num
5
      HAVING COUNT(num) = 1) AS numbers;

1
原始 MyNumbers:          → GROUP BY num + HAVING COUNT=1:    → MAX():
2
┌──────┐                ┌──────┐                           ┌──────┐
3
│ num  │                │ num  │                           │ num  │
4
├──────┤                ├──────┤                           ├──────┤
5
│ 8    │                │ 8    │ ← 出现1次 ✅               │      │
6
│ 8    │                │ 6    │ ← 出现1次 ✅               │  6   │
7
│ 3    │                │      │  3 出现2次 ❌              │      │
8
│ 3    │                │      │  1 出现3次 ❌              │      │
9
│ 7    │                │ 7    │ ← 出现1次 ✅               │      │
10
│ 6    │                └──────┘                           └──────┘
11
│ 6    │
12
│ 1    │
13
│ 1    │
14
│ 1    │
15
└──────┘

1
SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
2
FROM Insurance
3
WHERE (lat, lon) IN
4
      (SELECT lat, lon FROM Insurance GROUP BY lat, lon HAVING COUNT(pid) = 1)
5
  AND tiv_2015 IN
6
      (SELECT tiv_2015 FROM Insurance GROUP BY tiv_2015 HAVING COUNT(pid) > 1);

1
原始 Insurance 表:
2
┌─────┬──────────┬──────────┬──────┬──────┐
3
│ pid │ tiv_2015 │ tiv_2016 │ lat  │ lon  │
4
├─────┼──────────┼──────────┼──────┼──────┤
5
│ 1   │ 10       │ 5        │ 10   │ 10   │
6
│ 2   │ 20       │ 20       │ 20   │ 20   │
7
│ 3   │ 10       │ 30       │ 20   │ 20   │  ← lat,lon 与 pid=2 相同！
8
│ 4   │ 10       │ 40       │ 40   │ 40   │
9
└─────┴──────────┴──────────┴──────┴──────┘
10

11
子查询1: 城市坐标唯一的 → (10,10) 和 (40,40) → pid=1, 4
12
子查询2: 2015投保额重复的 → tiv_2015=10 (出现3次) → pid=1,3,4
13

14
两个条件取交集: pid=1 和 pid=4
15
→ SUM(tiv_2016) = 5 + 40 = 45

1
SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary
2
FROM Employee e
3
         JOIN Department d ON e.departmentId = d.id
4
WHERE (SELECT COUNT(DISTINCT e2.salary)
5
       FROM Employee e2
6
       WHERE e.salary < e2.salary
7
         AND e.departmentId = e2.departmentId) < 3;

1
SELECT employee_id, department_id
2
FROM Employee
3
WHERE primary_flag = 'Y'
4
   OR employee_id IN (SELECT employee_id
5
                      FROM Employee
6
                      GROUP BY employee_id
7
                      HAVING COUNT(employee_id) = 1);

1
Employee 表:
2
┌─────────────┬──────────────┬──────────────┐
3
│ employee_id │ department_id│ primary_flag │
4
├─────────────┼──────────────┼──────────────┤
5
│ 1           │ 1            │ N            │  ← 只有1个部门 → 子查询命中 ✅
6
│ 2           │ 1            │ Y            │  ← primary_flag=Y ✅
7
│ 2           │ 2            │ N            │  ← 不满足任何条件 ❌
8
│ 3           │ 2            │ N            │  ← 只有1个部门 → 子查询命中 ✅
9
│ 4           │ 1            │ N            │  ← 有2个部门，primary都不是Y → 看：
10
│ 4           │ 2            │ N            │    子查询 COUNT=2≠1 ❌, primary≠Y ❌
11
└─────────────┴──────────────┴──────────────┘
12

13
最终结果: (1,1), (2,1), (3,2)

1
普通 GROUP BY：每组只输出一行         窗口函数：每行都输出，但聚合范围是「窗口」
2
┌────────────┐                       ┌────────────────────────┐
3
│ dept │ AVG │                       │ emp │ dept │ salary │ AVG(dept) │
4
├──────┼─────┤                       ├─────┼──────┼────────┼──────────┤
5
│ A    │ 75  │                       │ 1   │ A    │ 80     │ 75       │ ← 同 dept 的平均
6
│ B    │ 60  │                       │ 2   │ A    │ 70     │ 75       │
7
└────────────┘                       │ 3   │ B    │ 60     │ 60       │
8
                                     └─────┴──────┴────────┴──────────┘

1
SELECT person_name
2
FROM (SELECT person_name,
3
             turn,
4
             SUM(weight) OVER(ORDER BY turn) AS sum_weight
5
      FROM Queue) AS wei
6
WHERE sum_weight <= 1000
7
ORDER BY turn DESC LIMIT 1;

1
SELECT visited_on, amount, average_amount
2
FROM (SELECT visited_on,
3
             SUM(amount)  OVER(ORDER BY visited_on
4
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount, ROUND(AVG(amount) OVER(ORDER BY visited_on
5
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS average_amount,
6
             ROW_NUMBER() OVER(ORDER BY visited_on) AS rn
7
      FROM (SELECT visited_on, SUM(amount) AS amount
8
            FROM Customer
9
            GROUP BY visited_on) AS daily) AS ranked
10
WHERE rn > 6;

1
第1步：先按天聚合
2
  visited_on  | amount(当天总额)
3
  2019-01-01  | 130
4
  2019-01-02  | 110
5
  2019-01-03  | 140
6
  2019-01-04  | 100
7
  2019-01-05  | 110
8
  2019-01-06  | 130
9
  2019-01-07  | 150
10
  2019-01-08  | 120
11
  2019-01-09  | 200
12

13
第2步：7天滚动窗口（ROWS BETWEEN 6 PRECEDING AND CURRENT ROW）
14

15
  visited_on  | 窗口范围              | SUM   | AVG     | rn | >6?
16
  2019-01-01  | [01-01]               | 130   | 130.00  | 1  | ❌
17
  2019-01-02  | [01-01 ~ 01-02]       | 240   | 120.00  | 2  | ❌
18
  ...         | 窗口不足7天           | ...   | ...     | .. | ❌
19
  2019-01-07  | [01-01 ~ 01-07]       | 870   | 124.29  | 7  | ❌
20
  2019-01-08  | [01-02 ~ 01-08]       | 860   | 122.86  | 8  | ✅ → 输出
21
  2019-01-09  | [01-03 ~ 01-09]       | 950   | 135.71  | 9  | ✅ → 输出

1
WITH uion AS (SELECT u.user_id, u.name, m.title, mr.rating, mr.created_at
2
              FROM MovieRating mr
3
                       JOIN Users u ON mr.user_id = u.user_id
4
                       JOIN Movies m ON mr.movie_id = m.movie_id)
5
SELECT name AS results
6
FROM (SELECT name, RANK() OVER(ORDER BY COUNT(title) DESC, name) AS rk
7
      FROM uion
8
      GROUP BY user_id) AS max_user
9
WHERE rk = 1
10
UNION ALL
11
SELECT title AS results
12
FROM (SELECT title, RANK() OVER(ORDER BY AVG(rating) DESC, title) AS rk
13
      FROM uion
14
      WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29'
15
      GROUP BY title) AS max_title
16
WHERE rk = 1;

1
第1步：CTE uion — 三表 JOIN
2
┌────────┬────────┬─────────────┬───────┬────────────┐
3
│ user_id│ name   │ title       │ rating│ created_at │
4
├────────┼────────┼─────────────┼───────┼────────────┤
5
│ 3      │ Daniel │ Ice         │ 5     │ 2020-02-14  │
6
│ 2      │ Daniel │ Detention   │ 3     │ 2020-02-12  │
7
│ ...    │ ...    │ ...         │ ...   │ ...         │
8
└────────┴────────┴─────────────┴───────┴────────────┘
9

10
第2步：子查询1 — 用户评论数排名
11
GROUP BY user_id → COUNT(title) → RANK() OVER(ORDER BY count DESC, name)
12
┌────────┬───────┬─────┐
13
│ name   │ count │ rk  │
14
├────────┼───────┼─────┤
15
│ Daniel │ 3     │ 1   │ ← WHERE rk=1 → 输出 "Daniel"
16
│ Gloria │ 1     │ 2   │
17
└────────┴───────┴─────┘
18

19
第3步：子查询2 — 2月电影平均评分排名
20
WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29'
21
GROUP BY title → AVG(rating) → RANK() OVER(ORDER BY avg DESC, title)
22
┌─────────────┬───────┬─────┐
23
│ title       │ avg   │ rk  │
24
├─────────────┼───────┼─────┤
25
│ Ice         │ 4.5   │ 1   │ ← WHERE rk=1 → 输出 "Ice"
26
│ Detention   │ 3.0   │ 2   │
27
└─────────────┴───────┴─────┘
28

29
第4步：UNION ALL 合并
30
┌──────────┐
31
│ results  │
32
├──────────┤
33
│ Daniel   │
34
│ Ice      │
35
└──────────┘

1
SELECT x,
2
       y,
3
       z,
4
       CASE
5
           WHEN x + y > z AND x + z > y AND y + z > x THEN 'Yes'
6
           ELSE 'No' END AS triangle
7
FROM Triangle;

1
SELECT CASE
2
           WHEN id % 2 = 0 THEN id - 1
3
           WHEN id % 2 = 1 AND id != (SELECT COUNT(*) FROM Seat) THEN id + 1
4
       ELSE id
5
END
6
AS id, student
7
FROM Seat ORDER BY id;

1
UPDATE Salary
2
SET sex = CASE WHEN sex = 'm' THEN 'f' WHEN sex = 'f' THEN 'm' END;

1
SELECT 'Low Salary' AS category, SUM(IF(income < 20000, 1, 0)) AS accounts_count
2
FROM Accounts
3
UNION
4
SELECT 'Average Salary', SUM(IF(income >= 20000 AND income <= 50000, 1, 0))
5
FROM Accounts
6
UNION
7
SELECT 'High Salary', SUM(IF(income > 50000, 1, 0))
8
FROM Accounts;

1
Accounts 表: income = [8000, 25000, 30000, 60000, 15000]
2

3
第1个 SELECT: 'Low Salary', SUM(IF(income<20000,1,0))
4
  → 8000→1, 25000→0, 30000→0, 60000→0, 15000→1 → SUM=2
5

6
第2个 SELECT: 'Average Salary', SUM(IF(20000≤income≤50000,1,0))
7
  → 8000→0, 25000→1, 30000→1, 60000→0, 15000→0 → SUM=2
8

9
第3个 SELECT: 'High Salary', SUM(IF(income>50000,1,0))
10
  → 8000→0, 25000→0, 30000→0, 60000→1, 15000→0 → SUM=1
11

12
UNION 合并:
13
┌──────────────────┬───────────────┐
14
│ category         │ accounts_count│
15
├──────────────────┼───────────────┤
16
│ Low Salary       │ 2             │
17
│ Average Salary   │ 2             │
18
│ High Salary      │ 1             │
19
└──────────────────┴───────────────┘

1
UNION：去重合并            UNION ALL：不去重合并
2
┌──────┐  ┌──────┐       ┌──────┐  ┌──────┐
3
│ A    │  │ A    │       │ A    │  │ A    │
4
│ B    │  │ C    │       │ B    │  │ C    │
5
└──────┘  └──────┘       └──────┘  └──────┘
6
    ↓                         ↓
7
┌──────┐                  ┌──────┐
8
│ A    │ ← 去重           │ A    │
9
│ B    │                  │ A    │ ← 保留
10
│ C    │                  │ B    │
11
└──────┘                  │ C    │
12
                          └──────┘

1
SELECT product_id, 'store1' AS store, store1 AS price
2
FROM Products
3
WHERE store1 IS NOT NULL
4
UNION ALL
5
SELECT product_id, 'store2' AS store, store2 AS price
6
FROM Products
7
WHERE store2 IS NOT NULL
8
UNION ALL
9
SELECT product_id, 'store3' AS store, store3 AS price
10
FROM Products
11
WHERE store3 IS NOT NULL;

1
原始 Products 表（宽表）:
2
┌────────────┬────────┬────────┬────────┐
3
│ product_id │ store1 │ store2 │ store3 │
4
├────────────┼────────┼────────┼────────┤
5
│ 0          │ 95     │ 100    │ 105    │
6
│ 1          │ 70     │ NULL   │ 80     │
7
└────────────┴────────┴────────┴────────┘
8

9
UNION ALL 后（长表）:
10
┌────────────┬────────┬───────┐
11
│ product_id │ store  │ price │
12
├────────────┼────────┼───────┤
13
│ 0          │ store1 │ 95    │
14
│ 0          │ store2 │ 100   │
15
│ 0          │ store3 │ 105   │
16
│ 1          │ store1 │ 70    │
17
│ 1          │ store3 │ 80    │  ← store2=NULL 被过滤
18
└────────────┴────────┴───────┘

1
-- 有变更记录的产品：取 ≤ 2019-08-16 的最近一次价格
2
SELECT product_id, new_price AS price
3
FROM Products
4
WHERE (product_id, change_date) IN
5
      (SELECT product_id, MAX(change_date)
6
       FROM Products
7
       WHERE change_date <= '2019-08-16'
8
       GROUP BY product_id)
9
UNION
10
-- 无变更记录的产品：价格为默认 10
11
SELECT product_id, 10 AS price
12
FROM Products
13
WHERE (product_id, change_date) IN
14
      (SELECT product_id, MIN(change_date)
15
       FROM Products
16
       GROUP BY product_id
17
       HAVING MIN(change_date) > '2019-08-16')
18
ORDER BY product_id;

1
Products 表:
2
┌────────────┬───────────┬─────────────┐
3
│ product_id │ new_price │ change_date │
4
├────────────┼───────────┼─────────────┤
5
│ 1          │ 20        │ 2019-08-14  │  ← ≤ 08-16, 最近
6
│ 2          │ 50        │ 2019-08-01  │  ← ≤ 08-16, 最近
7
│ 1          │ 10        │ 2019-08-17  │  ← > 08-16
8
│ 3          │ 30        │ 2019-08-19  │  ← 首次变更 > 08-16 → 默认 10
9
└────────────┴───────────┴─────────────┘
10

11
子查询1: ≤ 08-16 的最近变更 → (1, 2019-08-14), (2, 2019-08-01)
12
  → product 1 → price=20, product 2 → price=50
13

14
子查询2: 首次变更 > 08-16 → (3, 2019-08-19)
15
  → product 3 → price=10 (默认)
16

17
UNION 合并:
18
┌────────────┬───────┐
19
│ product_id │ price │
20
├────────────┼───────┤
21
│ 1          │ 20    │
22
│ 2          │ 50    │
23
│ 3          │ 10    │
24
└────────────┴───────┘

1
SELECT ROUND(COUNT(m.player_id) / COUNT(DISTINCT a.player_id), 2) AS fraction
2
FROM Activity a
3
         LEFT JOIN (SELECT player_id, MIN(event_date) AS event_date
4
                    FROM Activity
5
                    GROUP BY player_id) m
6
                   ON a.player_id = m.player_id AND DATEDIFF(a.event_date, m.event_date) = 1;

1
第1步：子查询找首次登录日期
2
  player_id=1 → 2016-03-01
3
  player_id=2 → 2017-06-25
4
  player_id=3 → 2016-03-02
5

6
第2步：LEFT JOIN 条件: 同玩家 + DATEDIFF=1（次日登录）
7
  Activity(1, 2016-03-02) JOIN m(1, 2016-03-01) → DATEDIFF=1 ✅ → m.player_id=1
8
  Activity(1, 2016-03-01) JOIN m(1, 2016-03-01) → DATEDIFF=0 ❌
9
  Activity(2, 2017-06-25) JOIN m(2, 2017-06-25) → DATEDIFF=0 ❌
10
  Activity(3, 2018-07-03) JOIN m(3, 2016-03-02) → DATEDIFF=488 ❌
11

12
第3步：计算比率
13
  COUNT(m.player_id) = 1（只有 player 1 次日登录）
14
  COUNT(DISTINCT a.player_id) = 3（总玩家数）
15
  fraction = 1/3 = 0.33

1
SELECT w1.id
2
FROM Weather w1
3
         JOIN Weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
4
WHERE w1.temperature > w2.temperature;

1
SELECT DISTINCT l1.Num AS ConsecutiveNums
2
FROM Logs l1,
3
     Logs l2,
4
     Logs l3
5
WHERE l1.Id = l2.Id - 1
6
  AND l2.Id = l3.Id - 1
7
  AND l1.Num = l2.Num
8
  AND l2.Num = l3.Num;

1
Logs 表:
2
┌────┬─────┐
3
│ id │ num │
4
├────┼─────┤
5
│ 1  │ 1   │
6
│ 2  │ 1   │
7
│ 3  │ 1   │  ← 1 连续3次 ✅
8
│ 4  │ 2   │
9
│ 5  │ 1   │
10
│ 6  │ 2   │
11
│ 7  │ 2   │  ← 2 只连续2次 ❌
12
└────┴─────┘
13

14
三表自连接条件: l1.id=l2.id-1 AND l2.id=l3.id-1
15
→ 匹配: (id=1,num=1), (id=2,num=1), (id=3,num=1)
16
→ l1.Num=l2.Num=l3.Num=1 ✅
17
→ DISTINCT 后结果: ConsecutiveNums = 1

1
SELECT *
2
FROM Users
3
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*@leetcode\.com$' COLLATE utf8mb4_bin;

1
^[a-zA-Z]                    → 以字母开头
2
[a-zA-Z0-9_.-]*              → 后跟任意数量的字母/数字/下划线/点/破折号
3
@leetcode\.com$              → 以 @leetcode.com 结尾
4
COLLATE utf8mb4_bin          → 区分大小写（确保域名为小写）

1
SELECT *
2
FROM Patients
3
WHERE conditions LIKE '% DIAB1%'
4
   OR conditions LIKE 'DIAB1%';

1
SELECT user_id,
2
       CONCAT(UPPER(SUBSTRING(name, 1, 1)), LOWER(SUBSTRING(name, 2))) AS name
3
FROM Users
4
ORDER BY user_id;

1
WITH tab AS (SELECT pp.user_id, pi.category
2
             FROM ProductPurchases pp
3
                      JOIN ProductInfo pi ON pp.product_id = pi.product_id)
4
SELECT tab.category                AS category1,
5
       tab2.category               AS category2,
6
       COUNT(DISTINCT tab.user_id) AS customer_count
7
FROM tab
8
         JOIN tab AS tab2
9
              ON tab.user_id = tab2.user_id AND tab.category < tab2.category
10
GROUP BY tab.category, tab2.category
11
HAVING customer_count >= 3
12
ORDER BY customer_count DESC, category1, category2;

1
第1步：CTE tab — 关联购买记录和产品类别
2
┌─────────┬──────────┐
3
│ user_id │ category │
4
├─────────┼──────────┤
5
│ 1       │ A        │
6
│ 1       │ B        │
7
│ 2       │ A        │
8
│ 2       │ B        │
9
│ 3       │ A        │
10
│ 3       │ B        │
11
│ 4       │ A        │  ← 只买了 A，没有 B
12
└─────────┴──────────┘
13

14
第2步：自连接生成类别对（条件 category1 < category2 避免重复）
15
tab JOIN tab ON user_id 相同 AND tab.category < tab2.category
16
┌─────────┬───────────┬───────────┐
17
│ user_id │ category1 │ category2 │
18
├─────────┼───────────┼───────────┤
19
│ 1       │ A         │ B         │
20
│ 2       │ A         │ B         │
21
│ 3       │ A         │ B         │
22
│ 4       │ (无 B)    │           │ ← 不匹配
23
└─────────┴───────────┴───────────┘
24

25
第3步：GROUP BY (category1, category2) + HAVING ≥ 3
26
┌───────────┬───────────┬────────────────┬──────┐
27
│ category1 │ category2 │ COUNT(DISTINCT)│ ≥ 3? │
28
├───────────┼───────────┼────────────────┼──────┤
29
│ A         │ B         │ 3              │ ✅   │
30
└───────────┴───────────┴────────────────┴──────┘

1
SELECT s.user_id,
2
       ROUND(IFNULL(SUM(IF(c.action = 'confirmed', 1, 0)) / COUNT(c.action), 0), 2)
3
           AS confirmation_rate
4
FROM Signups s
5
         LEFT JOIN Confirmations c ON s.user_id = c.user_id
6
GROUP BY s.user_id;

1
SELECT sample_id,
2
       dna_sequence,
3
       species,
4
       CASE WHEN dna_sequence LIKE 'ATG%' THEN 1 ELSE 0 END             AS has_start,
5
       CASE WHEN dna_sequence REGEXP 'TAA$|TAG$|TGA$' THEN 1 ELSE 0 END AS has_stop,
6
       CASE WHEN dna_sequence LIKE '%ATAT%' THEN 1 ELSE 0 END           AS has_atat,
7
       CASE WHEN dna_sequence LIKE '%GGG%' THEN 1 ELSE 0 END            AS has_ggg
8
FROM Samples;

1
SELECT tweet_id
2
FROM Tweets
3
WHERE LENGTH(content) > 15;

1
DELETE
2
p1 FROM Person p1
3
JOIN Person p2 ON p1.email = p2.email AND p1.id > p2.id;

1
删除前 Person 表:
2
┌────┬───────────┐
3
│ id │ email     │
4
├────┼───────────┤
5
│ 1  │ john@mail │  ← 保留（id 最小）
6
│ 2  │ bob@mail  │
7
│ 3  │ john@mail │  ← 删除（同 email，id 更大）
8
└────┴───────────┘
9

10
自连接条件: p1.email = p2.email AND p1.id > p2.id
11
→ p1(3, john@mail) JOIN p2(1, john@mail): email相同 AND 3>1 ✅ → DELETE p1
12
→ p1(1, john@mail) JOIN p2(3, john@mail): 1>3 ❌ → 不删
13
→ p1(2, bob@mail): 无同 email 的更小 id → 不删
14

15
删除后:
16
┌────┬───────────┐
17
│ id │ email     │
18
├────┼───────────┤
19
│ 1  │ john@mail │
20
│ 2  │ bob@mail  │
21
└────┴───────────┘