数据库常用日期统计查询

一、概述

二、日期查询

2.1 MySql

1. 统计今年每一个月数据

select res.day,ifnull(warn.count+warn.pcount,0) as count,ifnull(warn.pcount,0)as pcount,ifnull(warn.fcount,0)as fcount,
       ifnull(warn.handled,0)as handled
        from (
            SELECT concat(year(curdate()),'-12') as day
            union all
            SELECT concat(year(curdate()),'-11') as day
            union all
            SELECT concat(year(curdate()),'-10') as day
            union all
            SELECT concat(year(curdate()),'-09') as day
            union all
            SELECT concat(year(curdate()),'-08') as day
            union all
            SELECT concat(year(curdate()),'-07') as day
            union all
            SELECT concat(year(curdate()),'-06') as day
            union all
            SELECT concat(year(curdate()),'-05') as day
            union all
            SELECT concat(year(curdate()),'-04') as day
            union all
            SELECT concat(year(curdate()),'-03') as day
            union all
            SELECT concat(year(curdate()),'-02') as day
            union all
            SELECT concat(year(curdate()),'-01') as day
        ) res
        left join (
          select DATE_FORMAT(create_time, '%Y-%m') as datetime, count(event_level=1 or null) as pcount,count(event_level=2 or null) as count
          , count(event_level=3 or null) as fcount,count(event_state='1' or null) as handled
          from t_run_equ_event
          where  dept_id='101'   and YEAR(create_time)=YEAR(NOW())
          group by datetime
        ) warn on res.day = warn.datetime
        order by res.day asc
		

其中create_time格式为：yyyy-mm-dd hh:mm:ss。

1.2 最近7天

 select res.day,ifnull(warn.count+warn.pcount,0) as count,ifnull(warn.pcount,0)as pcount,ifnull(warn.fcount,0)as fcount,
        ifnull(warn.handled,0)as handled
        from (
            SELECT curdate() as day
            union all
            SELECT date_sub(curdate(), interval 1 day) as day
            union all
            SELECT date_sub(curdate(), interval 2 day) as day
            union all
            SELECT date_sub(curdate(), interval 3 day) as day
            union all
            SELECT date_sub(curdate(), interval 4 day) as day
            union all
            SELECT date_sub(curdate(), interval 5 day) as day
            union all
            SELECT date_sub(curdate(), interval 6 day) as day
        ) res
        left join (
          select date(event_time) as datetime, count(event_level=1 or null) as pcount,count(event_level=2 or null) as count
          ,count(event_level=3 or null) as fcount, count(event_state='1' or null) as handled
          from t_run_equ_event
          where  dept_id='101'   and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(event_time)
          group by date(event_time)
        ) warn on res.day = warn.datetime
        order by res.day asc

1.3 最近30天告警数和整改数+去年同期数

select curYear.warns,curYear.handled,lYear.lwarns,lYear.lhandled 
from(
  select ifnull(warn.warns,0)warns,ifnull(handle.handled,0)handled from (select count(*) warns,dept_id
        from t_run_equ_event e
        where e.dept_id=101 and  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(create_time)
)warn

        LEFT JOIN(
       SELECT count(*)handled,dept_id from t_run_work_order 
        where dept_id=101  and work_state='1' and  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(create_time)
            )handle on warn.dept_id=handle.dept_id
 ) curYear
LEFT JOIN (
 select ifnull(warn.warns,0)lwarns,ifnull(handle.handled,0)lhandled from (select count(*) warns,dept_id
        from t_run_equ_event e
        where e.dept_id=101 and  DATE_SUB(CURDATE(), INTERVAL 1 YEAR) >= DATE(create_time)>=DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 YEAR), INTERVAL 30 day)
	)warn

        LEFT JOIN(
       SELECT count(*)handled,dept_id from t_run_work_order 
        where dept_id=101  and work_state='1' and  DATE_SUB(CURDATE(), INTERVAL 1 YEAR) >= DATE(create_time)>=DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 YEAR), INTERVAL 30 day)
            )handle on warn.dept_id=handle.dept_id

)lYear on 1=1

1.4 今年告警数和整改数+去年同期数

select curYear.warns,curYear.handled,lYear.lwarns,lYear.lhandled
        from(
          select ifnull(warn.warns,0)warns,ifnull(handle.handled,0)handled from (select count(*) warns,dept_id
                from t_run_equ_event e
                where e.dept_id=#{deptId} and e.event_level='2' and   YEAR(create_time)=YEAR(NOW())
        )warn
          LEFT JOIN(
               SELECT count(*)handled,dept_id from t_run_work_order
                where dept_id=#{deptId}  and work_state='1' and  YEAR(create_time)=YEAR(NOW())
                    )handle on warn.dept_id=handle.dept_id
         ) curYear
        LEFT JOIN (
         select ifnull(warn.warns,0)lwarns,ifnull(handle.handled,0)lhandled from (select count(*) warns,dept_id
                from t_run_equ_event e
                where e.dept_id=#{deptId} and  YEAR(create_time)=YEAR(date_sub(now(),interval 1 year))
            )warn

                LEFT JOIN(
               SELECT count(*)handled,dept_id from t_run_work_order
                where dept_id=#{deptId}  and work_state='1' and  YEAR(create_time)=YEAR(date_sub(now(),interval 1 year))
                    )handle on warn.dept_id=handle.dept_id

        )lYear on 1=1

