有一张表结构如下
CID CASH
---------- ----------
1 5
1 51
2 53
3 521
4 103
5 129
6 198
7 256
5 356
8 456
cid为用户ID,cash为用户消费金额,
其中0-200为A等级,201-500为B等级,
要求用一个SQL语句统计出A,B等级所占总比例.
大侠帮忙!
CID CASH
---------- ----------
1 5
1 51
2 53
3 521
4 103
5 129
6 198
7 256
5 356
8 456
cid为用户ID,cash为用户消费金额,
其中0-200为A等级,201-500为B等级,
要求用一个SQL语句统计出A,B等级所占总比例.
大侠帮忙!
select a.Rank_A/c.summary, b.Rank_B/c.summary
from
(select count(cid) summary
from tab) c,
(select count(cid) Rank_A
from tab
where cash between 0 and 200) a,
(select count(cid) Rank_B
from tab
where cash between 200 and 500) b
:
select sum(case when cash>0 and cash<=200 then 1 else 0 end)/count(*) as 一等级,
sum(case when cash>200 and cash<=500 then 1 else 0 end)count(*) as 二等级 from test_customer;
sum(Decode( Sign(cash-200), -1, 1, 0 )) as LevelA,
sum(Decode( Sign(cash-200), -1, 0, 1 )) as LevelB,
count(*) as LevelAllfrom tab一下子把各个级别的数量和总的个数统计出来
使用的时候再算百分比
decode(countS,0,0,countA/countS) a,
decode(countS,0,0,countB/countS) b
from
(
select
sum(case when cash<=200 1 else 0 end) countA,
sum(case when cash>200 and case<=500 then 1 else 0 end) countB,
count(1) countS
from Table
) t