# 还是刚才的有关问题,要在输出数据加上 平均数 的分析,请 tntzbzc,roy_88.继续领分(3)

www.MyException.Cn  网友分享于：2013-01-22  浏览：6次

(SELECT COUNT(1) FROM #cMain WHERE INTEGRAL BETWEEN SPANBEGIN AND SPANEND) AS AMOUNT,
(SELECT isnull(avg(INTEGRAL),0) FROM #cMain WHERE INTEGRAL BETWEEN SPANBEGIN AND SPANEND) AS Avgintegral
FROM #cLevel
)t
order by [Level]
------解决方案--------------------
create table cMain(ClientID int,ClientName varchar(20),integral dec(10,2))
insert cMain select 0001, 'sa ',1050.28
insert cMain select 0002, 'li ',100.22
insert cMain select 0003, 'liko ',135.00
insert cMain select 0004, 'coco ',10000.52
insert cMain select 0005, 'mico ',850.00
insert cMain select 0006, 'IOTN ',10.00
insert cMain select 0007, 'DSTI ',1280.00
insert cMain select 0008, 'HOKU ',0.00

create table cLevel(SpanBegin dec(10,2),SpanEnd dec(10,2),Level varchar(5))
insert cLevel select 0,100.00, 'E '
insert cLevel select 100.01,1000.00, 'D '
insert cLevel select 1000.01,3000.00, 'C '
insert cLevel select 3000.02,5000.00, 'B '
insert cLevel select 5000.01,9999999.00, 'A '

select Level,
(select count(*) from cMain where integral between a.SpanBegin and a.SpanEnd) Amout,
Avgintegral=isnull((select cast(avg(integral) as dec(10,2)) from cMain where integral between a.SpanBegin and a.SpanEnd),0.00),
rtrim(cast((select count(*) from cMain where integral between a.SpanBegin and a.SpanEnd)*1.0/(select count(*) cont from cMain where integral between (select min(SpanBegin) from cLevel) and (select max(SpanEnd) from cLevel) )*100 as dec(5,2)))+ '% ' Percentpage
from cLevel a

drop table cMain,cLevel

------解决方案--------------------
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 '
--改一下保留Avgintegral的两位小数
select [Level],Amount=(select count(1)from @cMain where integral between SpanBegin and SpanEnd)
,Avgintegral=(select cast(isnull(avg(integral),0)as decimal(15,2)) from @cMain where integral between SpanBegin and SpanEnd)--加一段avg平均值就行了
,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 Avgintegral Percentage
----- ----------- ----------------- -----------------------------------------
A 1 10000.52 12.50%
B 0 .00 0.00%
C 2 1165.14 25.00%
D 3 361.74 37.50%
E 2 5.00 25.00%

（所影响的行数为 5 行）