MySQL高级查询 #

-- 子查询示例
SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users);

-- 返回单个值的子查询
SELECT 
    name,
    age,
    (SELECT AVG(age) FROM users) AS avg_age,
    age - (SELECT AVG(age) FROM users) AS age_diff
FROM users;

-- 在WHERE中使用
SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users);

-- 在HAVING中使用
SELECT status, AVG(age) AS avg_age
FROM users
GROUP BY status
HAVING AVG(age) > (SELECT AVG(age) FROM users);

-- 返回一列值的子查询
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');

-- 使用NOT IN
SELECT * FROM users 
WHERE id NOT IN (SELECT user_id FROM orders);

-- 返回一行多列
SELECT * FROM users 
WHERE (name, age) = (SELECT name, age FROM users WHERE id = 1);

-- 返回多行多列，用作临时表
SELECT u.name, o.total
FROM (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) AS o
JOIN users u ON o.user_id = u.id;

-- EXISTS：存在则返回true
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id
);

-- NOT EXISTS：不存在则返回true
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id
);

-- 相关子查询：子查询引用外部查询的列
SELECT 
    name,
    age,
    (SELECT COUNT(*) FROM users u2 WHERE u2.age > u1.age) AS rank
FROM users u1
ORDER BY rank;

-- 查找每个部门薪资最高的员工
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary = (
    SELECT MAX(salary) 
    FROM employees e2 
    WHERE e2.department_id = e.department_id
);

-- UNION：合并结果集，自动去重
SELECT name FROM users WHERE status = 1
UNION
SELECT name FROM admins WHERE status = 1;

-- 合并不同表的数据
SELECT id, name, 'user' AS type FROM users
UNION
SELECT id, name, 'admin' AS type FROM admins;

-- UNION ALL：合并结果集，保留重复
SELECT name FROM users WHERE status = 1
UNION ALL
SELECT name FROM admins WHERE status = 1;

-- INTERSECT：交集（MySQL 8.0.31+）
SELECT name FROM users
INTERSECT
SELECT name FROM admins;

-- EXCEPT：差集（MySQL 8.0.31+）
SELECT name FROM users
EXCEPT
SELECT name FROM admins;

-- WITH子句定义CTE
WITH user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT u.name, uo.order_count
FROM users u
JOIN user_orders uo ON u.id = uo.user_id;

WITH 
active_users AS (
    SELECT * FROM users WHERE status = 1
),
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT au.name, uo.order_count
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id;

-- 递归查询组织架构
WITH RECURSIVE org_tree AS (
    -- 基础查询：顶级员工
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询：下属员工
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level;

-- 递归查询分类树
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, CAST(name AS CHAR(1000)) AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, CONCAT(ct.path, ' > ', c.name)
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

function_name([arguments]) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC|DESC]]
    [frame_clause]
)

-- 行号
SELECT 
    name,
    age,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num
FROM users;

-- 分组行号
SELECT 
    name,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;

-- RANK：相同值排名相同，跳过后续排名
SELECT 
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

-- 示例结果：
-- name  score  rank  dense_rank
-- ----  -----  ----  ----------
-- Alice    100     1           1
-- Bob      100     1           1
-- Carol     90     3           2
-- Dave      80     4           3

-- 累计求和
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- 移动平均
SELECT 
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM orders;

-- 分组聚合
SELECT 
    name,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees;

-- LAG：获取前N行的值
-- LEAD：获取后N行的值
SELECT 
    order_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY order_date) AS prev_amount,
    LEAD(amount, 1) OVER (ORDER BY order_date) AS next_amount,
    amount - LAG(amount, 1) OVER (ORDER BY order_date) AS diff
FROM orders;

SELECT 
    name,
    department_id,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS max_salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS min_salary
FROM employees;

-- 将数据分成N组
SELECT 
    name,
    score,
    NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM students;

SELECT 
    name,
    status,
    CASE status
        WHEN 1 THEN 'Active'
        WHEN 0 THEN 'Inactive'
        ELSE 'Unknown'
    END AS status_text
FROM users;

SELECT 
    name,
    score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM students;

-- 条件计数
SELECT 
    COUNT(*) AS total,
    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS active_count,
    SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS inactive_count
FROM users;

-- 条件求和
SELECT 
    SUM(CASE WHEN category = 'A' THEN amount ELSE 0 END) AS total_a,
    SUM(CASE WHEN category = 'B' THEN amount ELSE 0 END) AS total_b
FROM orders;

-- 每个部门薪资前3名
WITH ranked AS (
    SELECT 
        name,
        department_id,
        salary,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT * FROM ranked WHERE rank <= 3;

-- 月度销售额及环比
WITH monthly_sales AS (
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(amount) AS total
    FROM orders
    GROUP BY month
)
SELECT 
    month,
    total,
    LAG(total, 1) OVER (ORDER BY month) AS prev_month,
    (total - LAG(total, 1) OVER (ORDER BY month)) / LAG(total, 1) OVER (ORDER BY month) * 100 AS growth_rate
FROM monthly_sales;

-- 查找连续登录3天以上的用户
WITH login_dates AS (
    SELECT DISTINCT
        user_id,
        login_date,
        login_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY AS group_date
    FROM user_logins
),
consecutive_groups AS (
    SELECT
        user_id,
        group_date,
        COUNT(*) AS consecutive_days
    FROM login_dates
    GROUP BY user_id, group_date
)
SELECT user_id, MAX(consecutive_days) AS max_consecutive_days
FROM consecutive_groups
GROUP BY user_id
HAVING max_consecutive_days >= 3;