按蜂箱分组

Question

是否有任何方法可以使用查询获得蜂巢中的预期输出？

select (CASE WHEN visit=1 THEN 'ONE' WHEN visit=2 THEN 'TWO' ELSE 'ABOVE_TWO' END),
count(visit) from sample_tbl 
group by (CASE WHEN visit=1 THEN 'ONE' WHEN visit=2 THEN 'TWO' ELSE 'ABOVE_TWO' END);

sample_tbl

参观 1 1 3. 3.

输出

一二 ABOVE_TWO 2

期望的

一二 二零零 ABOVE_TWO 2

Answer 1

我不是蜂巢专家，但你可以试试：

select
   CASE WHEN visit=1 
           THEN 'ONE' 
        WHEN visit=2 
           THEN 'TWO'
        ELSE 'ABOVE_TWO' 
   END as visit,
   cnt
from
(
    select visit, count(*) as cnt
    from sample_tbl
    group by visit
)s

Answer 2

select case visit when 1 then 'ONE' when 2 then 'TWO' else 'ABOVE_TWO' end as visit,count(visit) from sample_tbl GROUP BY visit;