带条件的 Oracle 查询分组

debugcn 发表于 Dev

用户2236102

我有这样的查询结果：

DNAME           JOB       Total Empl    Average Sa
ACCOUNTING      CLERK           1       15600
ACCOUNTING      MANAGER         1       29400
ACCOUNTING      PRESIDENT       1       60000
ACCOUNTING      All Jobs        3       35000
RESEARCH        ANALYST         2       36000
RESEARCH        CLERK           2       11400
RESEARCH        MANAGER         1       35700
RESEARCH        All Jobs        5       26100
SALES           CLERK           1       11400
SALES           MANAGER         1       34200
SALES           SALESMAN        4       16800
SALES           All Jobs        6       18800
All Departments All Jobs        14      24878.5714

有没有可能变成这样？

DNAME           JOB       Total Empl   Average Sa
ACCOUNTING      CLERK           1       15600
                MANAGER         1       29400
                PRESIDENT       1       60000
                All Jobs        3       35000
RESEARCH        ANALYST         2       36000
                CLERK           2       11400
                MANAGER         1       35700
                All Jobs        5       26100
SALES           CLERK           1       11400
                MANAGER         1       34200
                SALESMAN        4       16800
                All Jobs        6       18800
All Departments All Jobs        14      24878.5714

我现有的查询：

SELECT 
DECODE(GROUPING(dname), 1, 'All Departments', dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal"
FROM emp, dept
WHERE dept.deptno = emp.deptno
GROUP BY ROLLUP (dname, job);

蒂姆·比格莱森

一种方法是将您当前的查询放入 CTE，然后仅当它是组中的第一条记录时才生成部门名称：

WITH cte AS (
    SELECT 
        DECODE(GROUPING(d.dname), 1, 'All Departments', d.dname) AS dname,
        DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
        COUNT(*) "Total Empl",
        AVG(sal) * 12 "Average Sal",
        ROW_NUMBER() OVER (PARTITION BY DECODE(GROUPING(d.dname), 1, 'All Departments', d.dname)
            ORDER BY DECODE(GROUPING(job), 1, 'All Jobs', job)) rn
    FROM emp e
    INNER JOIN dept d
        ON d.deptno = e.deptno
    GROUP BY ROLLUP (dname, job)
)

SELECT
    CASE WHEN rn = 1 THEN dname ELSE '' END AS dname,
    job,
    "Total Empl",
    "Average Sal"
FROM cte
ORDER BY
    dname,
    job;

虽然这可能符合您的期望，但此类表示问题通常最好在表示层处理，例如 PHP 之类的东西。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。