sql - 在 4 行之后对所有行进行分组并创建第 5 行分组行

Question

我有一个查询：

SELECT *
FROM   (
  SELECT WORKING_DAYS, ACTIVITY
  FROM TABLE
)
PIVOT (
  COUNT(ACTIVITY)
  FOR ACTIVITY IN (
     6 AS NL6,
     7 AS NL7,
     8 AS NL8,
     9 AS NL9,
    11 AS NL11
  )
)
ORDER BY WORKING_DAYS;

上述查询的输出如下：

我试图输出如下，但我不知道如何继续：

任何人都可以在这里提供帮助并让我知道最好的解决方案吗？

我正在使用 Oracle 版本 - 12.1.0.2

标签： sqloraclepivotcaseoracle12c

Answer 1

使用条件聚合更容易做到这一点：

select 
    case when working_days > 4 then '> 4' else to_char(working_days) end as working_days,
    sum(case when activity =  6 then 1 else 0 end) nls6,
    sum(case when activity =  7 then 1 else 0 end) nls7,
    sum(case when activity =  8 then 1 else 0 end) nls8,
    sum(case when activity =  9 then 1 else 0 end) nls9
    sum(case when activity = 11 then 1 else 0 end) nls11
from mytable
group by case when working_days > 4 then '> 4' else to_char(working_days) end