sql - 从另一个表创建 View

Question

我想从另一个表创建一个 View ..

当前 View 有以下内容(图片 1):

尝试从上面的 View 中获取输出(图 2)，即对这些列中的值进行分组:

Answer 1

一种方法是取消数据透视然后重新聚合:

select w,
       sum(case when i = 0 then 1 else 0 end) as w1,
       sum(case when i = 1 then 1 else 0 end) as w2,
       sum(case when i = 2 then 1 else 0 end) as w3,
       sum(case when i = 3 then 1 else 0 end) as w4
from (select array[w1, w2, w3, w4] ws
      from t
     ) t cross join
     unnest(ws) w with offset i
group by w;

这是一个更完整的例子:

with t as (
      select 1 as slno, '<5' as w1, '<5' as w2, '<5' as w3, '<5' as w4 union all
      select 1 as slno, '<5' as w1, '5 to 10' as w2, '<5' as w3, '5 to 10' as w4 
     )
select w,
       sum(case when i = 0 then 1 else 0 end) as w1,
       sum(case when i = 1 then 1 else 0 end) as w2,
       sum(case when i = 2 then 1 else 0 end) as w3,
       sum(case when i = 3 then 1 else 0 end) as w4
from (select array[w1, w2, w3, w4] as ws
      from t
     ) t cross join
     unnest(ws) w with offset i
group by w;