还是刚才的问题,要在输出数据加上 平均数 的分析,请 tntzbzc,roy_88...继续领分
以下问题本在以下地址解决,但现增加一个 平均数 的分析指标.http://community.csdn.net/Expert/topic/5318/5318406.xml?temp=.9264337
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.SpanBegin ~ cLevel.SpanEnd 级别进行关联
Level = cMain.integral 达到 cLevel.SpanBegin ~ cLevel.SpanEnd 对应的级别
Amount = 达到某种级别的累计个数
Percentage = 达到某种级别的累计个数 / cMain的总记录个数
Avgintegral = 达到某种级别的 integral 总和 / 达到某种级别的累计个数
输出:
----------------------------------------
Level Amount Avgintegral Percentage
----------------------------------------
A 1 10000.52 12.50%
B 0 0.00 0.00%
C 2 1165.14 25.00%
D 3 361.74 37.50%
E 2 5.00 25.00%
在线等待ing... 先谢过了
[解决办法]
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 , cast(isnull(q.integral,0.00) as decimal(18,2)) as Avgintegral , isnull(q.percentage, '0.00% ') as percentage from clevel
left join
(
select o.jb , o.amount , o.integral , 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,avg(n.integral) as integral 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 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 行）
[解决办法]
没分我照样来凑热闹~
SELECT [Level],AMOUNT,cast(Avgintegral as decimal(10,2)),
cast(cast(AMOUNT as float)/(SELECT COUNT(1) FROM #cMain)*100 as varchar(10))+ '% ' as Percentage from
(
SELECT [Level],
(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 行）