1
-- ROW_NUMBER：连续排名，无并列（1,2,3,4,...）
2
SELECT
3
    emp_name, department, salary,
4
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
5
FROM employees;
6

7
-- RANK：有并列，跳号（1,1,3,4,...）
8
SELECT
9
    emp_name, department, salary,
10
    RANK() OVER (ORDER BY salary DESC) AS rk
11
FROM employees;
12

13
-- DENSE_RANK：有并列，不跳号（1,1,2,3,...）
14
SELECT
15
    emp_name, department, salary,
16
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
17
FROM employees;
18

19
-- 分组排名：每个部门内按薪资排名
20
SELECT
21
    emp_name, department, salary,
22
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
23
FROM employees;

1
-- LAG：向上偏移（取前一行）
2
SELECT
3
    order_date,
4
    amount,
5
    LAG(amount, 1)   OVER (ORDER BY order_date) AS prev_amount,   -- 前一天
6
    LAG(amount, 2, 0) OVER (ORDER BY order_date) AS two_days_ago   -- 前两天，默认 0
7
FROM orders;
8

9
-- LEAD：向下偏移（取后一行）
10
SELECT
11
    order_date,
12
    amount,
13
    LEAD(amount, 1) OVER (ORDER BY order_date) AS next_amount
14
FROM orders;

1
-- 累计求和 (Running Total)
2
SELECT
3
    order_date,
4
    amount,
5
    SUM(amount) OVER (ORDER BY order_date) AS running_total
6
FROM orders;
7

8
-- 分组累计求和
9
SELECT
10
    order_date,
11
    department,
12
    amount,
13
    SUM(amount) OVER (PARTITION BY department ORDER BY order_date) AS dept_running_total
14
FROM orders;
15

16
-- 移动平均（前 2 行 + 当前行 + 后 2 行，共 5 行）
17
SELECT
18
    order_date,
19
    amount,
20
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS moving_avg_5
21
FROM orders;
22

23
-- 每组内的聚合值
24
SELECT
25
    emp_name, department, salary,
26
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
27
    MAX(salary) OVER (PARTITION BY department) AS dept_max,
28
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
29
FROM employees;

1
-- 累积帧（从分区起点到当前行，默认行为）
2
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
3

4
-- 滑动窗口（前3行 + 当前行）
5
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
6

7
-- 前后各1行
8
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
9

10
-- 到分区末尾
11
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

1
-- 拼接
2
SELECT CONCAT(last_name, ' ', first_name) AS full_name FROM employees;
3
SELECT CONCAT_WS('-', '2024', '01', '15');              -- '2024-01-15'
4

5
-- 子串
6
SELECT SUBSTRING('Hello World', 1, 5);                   -- 'Hello'
7
SELECT LEFT('Hello World', 5);                            -- 'Hello'
8
SELECT RIGHT('Hello World', 5);                           -- 'World'
9

10
-- 替换
11
SELECT REPLACE('Hello World', 'World', 'MySQL');          -- 'Hello MySQL'
12

13
-- 长度
14
SELECT LENGTH('Hello');                                    -- 5（字节）
15
SELECT CHAR_LENGTH('你好');                                -- 2（字符数）
16

17
-- 大小写与去空格
18
SELECT UPPER('hello'), LOWER('HELLO'), TRIM('  text  ');

1
-- 当前时间
2
SELECT NOW(), CURDATE(), CURTIME();
3

4
-- 提取日期部分
5
SELECT
6
    YEAR('2024-06-15')        AS yr,
7
    MONTH('2024-06-15')       AS mon,
8
    DAY('2024-06-15')         AS day,
9
    DAYOFWEEK('2024-06-15')   AS weekday;   -- 1=Sunday
10

11
-- 日期格式化
12
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');         -- '2026-05-30 14:30:00'
13
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');                -- '2026年05月30日'
14

15
-- 日期计算
16
SELECT DATEDIFF('2024-12-31', '2024-01-01');             -- 364（日期差）
17
SELECT DATE_ADD('2024-01-01', INTERVAL 7 DAY);           -- '2024-01-08'
18
SELECT DATE_SUB('2024-01-01', INTERVAL 1 MONTH);         -- '2023-12-01'
19
SELECT TIMESTAMPDIFF(MONTH, '2023-01-01', '2024-06-01'); -- 17（月差）
20

21
-- 日期截断
22
SELECT DATE('2024-06-15 14:30:00');                      -- '2024-06-15'

1
-- 四舍五入 / 上下取整
2
SELECT ROUND(3.14159, 2);      -- 3.14
3
SELECT CEIL(3.1), FLOOR(3.9);  -- 4, 3
4
SELECT ABS(-5), MOD(10, 3);    -- 5, 1
5

6
-- 类型转换
7
SELECT CAST('123' AS SIGNED);
8
SELECT CAST('2024-01-01' AS DATE);
9
SELECT CONVERT(150, CHAR);     -- 等效 CAST(150 AS CHAR)
10

11
-- IFNULL / COALESCE：空值处理
12
SELECT IFNULL(bonus, 0) FROM employees;                       -- bonus 为 NULL 时返回 0
13
SELECT COALESCE(phone, email, '无联系方式') FROM employees;    -- 返回第一个非 NULL

1
-- CASE WHEN：条件分支
2
SELECT
3
    emp_name,
4
    salary,
5
    CASE
6
        WHEN salary < 5000  THEN '低薪'
7
        WHEN salary < 10000 THEN '中薪'
8
        ELSE '高薪'
9
    END AS salary_level
10
FROM employees;
11

12
-- CASE WHEN 配合聚合做交叉/透视
13
SELECT department,
14
    SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS 男性,
15
    SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS 女性
