MySQL Datediff语法

Question

我必须计算当前日期与存储在数据库中的日期(数据操作)之间的天数差异。

我需要知道这几天的区别是：

1. <= 30
2. 30和<= 60
3. 60

我尝试了这个查询sql，但是输出是错误的，因为在所有的行中，总数是不正确的。

当is > 30和<= 60时，如何计算日差？

mysql> SELECT
    IFNULL(action, 'Tot') AS action,
    COUNT(*) AS Tot,
    SUM(

        IF (
            DATEDIFF(
                CURRENT_DATE (),
                dataaction
            ) <= 30,
            1,
            0
        )
    ) '<= 30',
    SUM(

        IF (
            DATEDIFF(
                CURRENT_DATE (),
                dataaction
            ) <= 60,
            1,
            0
        )
    ) '<= 60',
    SUM(

        IF (
            DATEDIFF(
                CURRENT_DATE (),
                dataaction
            ) > 60,
            1,
            0
        )
    ) '> 60'
FROM
    (
        SELECT
            *
        FROM
            `tbl`
    ) t
GROUP BY
    action WITH ROLLUP;
+------------+--------+-------+-------+------+
| action     | Tot    | <= 30 | <= 60 | > 60 |
+------------+--------+-------+-------+------+
| study      |     83 | 7     | 37    | 46   |
| rebuilding |     25 | 25    | 25    | 0    |
| checking   |     95 | 2     | 3     | 92   |
| screening  |     39 | 1     | 4     | 35   |
| Tot        |    242 | 35    | 69    | 173  |
+------------+--------+-------+-------+------+
5 rows in set

mysql> 

Answer 1

正如注释中所要求的那样，这是一个解决此问题的开关/案例语法的简单示例。这不是解决这个问题的尝试。

select 
    case
        when datediff(day, current_date, dataaction) <= 30 then '<= 30'
        when datediff(day, current_date, dataaction) <= 60 then '31 to 60'
        else '> 60' 
    end age_range
    , count(*) records
from 
    etc
group by 
    case
        when datediff(day, current_date, dataaction) <= 30 then '<= 30'
        when datediff(day, current_date, dataaction) <= 60 then '31 to 60'
        else '> 60' 
    end