19 分组查询语句有哪些

vvEcho 2025-03-12 13:16:11
Categories： Tags：

窗口函数（Window Functions）

适用场景：需在保留原始行数据的同时进行分组聚合（如计算每个分组内的排名、累计值等）

1
2
3
4
5
SELECT
    department_id,
    salary,
    SUM(salary) OVER (PARTITION BY department_id) AS dept_total_salary
FROM employees;

子查询与 JOIN 结合

适用场景：需分步处理分组聚合或跨表关联时

1
2
3
4
5
6
7
8
9
10
11
SELECT
    d.department_name,
    sub.avg_salary
FROM (
    SELECT
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) sub
JOIN departments d ON sub.department_id = d.department_id;

用户变量模拟分组排名

适用场景：需为分组内的行生成序号（如 MySQL 8.0 以下版本无法使用窗口函数时）

1
2
3
4
5
6
7
8
9
10
SET @row_num = 0;
SET @dept = '';

SELECT
    department_id,
    salary,
    @row_num := IF(@dept = department_id, @row_num + 1, 1) AS rank,
    @dept := department_id AS dummy
FROM employees
ORDER BY department_id, salary DESC;

DISTINCT 去重结合聚合函数

适用场景：仅需去重后的分组值，无需复杂聚合

1
2
3
4
SELECT
    DISTINCT department_id,
    COUNT(*) OVER (PARTITION BY department_id) AS num_employees
FROM employees;

使用WITH ROLLUP扩展汇总

适用场景：在 GROUP BY 基础上生成分层小计和总计

1
2
3
4
5
SELECT
    department_id,
    SUM(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;

利用临时表或派生表

适用场景：多次复用分组结果或优化复杂查询性能

1
2
3
4
5
6
7
8
9
10
-- 创建临时表存储分组结果
CREATE TEMPORARY TABLE temp_dept_salary AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- 关联其他表获取详细信息
SELECT d.department_name, t.avg_salary
FROM temp_dept_salary t
JOIN departments d ON t.department_id = d.department_id;
20 mysql如何解决数据倾斜
24 redis常用数据结构命令及代码实现