16
FROM employees
17
GROUP BY department;
18

19
-- IF 函数（三元表达式）
20
SELECT IF(salary > 8000, '高', '低') FROM employees;

1
-- UNION：合并结果集并去重
2
SELECT emp_name FROM employees_2023
3
UNION
4
SELECT emp_name FROM employees_2024;
5

6
-- UNION ALL：合并结果集不去重（更快）
7
SELECT emp_name FROM employees_2023
8
UNION ALL
9
SELECT emp_name FROM employees_2024;

1
-- 普通 CTE：把子查询抽出来，可读性更好
2
WITH dept_avg AS (
3
    SELECT dept_id, AVG(salary) AS avg_sal
4
    FROM employees
5
    GROUP BY dept_id
6
)
7
SELECT e.emp_name, e.salary, d.avg_sal
8
FROM employees e
9
JOIN dept_avg d ON e.dept_id = d.dept_id
10
WHERE e.salary > d.avg_sal;
11

12
-- 多个 CTE
13
WITH
14
    dept_avg AS (
15
        SELECT dept_id, AVG(salary) AS avg_sal
16
        FROM employees GROUP BY dept_id
17
    ),
18
    high_paid AS (
19
        SELECT * FROM employees WHERE salary > 10000
20
    )
21
SELECT * FROM high_paid;
22

23
-- 递归 CTE（生成连续日期、组织结构树等）
24
WITH RECURSIVE dates AS (
25
    SELECT '2024-01-01' AS dt
26
    UNION ALL
27
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM dates WHERE dt < '2024-01-31'
28
)
29
SELECT * FROM dates;

1
-- 建表
2
CREATE TABLE users (
3
    id       INT AUTO_INCREMENT PRIMARY KEY,
4
    username VARCHAR(50) NOT NULL UNIQUE,
5
    email    VARCHAR(100),
6
    age      INT DEFAULT 0,
7
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
8
);
9

10
-- 修改表结构
11
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
12
ALTER TABLE users MODIFY COLUMN age TINYINT;
13
ALTER TABLE users DROP COLUMN phone;
14
ALTER TABLE users ADD INDEX idx_email(email);
15

16
-- 插入
17
INSERT INTO users (username, email) VALUES ('zhangsan', 'zhangsan@example.com');
18
INSERT INTO users (username, email) VALUES
19
    ('lisi', 'lisi@example.com'),
20
    ('wangwu', 'wangwu@example.com');
21

22
-- 更新 / 删除（务必带 WHERE）
23
UPDATE users SET age = 25 WHERE username = 'zhangsan';
24
DELETE FROM users WHERE id = 1;

1
-- 解法1：子查询 + 窗口函数（推荐）
2
SELECT emp_name, department, salary
3
FROM (
4
    SELECT *,
5
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk
6
    FROM employees
7
) t
8
WHERE rk = 1;
9

10
-- 解法2：相关子查询
11
SELECT emp_name, department, salary
12
FROM employees e1
13
WHERE salary = (
14
    SELECT MAX(salary)
15
    FROM employees e2
16
    WHERE e2.department = e1.department
17
);

1
WITH first_login AS (
2
    -- 每个用户的首次登录日期
3
    SELECT user_id, MIN(login_date) AS first_date
4
    FROM login_log
5
    GROUP BY user_id
6
),
7
retention AS (
8
    -- 次日是否还登录了
9
    SELECT
10
        f.first_date,
11
        COUNT(DISTINCT f.user_id) AS total_users,
12
        COUNT(DISTINCT l.user_id) AS retained_users
13
    FROM first_login f
14
    LEFT JOIN login_log l
15
        ON f.user_id = l.user_id
16
        AND l.login_date = DATE_ADD(f.first_date, INTERVAL 1 DAY)
17
    GROUP BY f.first_date
18
)
19
SELECT
20
    first_date,
21
    total_users,
22
    retained_users,
23
    ROUND(retained_users / total_users * 100, 2) AS retention_pct
24
FROM retention
25
ORDER BY first_date;

1
-- 用 SQL 查询连续登录 3 天及以上的用户
2
WITH ranked AS (
3
    SELECT
4
        user_id,
5
        login_date,
6
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
7
    FROM login_log
8
    GROUP BY user_id, login_date   -- 一天多次登录去重
9
),
10
diff_group AS (
11
    SELECT
12
        user_id,
13
        login_date,
14
        DATE_SUB(login_date, INTERVAL rn DAY) AS grp   -- 连续日期的分组标志
15
    FROM ranked
16
)
17
SELECT user_id, COUNT(*) AS consecutive_days
18
FROM diff_group
19
GROUP BY user_id, grp
20
HAVING COUNT(*) >= 3;

1
-- 方案1：窗口函数（简单直接）
2
SELECT
3
    order_date,
4
    amount,
5
    SUM(amount) OVER (ORDER BY order_date) AS running_total
6
FROM orders;
7

8
-- 方案2：自连接（理解原理）
9
SELECT
10
    o1.order_date,
11
    o1.amount,
12
    SUM(o2.amount) AS running_total
13
FROM orders o1
14
JOIN orders o2 ON o2.order_date <= o1.order_date
15
GROUP BY o1.order_date, o1.amount
16
ORDER BY o1.order_date;

1
-- 窗口函数方法
2
WITH numbered AS (
3
    SELECT
4
        salary,
5
        ROW_NUMBER() OVER (ORDER BY salary) AS rn,
6
        COUNT(*) OVER () AS total
7
    FROM employees
8
)
9
SELECT AVG(salary) AS median
10
FROM numbered
11
WHERE rn IN (FLOOR((total + 1) / 2), CEIL((total + 1) / 2));