现在查到一个count集合:
select count(oper_type) 合计
from man_operlog m
group by m.oper_type但是怎样是想怎样的效果?登录次数 客户数(为上面的count)
20次以上 0
15-19次 0
10-14次 0
5-9次 5
1-4次 0
0次 0
select count(oper_type) 合计
from man_operlog m
group by m.oper_type但是怎样是想怎样的效果?登录次数 客户数(为上面的count)
20次以上 0
15-19次 0
10-14次 0
5-9次 5
1-4次 0
0次 0
你是不是还少了一个标识的字段,例如 user_name 的
sum(case when 合计>=20 then 1 else 0 end) ,
sum(case when 合计>=15 and 合计<=19 then 1 else 0 end) ,
sum(case when 合计>=10 and 合计<=14 then 1 else 0 end) ,
sum(case when 合计>=5 and 合计<=9 then 1 else 0 end) ,
sum(case when 合计>=1 and 合计<=4 then 1 else 0 end) 1,
sum(case when 合计=0 then 1 else 0 end)
from (
select count(oper_type) 合计 from man_operlog m
group by m.oper_type)
如果是count(oper_type)是15的话,15-19就设置为15,其他的为0
用这个java好处理,就是sql不知道怎么转
with tbl as
(
select 1 as oper_type from dual
union all
select 1 as oper_type from dual
union all
select 1 as oper_type from dual
union all
select 1 as oper_type from dual
union all
select 2 as oper_type from dual
union all
select 2 as oper_type from dual
union all
select 1 as oper_type from dual
union all
select 3 as oper_type from dual
)
select "登陆次数", sum("客户数") as "客户数"
from (select case when icount > 20 then '20次以上'
when icount >= 15 and icount <= 19 then '15-19次'
when icount >= 10 and icount <= 14 then '10-14次'
when icount >= 5 and icount <= 9 then '5-9次'
when icount >= 1 and icount <= 4 then '1-4次'
else '0次'
end as "登陆次数",
icount as "客户数"
from (select oper_type, count(*) as icount from tbl group by oper_type)) t
group by t."登陆次数"