在MySQL中是否有任何函数可以将日期的结果分组为分日

Question

SQL表架构：

Id    CaseID   CounterID  ActionID   CurrentStart             CreatedDate 
973   11       13         16         2017-12-11 09:28:11    2017-12-11 09:28:11            

我需要像下面这样的结果

CounterID  0-30(Range 1)    31-60(Range 2)      61-90 
16         22(Count)        20(Count)           18(Count)

诸若此类

​

​

Answer 1

如果您想要一个包含组的解决方案，最简单的方法是：

<!-- language: sql -->
select 
    Id,
    sum(case when CurrentStart - CreatedDate &lt;= 30 THEN 1 ELSE 0 END) INF30,
    sum(case when CurrentStart - CreatedDate BETWEEN 31 AND 60 THEN 1 ELSE 0 END) BET3060,
    sum(case when CurrentStart - CreatedDate &gt; 60 THEN 1 ELSE 0 END) SUP60
from tablename where Id is not null group by Id;

下面是我建议的另一种帮助您构建此类查询的表单：

• 在子请求中，您将获得Id和三个列，以了解日期是否在此范围内:1如果日期在范围内，则为0(子请求在此处称为INNER_TABLE )。
• 根据Id对此子请求的结果进行分组，并接受范围的列之和。

select
        INNER_TABLE.Id,
        sum(INNER_TABLE.INF30) INF30,
        sum(INNER_TABLE.BET3060) BET3060,
        sum(INNER_TABLE.SUP60) SUP60
    from (select
            Id,
               case when CreatedDate - CurrentStart <= 30 THEN 1 ELSE 0 END INF30,
               case when CreatedDate - CurrentStart BETWEEN 31 AND 60 THEN 1 ELSE 0 END BET3060,
               case when CreatedDate - CurrentStart > 60 THEN 1 ELSE 0 END SUP60
        from tablename where Id is not null) INNER_TABLE
    group by INNER_TABLE.Id;

否则，另一个简单的解决方案是：

• 使用要按CounterId对其进行分组的不同(select distinct Id from tablename where id is not null) as ids列表
• 在选择的每一部分中使用子请求进行计数：(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate < 30)。
• 对所选的三个字段应用相同的

select
    ids.Id,
    (select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate <= 30) INF_30,
    (select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate > 30 and CurrentStart - CreatedDate <= 60) BET_30_60,
    (select count(*) from tablename where Id= ids.Id and CurrentStart - CreatedDate > 60) SUP_60
from (select distinct Id from tablename where Id is not null) ids;

当然，tablename是表的名称:)