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... 先谢过了
-----------------------------------------
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 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
Amount = 达到某种级别的记录个数
Percentage = 达到某种级别的记录个数 / cMain的总记录个数
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 cMaincreate 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]
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 cMaincreate 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 decimal(18,2)) as varchar(5))+'%'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%
*/
(ClientID varchar(50),
ClientName varchar(50),
integral decimal(10,2)
)
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 #cMaindrop table #cLevel
create table #cLevel
(SpanBegin decimal(10,2),
SpanEnd decimal(10,2),
Level varchar(10)
)
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 * from #cLevelSELECT [Level],AMOUNT,cast(cast(AMOUNT as float(10,2))/(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
FROM #cLevel A)t
order by [Level]--------------------
Level AMOUNT Percentage
A 1 12.5%
B 0 0%
C 2 25%
D 3 37.5%
E 2 25%
drop table cMain
gocreate 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
gocreate 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.leveldrop table cLevel
drop table cMainlevel amount percentage
---------- ----------- -----------
A 1 12.50%
B 0 0.00%
C 2 25.00%
D 3 37.50%
E 2 25.00%(所影响的行数为 5 行)
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 cast(count(1)as decimal(15,2))/(select count(1)from @cMain)
from @cMain where integral between SpanBegin and SpanEnd)
from @cLevel (所影响的行数为 8 行)
(所影响的行数为 5 行)Level Amount Percentage
----- ----------- ----------------------------
E 2 .2500000000000
D 3 .3750000000000
C 2 .2500000000000
B 0 .0000000000000
A 1 .1250000000000(所影响的行数为 5 行)
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 行)