MySQL + mybatis 查询业务月统计 包括 同比 环比

最新推荐文章于 2024-09-09 08:37:20 发布
最新推荐文章于 2024-09-09 08:37:20 发布
阅读量8.3k 收藏 4
点赞数 2
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xiao_maoqiu/article/details/72876239
版权

我要做的是，做一个月统计页面，如下：


如图可看出，就是每月的总交易量的一个统计，再计算出同比，环比。

在网上看了很多，都说用left join，刚开始也是学着用。现在总结的话，就

要想清楚，为什么用left join ？  打个比方，如果客户选择2017-04  至 2017-07 那么有可能这段时间内是没数据的，那也要显示出来，就是0嘛。所以这个时间段内每月不一定有数据的情况下，就要用left join了，因为月份是肯定存在的，只是数据有没有就不知道了。这里要建一个日期表。创建方式如下:

    CREATE TABLE bdt_date (  
      RepDate date NOT NULL,                 
      RepYear int(8) DEFAULT NULL,        
      RepMonth int(8) DEFAULT NULL,     
      RepDay int(8) DEFAULT NULL,       
      RepWeek int(8) DEFAULT NULL,      
      PRIMARY KEY (RepDate)  
    );  

    CREATE PROCEDURE getAllDate(num int)  
    BEGIN  
	DECLARE count INT DEFAULT 0;  
	DECLARE startDay DATE DEFAULT DATE(NOW());  
	DECLARE endDay DATE DEFAULT DATE(NOW());  
	WHILE count<num DO  
	    INSERT INTO bdt_date(RepDate,RepYear,RepMonth,RepDay,RepWeek,RepYearMonth) VALUES (startDay,YEAR(startDay),MONTH(startDay),DAY(startDay),WEEKOFYEAR(startDay),);  
	    SET count=count+1;  
	    SET startDay=DATE_ADD(DATE(NOW()),INTERVAL count DAY);  
	    SET endDay=DATE_SUB(DATE(NOW()),INTERVAL count DAY);  
	    INSERT INTO bdt_date(RepDate,RepYear,RepMonth,RepDay,RepWeek,RepYearMonth) VALUES (endDay,YEAR(endDay),MONTH(endDay),DAY(endDay),WEEKOFYEAR(endDay));  
	END WHILE;  
    END
那sql我也放出来，如果能帮到大家就很开心了。极力欢迎大神指教，优化sql，也确实有点长。 

