mysql - 在 MySQL 中使用 IF 输出作为变量

Question

我有一个表alpha有3列，其中id(INT)和time1，time2(DATETIME)

现在我想计算从 2017-04-18 到 2017-05-24 每天有多少行，我尝试了此查询但失败了

SELECT IF(time1 < time2, time2, time1) AS d, COUNT(DATE(d)) FROM alpha 
WHERE time1 IS NOT NULL AND time2 IS NOT NULL
AND DATE(d) BETWEEN DATE('2017-04-18') AND DATE('2017-05-24')

数据

ID  TIME1                  TIME2
-----------------------------------------------
1, '2017-04-19 04:08:03', '2017-04-19 04:08:03'
2, '2017-04-19 04:08:03', '2017-05-19 04:08:03'
3, '2017-06-19 04:08:03', '2017-04-19 04:08:03'
4, '2017-06-19 04:08:03', '2017-06-20 04:08:03'
5, '2017-05-19 04:08:03', '2017-05-01 04:08:03'

预期结果

DAY         COUNT
---------------------
2017-04-19  1
2017-05-19  2

Answer 1

一种可能的解决方案:

  SELECT DATE(d), COUNT(DATE(d)) FROM (
    SELECT IF(time1 < time2, time2, time1) AS d FROM alpha 
    WHERE time1 IS NOT NULL AND time2 IS NOT NULL
  ) AS t
  WHERE DATE(d) BETWEEN DATE('2017-04-18') AND DATE('2017-05-24')
  GROUP BY DATE(d);

或者我不喜欢:

SELECT IF(time1 < time2, time2, time1) AS d, COUNT(DATE(IF(time1 < time2, time2, time1))) FROM alpha 
WHERE time1 IS NOT NULL AND time2 IS NOT NULL
AND DATE(IF(time1 < time2, time2, time1)) BETWEEN DATE('2017-05-13') AND DATE('2017-05-15')
GROUP BY DATE(IF(time1 < time2, time2, time1));