88.按指定上下限区间进行数据统计的案例

阳光穿透心脏的1/2处 2024-02-19 18:16 57阅读 0赞
  1. --测试数据
  2. DECLARE @t TABLE(ID int PRIMARY KEY,col decimal(10,2))
  3. INSERT @t SELECT 1 ,26.21
  4. UNION ALL SELECT 2 ,88.19
  5. UNION ALL SELECT 3 , 4.21
  6. UNION ALL SELECT 4 ,76.58
  7. UNION ALL SELECT 5 ,58.06
  8. UNION ALL SELECT 6 ,53.01
  9. UNION ALL SELECT 7 ,18.55
  10. UNION ALL SELECT 8 ,84.90
  11. UNION ALL SELECT 9 ,95.60
  12. --统计
  13. SELECT a.Description,
  14. Record_count=COUNT(b.ID),
  15. [Percent]=CASE
  16. WHEN Counts=0 THEN '0.00%'
  17. ELSE CAST(CAST(
  18. COUNT(b.ID)*100./c.Counts
  19. as decimal(10,2)) as varchar)+'%'
  20. END
  21. FROM(
  22. SELECT sid=1,a=NULL,b=30 ,Description='<30' UNION ALL
  23. SELECT sid=2,a=30 ,b=60 ,Description='>=30 and <60' UNION ALL
  24. SELECT sid=3,a=60 ,b=75 ,Description='>=60 and <75' UNION ALL
  25. SELECT sid=4,a=75 ,b=95 ,Description='>=75 and <95' UNION ALL
  26. SELECT sid=5,a=95 ,b=NULL,Description='>=95'
  27. )a LEFT JOIN @t b
  28. ON (b.col=a.a OR a.a IS NULL)
  29. CROSS JOIN(
  30. SELECT COUNTS=COUNT(*) FROM @t
  31. )c
  32. GROUP BY a.Description,a.sid,c.COUNTS
  33. ORDER BY a.sid
  34. /*--结果:
  35. Description Record_count Percent
  36. ------------------- ------------------ ----------------------
  37. <30 3 33.33%
  38. >=30 and <60 2 22.22%
  39. >=60 and <75 0 0.00%
  40. >=75 and <95 3 33.33%
  41. >=95 1 11.11%
  42. --*/

发表评论

表情:
评论列表 (有 0 条评论,57人围观)

还没有评论,来说两句吧...

相关阅读