select 
	DISTINCT DATE_FORMAT(date.RepDate,'%Y-%m') as yearMonth,
			CASE WHEN t1.totalTransaction IS NULL OR t1.totalTransaction = 0
				THEN 0
			ELSE t1.totalTransaction END as totalTransaction,
			CASE WHEN t1.totalVolumes IS NULL OR t1.totalVolumes = 0
				THEN 0
			ELSE t1.totalVolumes END as totalVolumes,
			CASE WHEN t1.totalTransactionMoney IS NULL OR t1.totalTransactionMoney = 0
				THEN 0
			ELSE t1.totalTransactionMoney END as totalTransactionMoney,
			CASE WHEN t1.totalVolumesMoney IS NULL OR t1.totalVolumesMoney = 0
				THEN 0
			ELSE t1.totalVolumesMoney END as totalVolumesMoney,
			CASE WHEN t1.increasePrice IS NULL OR t1.increasePrice = 0
				THEN 0
			ELSE t1.increasePrice END as increasePrice,
			CASE WHEN t1.totalAbortive IS NULL OR t1.totalAbortive = 0
				THEN 0
			ELSE t1.totalAbortive END as totalAbortive,
			CASE WHEN t2.totalTransaction = 0 OR t2.totalTransaction IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalTransaction-t2.totalTransaction) / t2.totalTransaction) * 100,2) , '%') END AS totalTransactionProportionT,
			CASE WHEN t3.totalTransaction = 0 OR t3.totalTransaction IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalTransaction-t3.totalTransaction) / t3.totalTransaction) * 100,2) , '%') END AS totalTransactionProportionH,
			
			CASE WHEN t2.totalVolumes = 0 OR t2.totalVolumes IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalVolumes-t2.totalVolumes) / t2.totalVolumes) * 100,2) , '%') END AS totalVolumesProportionT,
			CASE WHEN t3.totalVolumes = 0 OR t3.totalVolumes IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalVolumes-t3.totalVolumes) / t3.totalVolumes) * 100,2) , '%') END AS totalVolumesProportionH,
		
			CASE WHEN t2.totalTransactionMoney = 0 OR t2.totalTransactionMoney IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalTransactionMoney-t2.totalTransactionMoney) / t2.totalTransactionMoney) * 100,2), '%') END  AS totalTransactionMoneyProportionT,
			CASE WHEN t3.totalTransactionMoney = 0 OR t3.totalTransactionMoney IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalTransactionMoney-t3.totalTransactionMoney) / t3.totalTransactionMoney) * 100,2), '%') END  AS totalTransactionMoneyProportionH,
			
			CASE WHEN t2.totalVolumesMoney = 0 OR t2.totalVolumesMoney IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalVolumesMoney-t2.totalVolumesMoney) / t2.totalVolumesMoney) * 100,2), '%') END  AS totalVolumesMoneyProportionT,
			
			CASE WHEN t3.totalVolumesMoney = 0 OR t3.totalVolumesMoney IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalVolumesMoney-t3.totalVolumesMoney) / t3.totalVolumesMoney) * 100,2), '%') END  AS totalVolumesMoneyProportionH,
			
			CASE WHEN t1.totalVolumes IS NULL OR t1.totalTransaction IS NULL OR t1.totalTransaction = 0
				THEN '---'
			ELSE CONCAT(round((t1.totalVolumes / t1.totalTransaction) *100,2), '%' ) END  AS volumesPercent
	from bdt_date date
	left join 
		(select 
			DATE_FORMAT(price.end_time,'%Y-%m') as t1_yearMonth,
	  		sum(item.TRADING_WEIGHT) as totalTransaction,
	  		sum(case when price.state = "6" then item.TRADING_WEIGHT else 0 end) as totalVolumes,
	  		sum(case when batch.OFFER_TYPE = "2" then price.STARTING_PIRCE else (price.STARTING_PIRCE * item.TRADING_WEIGHT) end) as totalTransactionMoney,
	  		sum(case when price.state = "6" then 
	  				case batch.OFFER_TYPE when "2" then price.STARTING_PIRCE
	  									  when "1" then (price.STARTING_PIRCE * item.TRADING_WEIGHT) end
	  			else 0 end) as totalVolumesMoney,
	  		sum(case when price.state = "6" then 
	  				case  batch.OFFER_TYPE when "2" then (price.CURRENT_PRICE - price.STARTING_PIRCE) 
	  									  when "1" then (item.TRADING_WEIGHT * (price.CURRENT_PRICE - price.STARTING_PIRCE)) end 
	  			else 0 end) as increasePrice,
	  		sum(case when price.state = "7" then item.TRADING_WEIGHT else 0 end) as totalAbortive
	  		from bdt_resources_item item,bdt_price_info price,bdt_batch batch
	  		where 
	  			 batch.id = price.batch_id and
	  			 item.id = price.item_id and 
	  			 price.state in ("6","7")
			group by DATE_FORMAT(price.end_time,'%Y-%m')) as t1 
	on DATE_FORMAT(date.RepDate,'%Y-%m') = t1.t1_yearMonth
	left join 
		(select 
			DATE_FORMAT(DATE_ADD(price2.end_time,INTERVAL 1 YEAR),'%Y-%m') as yearMonth,
		  	sum(item2.TRADING_WEIGHT) as totalTransaction,
		  	sum(case when price2.state = "6" then item2.TRADING_WEIGHT else 0 end) as totalVolumes,
		  	sum(case when batch2.OFFER_TYPE = "2" then price2.STARTING_PIRCE else (price2.STARTING_PIRCE * item2.TRADING_WEIGHT) end) as totalTransactionMoney,
		  	sum(case when price2.state = "6" then 
		  			case batch2.OFFER_TYPE when "2" then price2.STARTING_PIRCE
		  								  when "1" then (price2.STARTING_PIRCE * item2.TRADING_WEIGHT) end
		  		else 0 end) as totalVolumesMoney
		  	from bdt_resources_item item2,bdt_price_info price2,bdt_batch batch2
			where 
		  		 batch2.id = price2.batch_id and
		  		 item2.id = price2.item_id and 
		  		 price2.state in ("6","7")
			group by DATE_FORMAT(price2.end_time,'%Y-%m')) as t2
	on  DATE_FORMAT(date.RepDate,'%Y-%m')= t2.yearMonth
	left join 
		(select 
			DATE_FORMAT(DATE_ADD(price3.end_time,INTERVAL 1 MONTH),'%Y-%m') as yearMonth,
		  	sum(item3.TRADING_WEIGHT) as totalTransaction,
		  	sum(case when price3.state = "6" then item3.TRADING_WEIGHT else 0 end) as totalVolumes,
		  	sum(case when batch3.OFFER_TYPE = "2" then price3.STARTING_PIRCE else (price3.STARTING_PIRCE * item3.TRADING_WEIGHT) end) as totalTransactionMoney,
		  	sum(case when price3.state = "6" then 
		  			case batch3.OFFER_TYPE when "2" then price3.STARTING_PIRCE
		  								  when "1" then (price3.STARTING_PIRCE * item3.TRADING_WEIGHT) end
		  		else 0 end) as totalVolumesMoney
		  	from bdt_resources_item item3,bdt_price_info price3,bdt_batch batch3
		  	where 
		  		 batch3.id = price3.batch_id and
		  		 item3.id = price3.item_id and 
		  		 price3.state in ("6","7")
			group by DATE_FORMAT(price3.end_time,'%Y-%m')) as t3
	on DATE_FORMAT(date.RepDate,'%Y-%m')= t3.yearMonth
	where
		date.RepDate >=#{beginDate} and
  		date.RepDate <=#{endDate}

