Mysql 语句统计当天每周每月的sql 语句

梦里梦外; 2023-10-05 15:12 14阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70]  
**表结构:**

**计算今天的数据 :**  
**示例 :** select count(order\_form.id) 合计 from order\_form where order\_form.commodity\_status=“1” AND TO\_DAYS(order\_form.buy\_date)=TO\_DAYS(NOW());

SELECT * FROM 表名 WHERE TO_DAYS(时间字段名)=TO_DAYS(NOW());
    SELECT * FROM 表名 WHERE TO_DAYS(时间字段名)=TO_DAYS(CURRENT_DATE());

**计算昨天的数据 :**  
**示例 :** select count(order\_form.id),order\_form.id 合计 from order\_form where order\_form.commodity\_status=“1” AND TO\_DAYS(NOW())=TO\_DAYS(order\_form.buy\_date)+1;

SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) = 1;
    SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) = TO_DAYS( 时间字段名) + 1;

**计算最近七天的数据 :**  
**示例 :** select count(order\_form.id) 合计 from order\_form where order\_form.commodity\_status=“1” AND DATE\_SUB(CURDATE(), INTERVAL 7 DAY) <= date(order\_form.buy\_date);

SELECT * FROM 表名 WHERE TO_DAYS(CURDATE()) - TO_DAYS(时间字段名)<=7;
    SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名);

**近来30天 :**

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

**本月 :**

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

**上个月 :**

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

**查询本季度数据**

SELECT * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());

**查询上季度数据**

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

**查询本年数据**

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

**查询上一年数据**

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

**查询当前这周数据**

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

**查询上周数据**

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

**查询上个月的数据**

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
    
    select * from user where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; 
    
    select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) 
    
    select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 
    
    select * from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 
    select * from user where pudate between  上月最后一天  and 下月第一天

**查询当前月份数据**

select name,submittime from enterprise   where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

**查询距离当前现在6个月的数据**

select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

[记录学习使用 bz地址][bz]

--------------------

查询当天记录:

select * from create_time where TO_DAYS(create_time) = TO_DAYS(NOW())
    注意:这里的create_time是数据库中的时间字段,会根据这个时间去和今天的时间对比获取数据.

查询昨天固定几天的记录:

select count(1) from kunyao_shop_order  where is_payed = 1 and TO_DAYS(NOW()) - TO_DAYS(create_time) = 1

查询当前这周的数据:

SELECT * FROM wx_fund_record  WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now());
    
    注意:这里的create_time是数据库中的时间字段,会根据这个时间去和今天的时间对比获取数据,除此之外,这个SQL语句获取的是美国的一周数据,中国和美国的周期不一样,美国的周日是中国的周一,所以如果要按照中国的周获取结果,需要推迟一天,SQL在下面:
    
    SELECT * FROM wx_fund_record  WHERE store_id=1 AND YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(now(),1);
    SELECT SUM(money) FROM wx_fund_record  WHERE store_id=?1 AND DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

查询本月的数据:

SELECT * FROM wx_fund_record  WHERE store_id=1 AND DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
    
    注意:这里的create_time是数据库中的时间字段,会根据这个时间去和今天的时间对比获取数据.

--------------------

查询数据库表中最近7天的记录

select count(*),date(create_time) as date from task where datediff(now(),create_time)<=6  group by day(create_time);

但是发现某一天没有数据，结果中没有显示当天(2017-08-28)的数据  
![在这里插入图片描述][2021042311022545.png]

思路一： 可以在自己的程序中做额外的补零处理

思路二： 构建一个最近七天的结果集，然后和查询的结果集合做left join(本文采用第二种方式)

select a.click_date,b.count
    from (
        SELECT curdate() as click_date
        union all
        SELECT date_sub(curdate(), interval 1 day) as click_date
        union all
        SELECT date_sub(curdate(), interval 2 day) as click_date
        union all
        SELECT date_sub(curdate(), interval 3 day) as click_date
        union all
        SELECT date_sub(curdate(), interval 4 day) as click_date
        union all
        SELECT date_sub(curdate(), interval 5 day) as click_date
        union all
        SELECT date_sub(curdate(), interval 6 day) as click_date
    ) a left join (
      select date(create_time) as datetime, count(*) as count
      from arms_task
      group by date(create_time)
    ) b on a.click_date = b.datetime;

SELECT a.click_date,IFNULL(b.count,0) AS COUNT
    FROM (
        SELECT CURDATE() AS click_date
        UNION ALL
        SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS click_date
        UNION ALL
        SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS click_date
        UNION ALL
        SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS click_date
        UNION ALL
        SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS click_date
        UNION ALL
        SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS click_date
        UNION ALL
        SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS click_date
    ) a LEFT JOIN (
      SELECT DATE(create_time) AS totalMoney, SUM(money+wallet) AS COUNT
      FROM kunyao_shop_member_fund_record WHERE TYPE = 2 
      GROUP BY DATE(create_time)
    ) b ON a.click_date = b.totalMoney;

[地址][Link 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201207142056557.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70
[bz]: https://www.cnblogs.com/zelzzz/p/7881650.html
[2021042311022545.png]: https://img-blog.csdnimg.cn/2021042311022545.png
[Link 1]: https://blog.csdn.net/ouyang111222/article/details/77638826