# 关于SQL求同比、环比

19 篇文章 0 订阅

• 同比

• 环比

## 同比和环比的计算公式

• 同比发展速度

• 同比增长速度

• 环比增长率

（本期数-上期数）/上期数×100%

• 同比增长率

## 同比和环比的区别

• 同比是本期与同期做对比，环比是本期与上期做对比。
• 环比一般是用在月、日很少用在年上，主要是对比很短时间内涨幅程度，不过由于行业差异，比如旅游，会受到淡旺季影响。
• 同比一般用在相邻两年，相同时间段内，查看涨幅程度，一般用在两年相同月份，很少用在两月相同日期。

## 建表

CREATE TABLE SalesDetail (
ID int,
DepartName VARCHAR(50),
Sales DECIMAL (10, 2),
SalesDate datetime
);

insert into SalesDetail(ID, DepartName, Sales, SalesDate)

select 1, '营销一部', 300, '2006-7-1'
UNION ALL select 2, '营销二部', 500, '2006-7-1'
UNION ALL select 3, '营销三部', 800, '2006-8-1'
UNION ALL select 4, '营销一部', 600, '2006-8-1'
UNION ALL select 5, '营销二部', 800, '2006-8-1'
UNION ALL select 6, '营销一部', 400, '2007-7-1'
UNION ALL select 7, '营销二部', 800, '2007-7-1'
UNION ALL select 8, '营销三部', 700, '2007-8-1'
UNION ALL select 9, '营销一部', 600, '2008-7-1'
UNION ALL select 10, '营销二部', 700, '2008-7-1'


## 答案

-- 得到环比

select c.DepartName as 部门, convert(varchar(10), c.SalesDate, 23) as 对比年月,
c.Sales as 本月销售总量, d.Sales as 上月销售总量,
case when d.Sales is null or d.Sales=0 then '无穷大' else cast(cast((isnull(c.Sales, 0)-isnull(d.Sales,0))*100/isnull(d.Sales, 0) as decimal(10,2)) as varchar(50))+'%' end
as 环比 from SalesDetail c left join
(select a.DepartName as DepartName, a.Sales as Sales, a.SalesDate as lsSalesDate, b.SalesDate as SalesDate from SalesDetail a join SalesDetail b on a.SalesDate=DateAdd(month, -1, b.SalesDate) and a.DepartName=b.DepartName) d

on c.SalesDate=d.SalesDate and c.DepartName=d.DepartName


-- 得到同比

select c.DepartName as 部门, convert(varchar(10), c.SalesDate, 23) as 对比年月,
c.Sales as 本月销售总量, d.Sales as 去年同期销售总量,
case when d.Sales is null or d.Sales=0 then '无穷大' else cast(cast((isnull(c.Sales, 0)-isnull(d.Sales,0))*100/isnull(d.Sales, 0) as decimal(10,2)) as varchar(50))+'%' end as 同比
from SalesDetail c left join
(select a.DepartName as DepartName, a.Sales as Sales, a.SalesDate as lsSalesDate, b.SalesDate as SalesDate from SalesDetail a join SalesDetail b on a.SalesDate=DateAdd(year, -1, b.SalesDate) and a.DepartName=b.DepartName) d

on c.SalesDate=d.SalesDate and c.DepartName=d.DepartName


-- SQL语句说明

//获取根据时间获取去年同期时间

//获取根据时间获取上月时间

//获取根据时间获取去年同期时间数据
select a.DepartName as DepartName, a.Sales as Sales, a.SalesDate as lsSalesDate, b.SalesDate as SalesDate
from SalesDetail a join SalesDetail b on a.SalesDate=DateAdd(year, -1, b.SalesDate)
and a.DepartName=b.DepartName

//获取根据时间获取上月时间数据
select a.DepartName as DepartName, a.Sales as Sales, a.SalesDate as lsSalesDate, b.SalesDate as SalesDate
from SalesDetail a join SalesDetail b on a.SalesDate=DateAdd(month, -1, b.SalesDate)
and a.DepartName=b.DepartName


09-27 8747
03-05 1万+

02-26 2万+
01-07 2万+
12-12 3718
11-08 6089
02-23 5689
03-20 2742

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

WGS.

¥2 ¥4 ¥6 ¥10 ¥20

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