在这过程中，也熟练掌握了 CASE... WHEN ... ELSE ... END 用法，很开心。


还有一个呢，跟这个类似，多了一个分组条件，就是每月每个客户的统计



sql如下

select 
	DISTINCT DATE_FORMAT(date.RepDate,'%Y-%m') as yearMonth,
			t1.member_name as memberName,
			CASE WHEN t1.totalTransaction IS NULL OR t1.totalTransaction = 0
				THEN 0
			ELSE t1.totalTransaction END as totalTransaction,
			CASE WHEN t1.totalVolumes IS NULL OR t1.totalVolumes = 0
				THEN 0
			ELSE t1.totalVolumes END as totalVolumes,
			CASE WHEN t1.totalTransactionMoney IS NULL OR t1.totalTransactionMoney = 0
				THEN 0
			ELSE t1.totalTransactionMoney END as totalTransactionMoney,
			CASE WHEN t1.totalVolumesMoney IS NULL OR t1.totalVolumesMoney = 0
				THEN 0
			ELSE t1.totalVolumesMoney END as totalVolumesMoney,
			CASE WHEN t1.increasePrice IS NULL OR t1.increasePrice = 0
				THEN 0
			ELSE t1.increasePrice END as increasePrice,
			CASE WHEN t1.totalAbortive IS NULL OR t1.totalAbortive = 0
				THEN 0
			ELSE t1.totalAbortive END as totalAbortive,
			CASE WHEN t2.totalTransaction = 0 OR t2.totalTransaction IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalTransaction-t2.totalTransaction) / t2.totalTransaction) * 100,2) , '%') END AS totalTransactionProportionT,
			CASE WHEN t3.totalTransaction = 0 OR t3.totalTransaction IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalTransaction-t3.totalTransaction) / t3.totalTransaction) * 100,2) , '%') END AS totalTransactionProportionH,
			
			CASE WHEN t2.totalVolumes = 0 OR t2.totalVolumes IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalVolumes-t2.totalVolumes) / t2.totalVolumes) * 100,2) , '%') END AS totalVolumesProportionT,
			CASE WHEN t3.totalVolumes = 0 OR t3.totalVolumes IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalVolumes-t3.totalVolumes) / t3.totalVolumes) * 100,2) , '%') END AS totalVolumesProportionH,
		
			CASE WHEN t2.totalTransactionMoney = 0 OR t2.totalTransactionMoney IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalTransactionMoney-t2.totalTransactionMoney) / t2.totalTransactionMoney) * 100,2), '%') END  AS totalTransactionMoneyProportionT,
			CASE WHEN t3.totalTransactionMoney = 0 OR t3.totalTransactionMoney IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalTransactionMoney-t3.totalTransactionMoney) / t3.totalTransactionMoney) * 100,2), '%') END  AS totalTransactionMoneyProportionH,
			
			CASE WHEN t2.totalVolumesMoney = 0 OR t2.totalVolumesMoney IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalVolumesMoney-t2.totalVolumesMoney) / t2.totalVolumesMoney) * 100,2), '%') END  AS totalVolumesMoneyProportionT,
			
			CASE WHEN t3.totalVolumesMoney = 0 OR t3.totalVolumesMoney IS NULL
				THEN '---'
			ELSE CONCAT(round(((t1.totalVolumesMoney-t3.totalVolumesMoney) / t3.totalVolumesMoney) * 100,2), '%') END  AS totalVolumesMoneyProportionH,
			
			CASE WHEN t1.totalVolumes IS NULL OR t1.totalTransaction IS NULL OR t1.totalTransaction = 0
				THEN '---'
			ELSE CONCAT(round((t1.totalVolumes / t1.totalTransaction) *100,2), '%' ) END  AS volumesPercent
	from bdt_date date
	left join 
		(select 
			DATE_FORMAT(price.end_time,'%Y-%m') as t1_yearMonth,
			batch.member_code,
			batch.member_name,
	  		sum(item.TRADING_WEIGHT) as totalTransaction,
	  		sum(case when price.state = "6" then item.TRADING_WEIGHT else 0 end) as totalVolumes,
	  		sum(case when batch.OFFER_TYPE = "2" then price.STARTING_PIRCE else (price.STARTING_PIRCE * item.TRADING_WEIGHT) end) as totalTransactionMoney,
	  		sum(case when price.state = "6" then 
	  				case batch.OFFER_TYPE when "2" then price.STARTING_PIRCE
	  									  when "1" then (price.STARTING_PIRCE * item.TRADING_WEIGHT) end
	  			else 0 end) as totalVolumesMoney,
	  		sum(case when price.state = "6" then 
	  				case  batch.OFFER_TYPE when "2" then (price.CURRENT_PRICE - price.STARTING_PIRCE) 
	  									  when "1" then (item.TRADING_WEIGHT * (price.CURRENT_PRICE - price.STARTING_PIRCE)) end 
	  			else 0 end) as increasePrice,
	  		sum(case when price.state = "7" then item.TRADING_WEIGHT else 0 end) as totalAbortive
	  		from bdt_resources_item item,bdt_price_info price,bdt_batch batch
	  		where 
	  			 batch.id = price.batch_id and
	  			 item.id = price.item_id and 
	  			 price.state in ("6","7")
			group by DATE_FORMAT(price.end_time,'%Y-%m'),batch.member_code) as t1 
	on DATE_FORMAT(date.RepDate,'%Y-%m') = t1.t1_yearMonth
	left join 
		(select 
			DATE_FORMAT(DATE_ADD(price2.end_time,INTERVAL 1 YEAR),'%Y-%m') as yearMonth,
			batch2.member_code,
		  	sum(item2.TRADING_WEIGHT) as totalTransaction,
		  	sum(case when price2.state = "6" then item2.TRADING_WEIGHT else 0 end) as totalVolumes,
		  	sum(case when batch2.OFFER_TYPE = "2" then price2.STARTING_PIRCE else (price2.STARTING_PIRCE * item2.TRADING_WEIGHT) end) as totalTransactionMoney,
		  	sum(case when price2.state = "6" then 
		  			case batch2.OFFER_TYPE when "2" then price2.STARTING_PIRCE
		  								  when "1" then (price2.STARTING_PIRCE * item2.TRADING_WEIGHT) end
		  		else 0 end) as totalVolumesMoney
		  	from bdt_resources_item item2,bdt_price_info price2,bdt_batch batch2
			where 
		  		 batch2.id = price2.batch_id and
		  		 item2.id = price2.item_id and 
		  		 price2.state in ("6","7")
			group by DATE_FORMAT(price2.end_time,'%Y-%m'),batch2.member_code) as t2
	on  DATE_FORMAT(date.RepDate,'%Y-%m')= t2.yearMonth
	left join 
		(select 
			DATE_FORMAT(DATE_ADD(price3.end_time,INTERVAL 1 MONTH),'%Y-%m') as yearMonth,
			batch3.member_code,
		  	sum(item3.TRADING_WEIGHT) as totalTransaction,
		  	sum(case when price3.state = "6" then item3.TRADING_WEIGHT else 0 end) as totalVolumes,
		  	sum(case when batch3.OFFER_TYPE = "2" then price3.STARTING_PIRCE else (price3.STARTING_PIRCE * item3.TRADING_WEIGHT) end) as totalTransactionMoney,
		  	sum(case when price3.state = "6" then 
		  			case batch3.OFFER_TYPE when "2" then price3.STARTING_PIRCE
		  								  when "1" then (price3.STARTING_PIRCE * item3.TRADING_WEIGHT) end
		  		else 0 end) as totalVolumesMoney
		  	from bdt_resources_item item3,bdt_price_info price3,bdt_batch batch3
		  	where 
		  		 batch3.id = price3.batch_id and
		  		 item3.id = price3.item_id and 
		  		 price3.state in ("6","7")
			group by DATE_FORMAT(price3.end_time,'%Y-%m'),batch3.member_code) as t3
	on DATE_FORMAT(date.RepDate,'%Y-%m')= t3.yearMonth
	where
		date.RepDate >=#{beginDate} and
  		date.RepDate <=#{endDate} and 
		t1.member_code IS NOT NULL
		<if test="companycode != null and companycode != ''">
			and t1.member_code = #{companycode}
		</if>
	group by date.RepDate,t1.member_code





