分组聚合与 HAVING

SELECT COUNT(*)                        AS all_rows,        -- 107，含所有行
       COUNT(commission_pct)           AS non_null_comm,   -- 只有 35
       COUNT(DISTINCT department_id)   AS distinct_dept,   -- 不同的部门数
       SUM(salary)                     AS total_salary,
       ROUND(AVG(salary), 2)           AS avg_salary,
       MIN(hire_date)                  AS first_hire,
       MAX(salary)                     AS max_salary
FROM   employees;

SELECT ROUND(AVG(commission_pct), 4)             AS avg_ignore_null,  -- 忽略 NULL
       ROUND(SUM(commission_pct)/COUNT(*), 4)      AS avg_include_null, -- 把 NULL 当 0 摊
       COUNT(*)                                    AS total_emp,
       COUNT(commission_pct)                       AS emp_with_comm
FROM   employees;

SELECT d.department_name,
       COUNT(*)                 AS headcount,
       ROUND(AVG(e.salary), 2)  AS avg_salary,
       SUM(e.salary)            AS total_salary
FROM   employees   e
JOIN   departments d ON d.department_id = e.department_id
WHERE  e.salary > 3000           -- 分组前：过滤行
GROUP  BY d.department_name
HAVING COUNT(*) >= 3             -- 分组后：过滤组
ORDER  BY avg_salary DESC;

SELECT department_id,
       job_id,
       COUNT(*)        AS cnt,
       SUM(salary)     AS total
FROM   employees
GROUP  BY ROLLUP(department_id, job_id)
ORDER  BY department_id NULLS LAST, job_id NULLS LAST;

ROLLUP 会逐级汇总：明细 → 部门小计 → 总计，小计行的分组列显示为 NULL。

SELECT department_id,
       GROUPING(department_id)  AS is_subtotal,   -- 1 表示这是汇总行
       SUM(salary)              AS total
FROM   employees
GROUP  BY ROLLUP(department_id)
ORDER  BY is_subtotal, department_id NULLS LAST;

SELECT department_id,
       LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS members
FROM   employees
WHERE  department_id IN (50, 60, 80)
GROUP  BY department_id
ORDER  BY department_id;

拼接结果默认上限 4000 字节，超长会报 ORA-01489，需要配合 ON OVERFLOW TRUNCATE。