对数据按照范围进行分组查询

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 

 

转载于:https://www.cnblogs.com/fly0236/p/3245895.html