2.2 SqlServer

2.1 本月每一天

   select res.day as cal_date, isnull(warn.total_num,0)total_num, isnull(warn.fire_alarm_num, 0)fire_alarm_num,
 isnull(warn.error_num,0)error_num, isnull(warn.leave_post_count,0)leave_post_count, isnull(warn.other_num,0)other_num
        from (    
SELECT  (number + 1)as day FROM master..spt_values
	WHERE type = 'p' AND DATEADD(DAY,number,CAST (CONVERT (VARCHAR(7), getdate(), 120) + '-01' AS datetime)) < DATEADD(MONTH,1,CAST (CONVERT (VARCHAR(7), getdate(), 120) + '-01' AS datetime))
   ) res
        left join (
 select convert(varchar,datepart(day, cal_date))cal_date ,sum(total_num) total_num,sum(t.fire_alarm_num) fire_alarm_num, sum(t.error_num) error_num,sum(t.leave_post_count) leave_post_count, sum(t.total_num-t.fire_alarm_num-t.error_num-t.leave_post_count) other_num from T_RMCF_DAY_STATISTICS t inner join T_RMCF_SYS_DWXX dw on dw.dwid=t.dwid and dw.del_flag='0' where t.cal_date like '202205%' 
 group by cal_date 
        ) warn on res.day = warn.cal_date
        order by res.day asc

2.2 最近一年每个月统计

 select res.month as sta_month, isnull(warn.total_num,0)total_num, isnull(warn.fire_alarm_num, 0)fire_alarm_num,
 isnull(warn.error_num,0)error_num, isnull(warn.leave_post_count,0)leave_post_count, isnull(warn.other_num,0)other_num
        from (
            SELECT 1 as month
            union all SELECT 2  as month
            union all SELECT 3  as month
            union all  SELECT 4  as month
            union all  SELECT 5  as month
            union all SELECT 6  as month
            union all SELECT 7  as month
						union all SELECT 8  as month
						union all SELECT 9  as month
						union all SELECT 10  as month
						union all SELECT 11  as month
						union all SELECT 12  as month
        ) res
        left join (
 select sta_month,sum(total_num) total_num,sum(t.fire_alarm_num) fire_alarm_num, sum(t.error_num) error_num,sum(t.leave_post_count) leave_post_count,sum(t.total_num-t.fire_alarm_num-t.error_num-t.leave_post_count) other_num from T_RMCF_MONTH_STATISTICS t inner join T_RMCF_SYS_DWXX dw on dw.dwid=t.dwid and dw.del_flag='0' where t.sta_year='2022' group by sta_month
        ) warn on res.month = warn.sta_month
        order by res.month asc
				 

2.3 最近7天数据统计

 select res.day as cal_date, warn.total_num, warn.fire_alarm_num, warn.error_num, warn.leave_post_count, warn.other_num
        from (
            SELECT convert(varchar(10), getdate(), 112) as day
            union all
            SELECT convert(varchar(10), dateadd(dd,-1,getdate()), 112)  as day
            union all
                       SELECT convert(varchar(10), dateadd(dd,-2,getdate()), 112)  as day

            union all
                        SELECT convert(varchar(10), dateadd(dd,-3,getdate()), 112)  as day

            union all
                       SELECT convert(varchar(10), dateadd(dd,-4,getdate()), 112)  as day

            union all
                       SELECT convert(varchar(10), dateadd(dd,-5,getdate()), 112)  as day

            union all
                        SELECT convert(varchar(10), dateadd(dd,-6,getdate()), 112)  as day

        ) res
        left join (
          select cal_date, sum(total_num) total_num,sum(t.fire_alarm_num) fire_alarm_num, sum(t.error_num) error_num,sum(t.leave_post_count) leave_post_count, 
                    sum(t.total_num-t.fire_alarm_num-t.error_num-t.leave_post_count) other_num
                     from T_RMCF_DAY_STATISTICS t 
                     inner join T_RMCF_SYS_DWXX dw on dw.dwid=t.dwid and dw.del_flag='0' 
					  where t.cal_date>='2022-05-06'  
          group by cal_date
        ) warn on res.day = warn.cal_date
        order by res.day asc
				
				 
 

三、两者区别

方法类型，实现稍有区别，主要上数据库自带日期函数不同。