MariaDB高级查询 #

-- 子查询：嵌套在其他查询中的查询
-- 可以出现在SELECT、FROM、WHERE、HAVING子句中

SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');

-- 返回单个值的子查询
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

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

-- 返回一列值的子查询
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 使用ANY/SOME
SELECT * FROM users 
WHERE id = ANY (SELECT user_id FROM orders WHERE status = 'completed');

-- 使用ALL
SELECT * FROM users 
WHERE balance > ALL (SELECT balance FROM users WHERE status = 0);

-- 返回一行多列
SELECT * FROM users 
WHERE (id, status) = (SELECT user_id, status FROM orders WHERE id = 1);

-- 返回多行多列（派生表）
SELECT u.name, o.order_count
FROM users u
JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id;

-- 相关子查询：引用外部查询的表
SELECT 
    name,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- EXISTS子查询
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.status = 'completed'
);

-- NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- MariaDB 10.2+ 支持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
LEFT 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, SUM(amount) AS total_amount
        FROM orders
        GROUP BY user_id
    )
SELECT au.name, uo.total_amount
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id;

-- 递归CTE：处理层次结构数据
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT
);

INSERT INTO categories VALUES
    (1, 'Electronics', NULL),
    (2, 'Computers', 1),
    (3, 'Phones', 1),
    (4, 'Laptops', 2),
    (5, 'Desktops', 2);

-- 查询所有子分类
WITH RECURSIVE category_tree AS (
    -- 基础查询
    SELECT id, name, parent_id, 1 AS level, CAST(name AS CHAR(500)) AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归查询
    SELECT c.id, c.name, c.parent_id, ct.level + 1, CONCAT(ct.path, ' > ', c.name)
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY path;

+----+------------+-----------+-------+---------------------------+
| id | name       | parent_id | level | path                      |
+----+------------+-----------+-------+---------------------------+
|  1 | Electronics|      NULL |     1 | Electronics               |
|  2 | Computers  |         1 |     2 | Electronics > Computers   |
|  4 | Laptops    |         2 |     3 | Electronics > Computers > Laptops |
|  5 | Desktops   |         2 |     3 | Electronics > Computers > Desktops |
|  3 | Phones     |         1 |     2 | Electronics > Phones      |
+----+------------+-----------+-------+---------------------------+

-- 生成数字序列
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

-- 计算阶乘
WITH RECURSIVE factorial AS (
    SELECT 1 AS n, 1 AS fact
    UNION ALL
    SELECT n + 1, fact * (n + 1) FROM factorial WHERE n < 5
)
SELECT * FROM factorial;

-- MariaDB 10.2+ 支持窗口函数
-- 语法：函数名() OVER (PARTITION BY ... ORDER BY ...)

SELECT 
    name,
    age,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS rank
FROM users;

-- ROW_NUMBER：连续排名
SELECT 
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

-- RANK：并列排名，跳过后续名次
SELECT 
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

-- DENSE_RANK：并列排名，不跳过后续名次
SELECT 
    name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

-- 对比示例
+-------+-------+----------+------+------------+
| name  | score | row_num  | rank | dense_rank |
+-------+-------+----------+------+------------+
| Alice |    95 |        1 |    1 |          1 |
| Bob   |    90 |        2 |    2 |          2 |
| Carol |    90 |        3 |    2 |          2 |
| Dave  |    85 |        4 |    4 |          3 |
+-------+-------+----------+------+------------+

-- 按班级排名
SELECT 
    name,
    class,
    score,
    ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS class_rank
FROM students;

+-------+-------+-------+------------+
| name  | class | score | class_rank |
+-------+-------+-------+------------+
| Alice | A     |    95 |          1 |
| Bob   | A     |    90 |          2 |
| Carol | B     |    92 |          1 |
| Dave  | B     |    88 |          2 |
+-------+-------+-------+------------+

-- 累计求和
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,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

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

-- LEAD：获取后一行的值
SELECT 
    order_date,
    amount,
    LEAD(amount) OVER (ORDER BY order_date) AS next_amount
FROM orders;

-- 指定偏移量和默认值
SELECT 
    order_date,
    amount,
    LAG(amount, 2, 0) OVER (ORDER BY order_date) AS prev_2_amount
FROM orders;

-- FIRST_VALUE：分区第一个值
SELECT 
    name,
    department,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS dept_max
FROM employees;

-- LAST_VALUE：分区最后一个值
SELECT 
    name,
    department,
    salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS dept_min
FROM employees;

-- 获取第N个值
SELECT 
    name,
    score,
    NTH_VALUE(name, 2) OVER (ORDER BY score DESC) AS second_place
FROM students;

-- 将数据分成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_name
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 status = 1 THEN balance ELSE 0 END) AS active_balance,
    SUM(CASE WHEN status = 0 THEN balance ELSE 0 END) AS inactive_balance
