对数据按照范围进行分组查询
ALTER PROC [dbo].[pro_GetE2ERange] @GSC varchar(20)='', @cluster varchar(100)='', @Team varchar(100)='', @BA varchar(100)='' AS SELECT CASE WHEN CAST(E2E AS float)/24.0<=1 THEN '<= 1' WHEN CAST(E2E AS float)/24.0>1 AND CAST(E2E AS float)/24.0<=2 THEN '<= 2' WHEN CAST(E2E AS float)/24.0>2 AND CAST(E2E AS float)/24.0<=3 THEN '<= 3' WHEN CAST(E2E AS float)/24.0>3 AND CAST(E2E AS float)/24.0<=4 THEN '<= 4' WHEN CAST(E2E AS float)/24.0>4 AND CAST(E2E AS float)/24.0<=5 THEN '<= 5' WHEN CAST(E2E AS float)/24.0>5 AND CAST(E2E AS float)/24.0<=6 THEN '<= 6' WHEN CAST(E2E AS float)/24.0>6 AND CAST(E2E AS float)/24.0<=7 THEN '<= 7' WHEN CAST(E2E AS float)/24.0>7 AND CAST(E2E AS float)/24.0<=8 THEN '<= 8' WHEN CAST(E2E AS float)/24.0>8 AND CAST(E2E AS float)/24.0<=9 THEN '<= 9' WHEN CAST(E2E AS float)/24.0>9 AND CAST(E2E AS float)/24.0<=10 THEN '<=10 ' WHEN CAST(E2E AS float)/24.0>10 AND CAST(E2E AS float)/24.0<=11 THEN '<=11 ' WHEN CAST(E2E AS float)/24.0>11 AND CAST(E2E AS float)/24.0<=12 THEN '<=12 ' WHEN CAST(E2E AS float)/24.0>12 AND CAST(E2E AS float)/24.0<=13 THEN '<=13 ' WHEN CAST(E2E AS float)/24.0>13 AND CAST(E2E AS float)/24.0<=14 THEN '<=14 ' WHEN CAST(E2E AS float)/24.0>14 AND CAST(E2E AS float)/24.0<=15 THEN '<=15 ' WHEN CAST(E2E AS float)/24.0>15 THEN '>15' END E2ERange,count(*) NUM FROM ReportView WHERE GSC LIKE CASE WHEN @gsc='' THEN '%%' ELSE @gsc END AND Cluster LIKE CASE WHEN @cluster='' then '%%' ELSE @cluster end AND team like case when @team='' then '%%' else @team end and BA like case when @ba='' then '%%' else @ba end GROUP BY CASE WHEN CAST(E2E AS float)/24.0<=1 THEN '<= 1' WHEN CAST(E2E AS float)/24.0>1 AND CAST(E2E AS float)/24.0<=2 THEN '<= 2' WHEN CAST(E2E AS float)/24.0>2 AND CAST(E2E AS float)/24.0<=3 THEN '<= 3' WHEN CAST(E2E AS float)/24.0>3 AND CAST(E2E AS float)/24.0<=4 THEN '<= 4' WHEN CAST(E2E AS float)/24.0>4 AND CAST(E2E AS float)/24.0<=5 THEN '<= 5' WHEN CAST(E2E AS float)/24.0>5 AND CAST(E2E AS float)/24.0<=6 THEN '<= 6' WHEN CAST(E2E AS float)/24.0>6 AND CAST(E2E AS float)/24.0<=7 THEN '<= 7' WHEN CAST(E2E AS float)/24.0>7 AND CAST(E2E AS float)/24.0<=8 THEN '<= 8' WHEN CAST(E2E AS float)/24.0>8 AND CAST(E2E AS float)/24.0<=9 THEN '<= 9' WHEN CAST(E2E AS float)/24.0>9 AND CAST(E2E AS float)/24.0<=10 THEN '<=10 ' WHEN CAST(E2E AS float)/24.0>10 AND CAST(E2E AS float)/24.0<=11 THEN '<=11 ' WHEN CAST(E2E AS float)/24.0>11 AND CAST(E2E AS float)/24.0<=12 THEN '<=12 ' WHEN CAST(E2E AS float)/24.0>12 AND CAST(E2E AS float)/24.0<=13 THEN '<=13 ' WHEN CAST(E2E AS float)/24.0>13 AND CAST(E2E AS float)/24.0<=14 THEN '<=14 ' WHEN CAST(E2E AS float)/24.0>14 AND CAST(E2E AS float)/24.0<=15 THEN '<=15 ' WHEN CAST(E2E AS float)/24.0>15 THEN '>15' END order by e2erange