# ■■■ 关于 百分比 的数据统计。即解即结. ■■■(2)

www.myexceptions.net  网友分享于：2013-03-25  浏览：6次

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