在甲骨文中获得月度年度报告

Question

我有一个包含以下字段的employee表:员工姓名、工资日期、我想要汇总的月工资。这是表数据。我使用oracle数据库11g。

​

​

这是我想要的输出。

​

​

Answer 1

您可以在您的ROLLUP中使用GROUP BY

WITH t(NAME, dt, w) AS (
    SELECT 'adam', DATE '2020-01-01', 200 FROM dual UNION ALL
    SELECT 'adam', DATE '2020-02-01', 200 FROM dual UNION ALL
    SELECT 'adam', DATE '2020-03-01', 200 FROM dual UNION ALL
    SELECT 'jhone', DATE '2020-01-01', 100 FROM dual UNION ALL
    SELECT 'jhone', DATE '2020-02-01', 200 FROM dual UNION ALL
    SELECT 'jhone', DATE '2020-03-01', 151 FROM dual
    )
SELECT NAME, NVL(TO_CHAR(dt, 'fmMon'), 'total') AS mon, SUM(w) AS sum_w
FROM t
GROUP BY NAME, ROLLUP(TO_CHAR(dt, 'fmMon'));


+-----------------+
|NAME |MON  |SUM_W|
+-----------------+
|adam |Feb  |200  |
|adam |Jan  |200  |
|adam |Mar  |200  |
|adam |total|600  |
|jhone|Feb  |200  |
|jhone|Jan  |100  |
|jhone|Mar  |151  |
|jhone|total|451  |
+-----------------+

如果您需要转接结果，您可以PIVOT它：

WITH t(NAME, dt, w) AS (
    SELECT 'adam', DATE '2020-01-01', 200 FROM dual UNION ALL
    SELECT 'adam', DATE '2020-02-01', 200 FROM dual UNION ALL
    SELECT 'adam', DATE '2020-03-01', 200 FROM dual UNION ALL
    SELECT 'jhone', DATE '2020-01-01', 100 FROM dual UNION ALL
    SELECT 'jhone', DATE '2020-02-01', 200 FROM dual UNION ALL
    SELECT 'jhone', DATE '2020-03-01', 151 FROM dual
    )
SELECT *
FROM (
    SELECT NAME, NVL(TO_CHAR(dt, 'fmMon'), 'total') AS mon, SUM(w) AS sum_w
    FROM t
    GROUP BY NAME, ROLLUP(TO_CHAR(dt, 'fmMon'))
    )
PIVOT (
   SUM(sum_w)
   FOR mon IN ('Jan','Feb','Mar','total')
    );
    
+-------------------------------+
|NAME |'Jan'|'Feb'|'Mar'|'total'|
+-------------------------------+
|adam |200  |200  |200  |600    |
|jhone|100  |200  |151  |451    |
+-------------------------------+

Answer 2

您可以按以下方式使用条件聚合：

Select name,
       Sum(case when to_char(date,'mon') = 'jan' then wages end) as jan,
       Sum(case when to_char(date,'mon') = 'feb' then wages end) as feb,
       ...
       Sum(wages) as total
From yourTable
Group by name;

您需要使用where条件只考虑一年的数据。