mysql把日期相减并按照区间范围统计

数据计算导入时间和审核时间之间的差并且按照时间区间，整理成excel画出图表。

关键的表字段如上图所示。

首先两个日期的加减涉及到mysql的函数datediff（），可以计算出两者之间的差值

SELECT
  fk_comp_id,
  datediff(
    LEFT (f_create_time, 8),
    LEFT (f_date, 8)
  )
FROM
  t_athlete_score
WHERE
  f_year = 2019
GROUP BY      

然后计算差值在什么范围之内，使用到mysql的函数INTERVAL()，INTERVAL()函数进行比较列表(N1，N2，N3等等)中的N值。该函数如果N<N1返回0，如果N<N2返回1，如果N<N3返回2 等等。如果N为NULL，它将返回-1。列表值必须是N1<N2<N3的形式才能正常工作。下面的代码是显示 INTERVAL()函数如何工作的一个简单的例子：

mysql>SELECT INTERVAL(6,1,2,3,4,5,6,7,8,9,10);
+---------------------------------------------------------+
| INTERVAL(6,1,2,3,4,5,6,7,8,9,10)                        |
+---------------------------------------------------------+
| 6                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)      

结果中，6是从零开始的索引，列表第一个值的值大于N. 在我们的例子中，6 属于 [6,7) ，所以返回第6个索引

sql可以演化为：

SELECT
  
    INTERVAL (
      datediff(
        LEFT (f_create_time, 8),
        LEFT (f_date, 8)
      ),
      0,
      10,
      20,
      30,
      40,
      50,
      60,
      70,
      80,
      90,
      100,
      110,
      120
    ) AS dayNumber,
  fk_comp_id,
  datediff(
    LEFT (f_create_time, 8),
    LEFT (f_date, 8)
  )
FROM
  t_athlete_score
WHERE
  f_year = 2019
GROUP BY      

查询出来的结果，如图所示：

然后为了能更好看，表达的更清晰一点，可以使用ELT()函数，将每个值赋予意义，ELT(n,str1,str2,str3,…) ：如果n=1，则返回str1,如果n=2，则返回str2,依次类推。如果n小于1或大于参数个数，返回NULL。

mysql> SELECT ELT(3,'hello','halo','test','world');
+--------------------------------------+
| ELT(3,'hello','halo','test','world') |
+--------------------------------------+
| test                                 |
+--------------------------------------+
1 row in set      

此时，mysql的SQL语句演变为：

SELECT
  ELT(INTERVAL(datediff(LEFT (f_create_time, 8),LEFT (f_date, 8)),0,10,20,30,40,50,60,70,80,90,100,110,120),'<=10','10<x<=20','20<x<=30','30<x<=40','40<x<=50','50<x<=60','60<x<=70','70<x<=80','80<x<=90','90<x<=100','100<x<=110','110<x<=120','x>=120') as dayNumber,
  fk_comp_id,
datediff(LEFT (f_create_time, 8),LEFT (f_date, 8))
FROM
  t_athlete_score
WHERE
  f_year = 2019
GROUP BY      

然后统计每个区间的个数，sql演化为：

select m.dayNumber,count(fk_comp_id) from (
SELECT
  ELT(
    INTERVAL (
      datediff(
        LEFT (f_create_time, 8),
        LEFT (f_date, 8)
      ),
      0,
      10,
      20,
      30,
      40,
      50,
      60,
      70,
      80,
      90,
      100,
      110,
      120
    ),
    '<=10',
    '10<x<=20',
    '20<x<=30',
    '30<x<=40',
    '40<x<=50',
    '50<x<=60',
    '60<x<=70',
    '70<x<=80',
    '80<x<=90',
    '90<x<=100',
    '100<x<=110',
    '110<x<=120',
    'x>=120'
  ) AS dayNumber,
  fk_comp_id,
  datediff(
    LEFT (f_create_time, 8),
    LEFT (f_date, 8)
  ),INTERVAL (
      datediff(
        LEFT (f_create_time, 8),
        LEFT (f_date, 8)
      ),
      0,
      10,
      20,
      30,
      40,
      50,
      60,
      70,
      80,
      90,
      100,
      110,
      120
    ) as orderindex
FROM
  t_athlete_score
WHERE
  f_year = 2019
GROUP BY
  fk_comp_id
) as m GROUP BY m.dayNumber ORDER BY m.orderindex      

查询出来的结果为：