FROM users;

-- 自定义排序
SELECT * FROM users
ORDER BY 
    CASE status
        WHEN 1 THEN 1
        WHEN 0 THEN 2
        ELSE 3
    END,
    name;

-- 查找同一部门的其他员工
SELECT 
    e1.name AS employee,
    e2.name AS colleague
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id AND e1.id != e2.id;

-- 查找经理-员工关系
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 笛卡尔积
SELECT u.name, p.product_name
FROM users u
CROSS JOIN products p;

-- 生成所有组合
SELECT d.date, p.product_id
FROM dates d
CROSS JOIN products p;

-- LEFT JOIN：左表所有记录
SELECT u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN：右表所有记录
SELECT u.name, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN（MariaDB不支持，使用UNION模拟）
SELECT u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.name, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 合并结果（去重）
SELECT name FROM users
UNION
SELECT name FROM customers;

-- UNION ALL（不去重）
SELECT name FROM users
UNION ALL
SELECT name FROM customers;

-- MariaDB 10.3+ 支持INTERSECT
-- 交集
SELECT name FROM users
INTERSECT
SELECT name FROM customers;

-- 模拟INTERSECT（旧版本）
SELECT name FROM users
WHERE name IN (SELECT name FROM customers);

-- MariaDB 10.3+ 支持EXCEPT
-- 差集
SELECT name FROM users
EXCEPT
SELECT name FROM customers;

-- 模拟EXCEPT（旧版本）
SELECT name FROM users
WHERE name NOT IN (SELECT name FROM customers);

-- MariaDB 10.2+ 支持GROUPING SETS
SELECT 
    COALESCE(department, 'All') AS department,
    COALESCE(job_title, 'All') AS job_title,
    COUNT(*) AS count
FROM employees
GROUP BY GROUPING SETS (
    (department, job_title),
    (department),
    (job_title),
    ()
);

-- 生成分组汇总
SELECT 
    department,
    job_title,
    COUNT(*) AS count
FROM employees
GROUP BY department, job_title WITH ROLLUP;

-- 等价于
SELECT department, job_title, COUNT(*) FROM employees GROUP BY department, job_title
UNION ALL
SELECT department, NULL, COUNT(*) FROM employees GROUP BY department
UNION ALL
SELECT NULL, NULL, COUNT(*) FROM employees;

-- MariaDB 10.2+ 支持CUBE
-- 生成所有组合的汇总
SELECT 
    department,
    job_title,
    COUNT(*) AS count
FROM employees
GROUP BY CUBE (department, job_title);

-- 每组前N条
WITH ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

-- 传统分页（大偏移量性能差）
SELECT * FROM users ORDER BY id LIMIT 10000, 10;

-- 优化：使用WHERE条件
SELECT * FROM users 
WHERE id > 10000 
ORDER BY id 
LIMIT 10;

-- 使用JOIN优化
SELECT u.* FROM users u
JOIN (SELECT id FROM users ORDER BY id LIMIT 10000, 10) t
ON u.id = t.id;

-- 查找重复数据
SELECT name, COUNT(*) 
FROM users 
GROUP BY name 
HAVING COUNT(*) > 1;

-- 删除重复数据保留一条
DELETE u1 FROM users u1
JOIN users u2 ON u1.name = u2.name AND u1.id > u2.id;