xiao_maoqiu
关注
同比环比日期计算、本周一、本周日、上周一、上周日（mysql）
u011097323的专栏
12-28 5861
在计算同比环比时需要用到本日、本月1日、上月本日、上月1日，年初 及去年对应的时间 select CURDATE() as cur_day, DATE_FORMAT(CURDATE(),'%Y-%m-01') as cur_month_first_day, DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY) as cur_year_first_day, DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 MONT.
mysql 计算同比、环比，查询结果拼接成列、行、
Xiaoweidumpb
11-28 1815
增长速度是反映经济社会某一领域发展变化情况的重要数据，而同比和环比是反映增长速度最基础、最核心的数据指标，也是国际上通用的指标。在统计中， 同比和环比通常是同比变化率和环比变化率的简称，用于表示某一事物在对比期内发展变化的方向和程度。一、同比基本概念同比是以上年同期为基期相比较，即本期某一时间段与上年某一时间段相比， 可以理解为今年第 n 月与去年第 n 月的比较。如，2019 年 12 月份与 2018 年 12 月份相比较，2019 年上半年与 2018 年上半年相比较就是同比。
Mysql数据统计
最新发布
qq_43038960的博客
09-09 339
Mysql数据库数据统计语句，包括总记录数、总容量、各库表数量等
使用mybatis-plus实现按月统计数据默认统计到当前月
qq_43599841的博客
07-12 2539
统计今年每月的用户访问量，默认展示到当前月，可根据日期筛选。
mybatis 连续日期统计
qq_30831015的博客
11-28 816
SELECT s.day ,ifnull(re.register,0) as register   , ifnull( pe.perfect,0) as perfect ,ifnull(  ce.certified,0) as certified  from (  select s.day from (SELECT @mycnt := -1 FROM DUAL) r ,   (select d
Mysql同环比计算详解
弹指天下
09-16 6573
同环比是数据分析中的一个重要指标，主要反应指标随时间变化的情况，在日常开发中会经常遇到。环比会突出显示数据的短期趋势，会受到季节等因素的影响。 同比更加侧重反映长期的大趋势，也就规避了季节的因素。同比增长率＝（本期数－上年同期数）／上年同期数×100％；环比增长率＝（本期数－上期数）／上期数×100％
SpringBoot2.7整合SpringSecurity+Jwt+Redis+MySQL+MyBatis完整项目代码
10-30
在本项目中，我们主要关注的是SpringBoot 2.7版本与多个技术的深度整合，包括Spring Security、JWT（JSON Web Token）、Redis缓存以及MySQL数据库，并利用MyBatis作为持久层框架。以下是对这些技术及其整合应用的...
社区系统源码：基于 SpringBoot + MyBatis + MySQL + Redis + Kafka
05-08
Java社区系统源码并提供详细的开发文档和配套教程，包含帖子、评论、私信、系统通知、点赞、关注、搜索、用户设置、数据统计等模块。 核心技术栈 后端： Spring Spring Boot 2.1.5 RELEASE Spring MVC ORM：MyBatis ...
springboot+mybatis+mysql+postman（适合新手）
07-29
一个简单的springboot+mybatis+mysql的CRUD项目，适合想快速上手springboot项目的同学进行研究，四个接口都经过本人测试通过，可放心下载，如有疑问，可随时联系本人，看到后第一时间帮忙处理
基于Springboot+Mybatis+Redis+MySql+MQ的校园医疗管理系统源码+数据库.zip
01-10
基于Springboot+Mybatis+Redis+MySql+MQ的校园医疗管理系统源码+数据库.zip 基于Springboot+Mybatis+Redis+MySql+MQ的校园医疗管理系统源码+数据库.zip 基于Springboot+Mybatis+Redis+MySql+MQ的校园医疗管理系统...
基于SpringBoot+Mybatis Plus+Shiro+mysql+redis智慧云智能教育平台
08-23
基于 SpringBoot + Mybatis + Shiro + mysql + redis构建的智能云教育平台。架构上使用完全前后端分离。 系统概述： 1、支持多种题型：选择题、多选题、判断题、填空题、综合题以及数学公式。支持在线考试，教师...
mybatis-plus QueryWrapper条件子句多重与或
集北卡卡罗特的博客
08-03 2603
鄙人在项目中遇到一个业务要计算月同比和环比，需要查询一个时间字段中多个时间范围的记录，实现的SQL示例如下： select * from fault_happen where train_code = '710' and fault_level = '3' and (happen_time >= '2020-07-01 00:00:00' and happen_time < '2020-08-01 00:00:00' OR happen_time >= '2020-06-01 00:00
MyBatis动态SQL详解
热门推荐
SGLP521的博客
11-24 3万+
MyBatis动态SQL
Mybatis人数和次数的统计
非衣鲲化的博客
05-02 3152
1、mybatis 人数和次数统计sql &lt;select id="queryWebCladdifyOrderList" resultMap="BaseResultMap" parameterType="java.util.Map"&gt;    select cc.urlgrp_name,sum(count) intCount,count(*) intPeopleNum from     (...
MySQL统计同比环比SQL
Mo小泽的技术博客
03-05 1万+
大体思路：MySQL没有类似oracle方便的统计函数，只能靠自己去硬计算：通过时间字段直接增加年份、月份，然后通过left join关联时间字段去计算环比、同比公式即可原始表结构：求同比SQL：-- 按年同比 SELECT t5.*, CASE WHEN t5.last_energy_year IS NULL OR t5.last_energy_year =0 THEN 0.00 ...
MYSQL使用GROUP BY时想获取每组中时间最大的那组
码是一种态度
05-08 2729
SELECT * FROM (SELECT * FROM`history` where active =1and id= 485 ORDER BY gmt_modify desc LIMIT 1000)a GROUP BY a.check_task_id ORDER BY a.gmt_modify DESC; 1，首先将符合查询条件的数据根据自己所需要的字段排序...
MyBatis:统计数量
weixin_34273481的博客
09-19 2500
dao： /** * 统计商家的案例数量 * * @param shopId * @return */ long countByShopId(Long shopId); @Override public long countByShopId(Long shopId) { ret...
mysql同环比写法
10-17
MySQL同环比的写法可以通过使用子查询和自连接来实现。具体实现方法如下： 同比： ``` SELECT t1.date, t1.value, t2.value AS last_year_value, (t1.value - t2.value) / t2.value AS yoy FROM table_name t1 LEFT JOIN table_name t2 ON DATE_SUB(t1.date, INTERVAL 1 YEAR) = t2.date WHERE t1.date BETWEEN '2020-01-01' AND '2020-12-31' ``` 环比： ``` SELECT t1.date, t1.value, t2.value AS last_month_value, (t1.value - t2.value) / t2.value AS mom FROM table_name t1 LEFT JOIN table_name t2 ON DATE_SUB(t1.date, INTERVAL 1 MONTH) = t2.date WHERE t1.date BETWEEN '2020-01-01' AND '2020-12-31' ``` 其中，`table_name`为你的数据表名，`date`为日期列名，`value`为需要计算同环比的数值列名。
评论 5
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值