mysql统计详解

「这是我参与11月更文挑战的第25天，活动详情查看：2021最后一次更文挑战」。

统计mysql 统计当天,本周,本月,上一月的数据

1 今天

select * from ht_invoice_information where year(create_date)=year(date_sub(now(),interval 1 year));
select * from 表名 where to_days(时间字段名) = to_days(now());

2 昨天

select * from 表名 where to_days( now( ) ) - to_days( 时间字段名) <= 1

3 近7天

select * from 表名 where date_sub(curdate(), interval 7 day) <= date(时间字段名)

4 近30天

select * from 表名 where date_sub(curdate(), interval 30 day) <= date(时间字段名)

5 本月

select * from 表名 where date_format( 时间字段名, '%y%m' ) = date_format( curdate( ) , '%y%m' )

6 上一个月

select * from 表名 where period_diff( date_format( now( ) , '%y%m' ) , date_format( 时间字段名, '%y%m' ) ) =1

7 本季度

select * from ht_invoice_information where QUARTER(create_date)=QUARTER(now());

8 上季度

select * from ht_invoice_information where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

9 本年数据

select * from ht_invoice_information where YEAR(create_date)=YEAR(NOW());

10 上年数据

select * from ht_invoice_information where year(create_date)=year(date_sub(now(),interval 1 year));

11 当前这周的数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

12 查询上周

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

13 检验学习成果

有些统计页面需要达到如下效果（按照月份分组计算数据）

14 函数讲解

1、DATE_ADD() 函数向日期添加指定的时间间隔。

语法：
DATE_ADD(date,INTERVAL expr unit)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。unit是指定的时间间隔类型单位。

unit参数可以是下列值：
unit值

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

举例如下：

2、DATE_SUB() 函数从日期减去指定的时间间隔。
语法：
DATE_SUB(date,INTERVAL expr unit)
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。unit是指定的时间间隔类型单位。

所用的unit单位和上面的DATE_ADD()完全一致

3、DAYOFYEAR(date)
date 参数是合法的日期表达式
返回date在一年中的日数, 在1到366范围内。

15 执行结果

 SELECT
        left(temp.date,7) as dateAbscissa,
        IFNULL(u.unmber,0) as thisYearCost
  from(
        SELECT date_add(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), interval numlist.id month) AS 'date' FROM
        (
        SELECT * from
        (SELECT i AS id FROM num ) a
        where a.id <=11
        ) AS numlist
        WHERE adddate(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), interval numlist.id month)<=
        concat(YEAR(now()),'-12-31')) temp
        LEFT JOIN
        (
        SELECT
        left(c.statistics_date,7) AS udate,
        SUM(c.operation_number) as unmber
        FROM
        t_hospitalization_operation c
        WHERE
        1 = 1
        
        GROUP BY left(c.statistics_date,7)
        ) u on left(temp.date,7) = u.udate ORDER BY temp.date

结果如下 四、分步解释sql

前提：需要一张中间表num，数据如下 然后LEFT JOIN 本身的数据表，既可达到效果,

# 按照天数统计
 SELECT
        temp.date as dateAbscissa,
        IFNULL(u.singleDiseasesNum,0) as singleDiseasesNum,
        IFNULL(u.multidisciplinaryNum,0) as multidisciplinaryNum
  from(   SELECT DATE_ADD((date_add(date_add(last_day(now()),interval 1 day),interval -1 month)),INTERVAL  numlist.id  DAY)AS 'date' FROM
        (
        SELECT * from
        (SELECT i AS id FROM num ) a
        where a.id <=30 
        ) AS numlist
      
) temp
        LEFT JOIN
        (
        SELECT
        DATE(c.create_time) AS udate,
        COUNT(if( c.audit_opinion='3' ,1,NULL) )as singleDiseasesNum,  
				COUNT(if( c.audit_opinion='4' ,1,NULL) )as multidisciplinaryNum 
        FROM
        t_mdt_apply_list c
        WHERE
        1 = 1
        
        GROUP BY DATE(c.create_time)
        ) u on DATE(temp.date) = u.udate ORDER BY temp.date