首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >统计特定月份内每天的所有记录

问统计特定月份内每天的所有记录
EN

Stack Overflow用户
提问于 2018-08-17 17:16:06
回答 3查看 610关注 0票数 0

我在MariaDB中有一个名为'incidents‘的表,其中包含以下数据

代码语言:javascript
复制
+-----+----------+---------------------+
| ID  | status   |     created_at      |
+-----+----------+---------------------+
|  1  | open     | 2018-07-03 16:15:24 |
|  2  | open     | 2018-07-03 16:15:24 |
|  3  | open     | 2018-07-05 16:15:24 |
|  4  | open     | 2018-07-08 16:15:24 |
|  5  | closed   | 2018-07-15 16:15:24 |
+-----+----------+---------------------+

我想用一个参数来计算一个月内每天所有的“打开”状态,以选择要获取的月份和年份。

例如,我的月份值是'07‘,年份是'2018’。它应该返回:

代码语言:javascript
复制
+-------+------------+
| count |    date    |
+-------+------------+
|   0   | 2018-07-01 |
|   0   | 2018-07-02 |
|   2   | 2018-07-03 |
|   0   | 2018-07-04 |
|   1   | 2018-07-05 |
|   0   | 2018-07-06 |
|   0   | 2018-07-07 |
|   1   | 2018-07-08 |
and so on up to July 31(should also be dynamic depending on month and year)
+-------+------------+
EN

回答 3

Stack Overflow用户

发布于 2018-08-17 17:22:15

尝试下面的查询:

代码语言:javascript
复制
   select thedate,case when status is null then 0 else 1 end as count
     from (Select '2018-07-01' As [TheDate]
         Union All
         Select DateAdd(month, 1, TheDate) From dt Where [TheDate] < '2018-07-31') as dt left join tablename 
    on dt.thedate=tablename.date
票数 1
EN

Stack Overflow用户

发布于 2018-08-17 17:42:31

使用以下代码来实现您的结果集。

声明两个变量:

var_from_date =每月第一天的日期。var_daydiff =每月的天数

代码语言:javascript
复制
         WITH RECURSIVE cte_days (months, days, dates, n) AS
        (
          SELECT DATE(DATE_ADD(var_from_date, INTERVAL 0 DAY)) as dates,
                    0 as n
          UNION ALL
          SELECT DATE(DATE_ADD(var_from_date, INTERVAL n+1 DAY)) as dates, 
                    n + 1 as n 
          FROM cte_days WHERE n <= var_daydiff
        )
        SELECT ch.date, IFNULL(COUNT(t.id), 0) as count
        FROM cte_days ch
        LEFT JOIN table t on ch.dates = t.date
        GROUP BY ch.date;
票数 0
EN

Stack Overflow用户

发布于 2018-08-18 13:05:49

下面的方式,你可以产生日期根据你的要求,我已经给了where gen_date between '2018-07-01' and '2018-12-31',你必须使用你的日期条件的地方,并使用case when和aggregate函数

代码语言:javascript
复制
    select gen_date as date ,sum(case when status='open' then 1 else 0 end) as Cnt from

    (    select * from 
        (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
         (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
         (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
         (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
         (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
         (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
        where gen_date between '2018-07-01' and '2018-12-31'
    ) as T1 left join your_table t2 on T1.gen_date= date(t2.created_at)
group by gen_date
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/51892123

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档