统计sql语句

--测试数据

DECLARE @t TABLE(ID int PRIMARY KEY,col decimal(10,2))

INSERT @t SELECT 1 ,26.21

UNION ALL SELECT 2 ,88.19

UNION ALL SELECT 3 , 4.21

UNION ALL SELECT 4 ,76.58

UNION ALL SELECT 5 ,58.06

UNION ALL SELECT 6 ,53.01

UNION ALL SELECT 7 ,18.55

UNION ALL SELECT 8 ,84.90

UNION ALL SELECT 9 ,95.60

--统计

SELECT a.Description,

Record_count=COUNT(b.ID),

[Percent]=CASE 

WHEN Counts=0 THEN '0.00%'

ELSE CAST(CAST(

COUNT(b.ID)*100./c.Counts

as decimal(10,2)) as varchar)+'%'

END

FROM(

SELECT sid=1,a=NULL,b=30  ,Description='<30' UNION ALL

SELECT sid=2,a=30  ,b=60  ,Description='>=30 and <60' UNION ALL

SELECT sid=3,a=60  ,b=75  ,Description='>=60 and <75' UNION ALL

SELECT sid=4,a=75  ,b=95  ,Description='>=75 and <95' UNION ALL

SELECT sid=5,a=95  ,b=NULL,Description='>=95' 

)a LEFT JOIN @t b 

ON (b.col<a.b OR a.b IS NULL)

AND(b.col>=a.a OR a.a IS NULL)

CROSS JOIN(

SELECT COUNTS=COUNT(*) FROM @t

)c

GROUP BY a.Description,a.sid,c.COUNTS

ORDER BY a.sid

/*--结果:

Description    Record_count  Percent

------------------- ------------------ ---------------------- 

<30          3            33.33%

>=30 and <60  2            22.22%

>=60 and <75  0            0.00%

>=75 and <95  3            33.33%

>=95         1            11.11%

--*/