使用mysql查询当天、近一周、近一个月及近一年的数据以及各种报表查询sql
创始人
2025-01-15 12:10:11
0

1.mysql查询当天的数据

1

select* fromtablewhereto_days(时间字段) = to_days(now());

2.mysql查询昨天的数据

1

select* fromtablewhereto_days(now( ) ) - to_days( 时间字段名) <= 1

3.mysql查询近一个月的数据

1

SELECT* FROMtableWHEREdate(时间字段) >= DATE_SUB(CURDATE(),INTERVAL 1 MONTH)//查询近一个月

SELECT* FROMtableWHEREdate(时间字段) >= DATE_SUB(CURDATE(),INTERVAL 1 YEAR) //查询近一年

SELECT* FROMtableWHEREdate(时间字段) >= DATE_SUB(CURDATE(),INTERVAL 7 DAY) //查询近七天

4.mysql查询本月的数据

1

select* fromtablewhereDATE_FORMAT(时间字段, '%Y%m') = DATE_FORMAT( CURDATE( ) , '%Y%m')

5.mysql查询上月的数据

1

select* fromtablewherePERIOD_DIFF(date_format(now(),'%Y%m') , date_format(时间字段,'%Y%m')) =1

6.mysql查询本年的数据

1

select* fromtablewhereYEAR(时间字段)=YEAR(now());

7.mysql查询上一年的数据

1

select* fromtablewhereyear(时间字段)=year(date_sub(now(),interval 1 year));

8.mysql查询本周数据(周一为第一天)

1

SELECT* FROMtableWHEREYEARWEEK(date_format(时间字段,'%Y-%m-%d'),1) = YEARWEEK(now(),1);

9.mysql查询近五分钟的数据

1

SELECT* FROMtableWHERE时间字段 >= DATE_SUB(now(),INTERVAL 5 MINUTE)

 10.mysql查询某年度的每个月数据报表

      select a.date AS 'xData',IFNULL(b.sum, 0) AS 'yData'         from  (         select DATE_FORMAT(adddate(DATE_SUB(CURDATE(), INTERVAL dayofyear(now()) - 1 DAY),         INTERVAL numlist.id - 1 month), '%m') as date         from (SELECT @xi := @xi + 1 as id         from (SELECT 1 UNION SELECT 2 UNION SELECT 3) xc1,         (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,         (SELECT @xi := 0) xc0) as numlist         ) a         left join         (         SELECT  IFNULL(SUM(income),0) sum, DATE_FORMAT(time, '%m') as date         FROM zq_cnz_hy_income_report ts         WHERE YEAR(time)=#{year}         GROUP BY date ORDER BY date         ) b         on a.date = b.date order by a.date

效果图:

  11.mysql查询近一年的每个月数据报表

        SELECT v.month AS 'xData',IFNULL(b.COUNT,0) AS 'yData' FROM (         SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `month`         UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month`         UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month`         UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month`         UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month`         UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month`         UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month`         UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month`         UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month`         UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month`         UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month`         UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `month`         ) v         LEFT JOIN         (         SELECT LEFT(a.time,7) AS 'month',SUM(income) AS COUNT         FROM zq_cnz_hy_income_report AS a         LEFT JOIN zq_cnz_hy_user_station zs ON a.station_id=zs.station_id         WHERE DATE_FORMAT(a.time,'%Y-%m')>DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH),'%Y-%m')         GROUP BY MONTH) AS b         ON v.month = b.month  GROUP BY v.month ORDER BY v.month

