■■■ 求一个关于 百分比 的数据统计。即解即结... ■■■
cMain
-----------------------------------------
ClientID ClientName integral
-----------------------------------------
0001 sa 1050.28
0002 li 100.22
0003 liko 135.00
0004 coco 10000.52
0005 mico 850.00
0006 IOTN 10.00
0007 DSTI 1280.00
0008 HOKU 0.00
...更多记录
cLevel
-----------------------------------------
SpanBegin SpanEnd Level
-----------------------------------------
0 100.00 E
100.01 1000.00 D
1000.01 3000.00 C
3000.02 5000.00 B
5000.01 9999999.00 A
...分段不固定
要求: 统计cMain.integral 达到 cLevel.Level 的数量和百分比例
输出:
------------------------------
Level Amount Percentage
------------------------------
A 1 12.50%
B 0 0.00%
C 2 25.00%
D 3 37.50%
E 2 25.00%
在线等待ing... 先谢过了
[解决办法]
SELECT [Level],
(SELECT COUNT(1) FROM cMain WHERE INTEGRAL BETWEEN SPANBEGIN AND SPANEND) AS AMOUNT,
((SELECT COUNT(1) FROM cMain WHERE INTEGRAL BETWEEN SPANBEGIN AND SPANEND)/(SELECT COUNT(1) FROM cMain)) AS Percentage
FROM cLevel A
[解决办法]
/*
create table cMain(ClientID varchar(10),ClientName varchar(10), integral float)
insert into cMain
select '0001 ', 'sa ',1050.28
union all select '0002 ', 'li ',100.22
union all select '0003 ', 'liko ',135.00
union all select '0004 ', 'coco ',10000.52
union all select '0005 ', 'mico ',850.00
union all select '0006 ', 'IOTN ',10.00
union all select '0007 ', 'DSTI ',1280.00
union all select '0008 ', 'HOKU ',0.00
select * from cMain
create table cLevel(SpanBegin float, SpanEnd float, [Level] varchar(1))
insert into cLevel
select 0,100.00, 'E '
union all select 100.01,1000.00, 'D '
union all select 1000.01,3000.00, 'C '
union all select 3000.02,5000.00, 'B '
union all select 5000.01,9999999.00, 'A '
*/
select LEVEL,isnull(sum(ct),0)Amount,cast(cast(isnull(sum(ct),0)*1.0/(select count(1) from cMain)*100 as int) as varchar(3))+ '% 'Percentage from cLevel a
left join (select count(1)ct,integral from cMain group by integral)b
on b.integral between a.spanbegin and a.spanend
group by LEVEL
/*
------------------------------
Level Amount Percentage
------------------------------
A 1 12.50%
B 0 0.00%
C 2 25.00%
D 3 37.50%
E 2 25.00%
*/
[解决办法]
试试看
SELECT [Level],AMOUNT,cast(AMOUNT as decimal(10,2))/(SELECT COUNT(1) FROM #cMain)*100 as Percentage from
(
SELECT [Level],
(SELECT COUNT(1) FROM #cMain WHERE INTEGRAL BETWEEN SPANBEGIN AND SPANEND) AS AMOUNT
FROM #cLevel A)t
order by [Level]
[解决办法]
tntzbzc(华裔大魔王—抗日要从娃娃抓起)
正解~~~
[解决办法]
if object_id( 'pubs..cMain ') is not null
drop table cMain
go
create table cMain(
ClientID varchar(10),
ClientName varchar(10),
integral decimal(18,2))
insert into cMain(ClientID,ClientName,integral) values( '0001 ', 'sa ' , 1050.28)
insert into cMain(ClientID,ClientName,integral) values( '0002 ', 'li ' , 100.22)
insert into cMain(ClientID,ClientName,integral) values( '0003 ', 'liko ', 135.00)
insert into cMain(ClientID,ClientName,integral) values( '0004 ', 'coco ', 10000.52)
insert into cMain(ClientID,ClientName,integral) values( '0005 ', 'mico ', 850.00)
insert into cMain(ClientID,ClientName,integral) values( '0006 ', 'IOTN ', 10.00)
insert into cMain(ClientID,ClientName,integral) values( '0007 ', 'DSTI ', 1280.00)
insert into cMain(ClientID,ClientName,integral) values( '0008 ', 'HOKU ', 0.00)
if object_id( 'pubs..cLevel ') is not null
drop table cLevel
go
create table cLevel(
SpanBegin decimal(18,2),
SpanEnd decimal(18,2),
Level varchar(10))
insert into cLevel(SpanBegin,SpanEnd,Level) values(0 , 100.00 , 'E ')
insert into cLevel(SpanBegin,SpanEnd,Level) values(100.01 , 1000.00 , 'D ')
insert into cLevel(SpanBegin,SpanEnd,Level) values(1000.01, 3000.00 , 'C ')
insert into cLevel(SpanBegin,SpanEnd,Level) values(3000.02, 5000.00 , 'B ')
insert into cLevel(SpanBegin,SpanEnd,Level) values(5000.01, 9999999.00 , 'A ')
select clevel.level , isnull(q.amount,0) as amount , isnull(q.percentage, '0.00% ') as percentage from clevel
left join
(
select o.jb , o.amount , cast(cast((cast(o.amount as decimal(18,2)) / p.amount) * 100 as decimal(18,2)) as varchar(10)) + '% ' as Percentage from
(
select n.jb , count(*) as amount from
(
select m.* from
(select cMain.* , case when cmain.integral > = clevel.spanbegin and cmain.integral <= clevel.spanend then cLevel.level end as jb
from cmain , clevel
) m
where jb is not null
) n
group by jb
) o ,
(select count(*) as amount from cmain) p
) q
on clevel.level = q.jb
order by clevel.level
drop table cLevel
drop table cMain
level amount percentage
---------- ----------- -----------
A 1 12.50%
B 0 0.00%
C 2 25.00%
D 3 37.50%
E 2 25.00%
（所影响的行数为 5 行）
[解决办法]
declare @cMain table (ClientID varchar(10),ClientName varchar(10), integral float)
insert @cMain
select '0001 ', 'sa ',1050.28
union all select '0002 ', 'li ',100.22
union all select '0003 ', 'liko ',135.00
union all select '0004 ', 'coco ',10000.52
union all select '0005 ', 'mico ',850.00
union all select '0006 ', 'IOTN ',10.00
union all select '0007 ', 'DSTI ',1280.00
union all select '0008 ', 'HOKU ',0.00
declare @cLevel table (SpanBegin float, SpanEnd float, [Level] varchar(1))
insert @cLevel
select 0,100.00, 'E '
union all select 100.01,1000.00, 'D '
union all select 1000.01,3000.00, 'C '
union all select 3000.02,5000.00, 'B '
union all select 5000.01,9999999.00, 'A '
select [Level],Amount=(select count(1)from @cMain where integral between SpanBegin and SpanEnd)
,Percentage=
(select rtrim(cast(cast(count(1)as decimal(15,2))/(select count(1)from @cMain)*100 as decimal(15,2)))+ '% '
from @cMain where integral between SpanBegin and SpanEnd)
from @cLevel order by Level
（所影响的行数为 8 行）
（所影响的行数为 5 行）
Level Amount Percentage
----- ----------- -----------------------------------------
A 1 12.50%
B 0 0.00%
C 2 25.00%
D 3 37.50%
E 2 25.00%
（所影响的行数为 5 行）
