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