mysql根据俩个字段之差进行区间分组

一、表结构

 

 二、需求

1.根据字段jobid和subtime之差的区间进行分组，区间分别为[0-600],[600-1200],[1200-1800],[1800-3600],[3600-21600],[21600-43200],[43200+]

2.语句

select case
         when (a.subtime-a.jobid) <=600 then
          '[0-600]'
         when (a.subtime-a.jobid) >600 and (a.subtime-a.jobid) <=1200 then
          '[600-1200]'
         when (a.subtime-a.jobid) >1200 and (a.subtime-a.jobid) <=1800 then
          '[1200,1800]'
         when (a.subtime-a.jobid) >1800 and (a.subtime-a.jobid) <=3600 then
          '[1800-3600]'
        when (a.subtime-a.jobid) >3600 and (a.subtime-a.jobid) <=21600 then
          '[3600-21600]'
        when (a.subtime-a.jobid) >21600 and (a.subtime-a.jobid) <=43200 then
          '[21600-43200]'
       when (a.subtime-a.jobid) >43200 then
          '[43200+]'
       end group1,
       COUNT(a.queue) as total
  from sgelog a
 group by case
         when (a.subtime-a.jobid) <=600 then
          '[0-600]'
         when (a.subtime-a.jobid) >600 and (a.subtime-a.jobid) <=1200 then
          '[600-1200]'
         when (a.subtime-a.jobid) >1200 and (a.subtime-a.jobid) <=1800 then
          '[1200,1800]'
         when (a.subtime-a.jobid) >1800 and (a.subtime-a.jobid) <=3600 then
          '[1800-3600]'
        when (a.subtime-a.jobid) >3600 and (a.subtime-a.jobid) <=21600 then
          '[3600-21600]'
        when (a.subtime-a.jobid) >21600 and (a.subtime-a.jobid) <=43200 then
          '[21600-43200]'
       when (a.subtime-a.jobid) >43200 then
          '[43200+]'
       end;

3.执行结果