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

33qvvth1 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(247)

我在mariadb中有一个名为“incidents”的表，其中包含以下数据

+-----+----------+---------------------+
| 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”。它应该返回：

+-------+------------+
| 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)
+-------+------------+

sql mysql mariadb

来源：https://stackoverflow.com/questions/51892123/count-all-records-per-day-in-a-specific-month

3条答案

按热度按时间

cpjpxq1n1#

使用下面的代码来实现您的结果集。
声明两个变量：
var_from_date=每月第一天的日期。var_daydiff=月天数

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）回复(0）举报 2021-06-20

ghhkc1vu2#

下面的方法，您可以生成日期根据您的要求，我已经给 where gen_date between '2018-07-01' and '2018-12-31' 你必须用你的约会条件和地点 case when 以及 aggregate 功能

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）回复(0）举报 2021-06-20

ymzxtsji3#

尝试以下查询：

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

赞(0）回复(0）举报 2021-06-20

我来回答

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

3条答案

相关问题

热门标签

最新问答