效果图:

 12.mysql查询本月数据报表

       select a.date as 'xData',IFNULL(b.sum, 0) AS 'yData'         from  (         select date from (         SELECT DATE_FORMAT(DATE_SUB(last_day(curdate()), INTERVAL xc-1 day), '%Y-%m-%d') as date         FROM (         SELECT @xi:=@xi+1 as xc from         (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1,         (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2,         (SELECT @xi:=0) xc0         ) xcxc) x0 where x0.date >= (select date_add(curdate(),interval-day(curdate())+1 day))         ) a         left join         (         select IFNULL(SUM(income),0) sum, DATE_FORMAT(time,'%Y-%m-%d') as date         FROM zq_cnz_hy_income_report ts         group by DATE_FORMAT(time, '%Y-%m-%d')         ) b         on a.date =b.date order by a.date

效果图:

13.mysql查询近一个月数据报表

SELECT         b.created AS 'xData',         IFNULL(c.sum, 0) AS 'yData'         FROM         (         SELECT         @cdate := date_add( @cdate, INTERVAL - 1 DAY ) created         FROM         ( SELECT @cdate := date_add( CURDATE( ), INTERVAL 1 DAY ) FROM zq_cnz_hy_income_report LIMIT 30 ) a         ) b         LEFT JOIN (         select SUM(income) as sum, DATE_FORMAT(time,'%Y-%m-%d') as date         FROM zq_cnz_hy_income_report ts         group by DATE_FORMAT(time, '%Y-%m-%d')) c         ON b.created = date_format( c.date, '%Y-%m-%d')         GROUP BY         b.created         ORDER BY         b.created;

效果图:

14.mysql查询近一周数据报表

 SELECT t1.datetime as 'xData', IFNULL(t2.num, 0) AS 'yData' FROM (         SELECT CURDATE() AS datetime         union all         SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS datetime         union all         SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS datetime         union all         SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS datetime         union all         SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS datetime         union all         SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS datetime         union all         SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS datetime         ) t1 LEFT JOIN (         select IFNULL(SUM(income),0) num, DATE_FORMAT(time, '%Y-%m-%d') as datetime         FROM zq_cnz_hy_income_report ts         GROUP BY LEFT(datetime, 10) ORDER BY datetime DESC LIMIT 7         ) t2 ON t1.datetime = t2.datetime ORDER BY t1.datetime asc

效果图:

相关内容

热门资讯

一分钟发现!丽水茶苑有外挂吗,... 一分钟发现!丽水茶苑有外挂吗,poker其实是真的有挂,总结教程(有挂详情)所有人都在同一条线上,像...
3分钟辅助挂!杭州都莱有挂吗,... 3分钟辅助挂!杭州都莱有挂吗,德州之星好像有挂,必备教程(有挂实操);亲,关键说明,杭州都莱有挂吗赛...
五分钟辅助挂!大宝苏北麻将挂,... 五分钟辅助挂!大宝苏北麻将挂,We辅poker助真是真的有挂,透明教程(有挂攻略);1、玩家可以在大...
3分钟发现!人人燕赵麻将有挂吗... 3分钟发现!人人燕赵麻将有挂吗,德扑助手本来真的有挂,爆料教程(有挂透明)1)人人燕赵麻将有挂吗辅助...
1分钟辅助挂!皮皮跑胡子输赢规... 1分钟辅助挂!皮皮跑胡子输赢规律,WEPOKE总是真的是有挂,2025版教程(有挂技巧)该软件可以轻...
7分钟实锤!雀神广东麻雀正规吗... 7分钟实锤!雀神广东麻雀正规吗,xpoker切实存在有挂,AI教程(有挂科普);1、首先打开雀神广东...
6分钟发现!胡乐麻将神器免费下... 6分钟发现!胡乐麻将神器免费下,wePoke其实真的是有挂,可靠技巧(有挂ai代打)1、构建自己的胡...
八分钟攻略!中至窝龙必赢攻略,... 八分钟攻略!中至窝龙必赢攻略,aaPOKER都是有挂,教你攻略(有挂详情);1)中至窝龙必赢攻略辅助...
四分钟攻略!中至吉安王炸输赢,... 四分钟攻略!中至吉安王炸输赢,鱼扑克app俱乐部真是真的有挂,规律教程(有挂详情)1、全新机制【中至...
2分钟了解!宁波竞技馆有挂吗,... 2分钟了解!宁波竞技馆有挂吗,wepOke总是存在有挂,揭秘教程(有挂ai代打);1、宁波竞技馆有挂...