SQL SERVER统计每个月往前推一年时间内的数据

悬赏园豆：160 [待解决问题]

第一个表字段分别为：
处理单号（PK）
时间（格式为YYYY-MM）
车架号
车型号
总费用
通过SQL SERVER处理得到
下表
时间(YYYY-MM)
车架号
车型号
总费用（这个总费用=YYYY-MM 月份-12到YYYY-MM之间单子的总费用求和
如这张表的一条记录为2019.5月，则从源数据表中选取时间位于2018.6月到2019.5
月之间的单子，对这些单子的费用求和）

即通过第一张表统计每个月往前推一年内的费用总计，并记录下来，形成一张新表

苏尚Lewis | 初学一级 | 园豆：42
提问于：2019-11-28 15:47

不用按车型什么的分类吗？

– wwr01 4年前

< >

所有回答(2)

select SUM('总费用') as tCost from table where riqi>='开始时间' and riqi<='结束时间'

你小子嚣张呀 | 园豆：630 (小虾三级) | 2019-11-28 16:59

IF OBJECT_ID('MyDateTable','U') IS NOT NULL
DROP TABLE MyDateTable

CREATE TABLE MyDateTable (
PKNo INT primary key identity(1,1),
CarType VARCHAR(20),
CurrMonth varCHAR(6),
TotalCost decimal(13, 2)
)
GO

INSERT INTO mydatetable VALUES ('3型号', '201712', 703.00);
INSERT INTO mydatetable VALUES ('2型号', '201801', 669.00);
INSERT INTO mydatetable VALUES ('4型号', '201802', 641.00);
INSERT INTO mydatetable VALUES ('3型号', '201803', 614.00);
INSERT INTO mydatetable VALUES ('3型号', '201804', 582.00);
INSERT INTO mydatetable VALUES ('2型号', '201805', 548.00);
INSERT INTO mydatetable VALUES ('2型号', '201806', 523.00);
INSERT INTO mydatetable VALUES ('1型号', '201807', 489.00);
INSERT INTO mydatetable VALUES ('0型号', '201808', 455.00);
INSERT INTO mydatetable VALUES ('0型号', '201809', 430.00);
INSERT INTO mydatetable VALUES ('4型号', '201810', 396.00);
INSERT INTO mydatetable VALUES ('4型号', '201811', 364.00);
INSERT INTO mydatetable VALUES ('3型号', '201812', 337.00);
INSERT INTO mydatetable VALUES ('2型号', '201901', 303.00);
INSERT INTO mydatetable VALUES ('4型号', '201902', 275.00);
INSERT INTO mydatetable VALUES ('3型号', '201903', 248.00);
INSERT INTO mydatetable VALUES ('3型号', '201904', 216.00);
INSERT INTO mydatetable VALUES ('2型号', '201905', 182.00);
INSERT INTO mydatetable VALUES ('2型号', '201906', 157.00);
INSERT INTO mydatetable VALUES ('1型号', '201907', 123.00);
INSERT INTO mydatetable VALUES ('0型号', '201908', 96.00);
INSERT INTO mydatetable VALUES ('0型号', '201909', 64.00);
INSERT INTO mydatetable VALUES ('4型号', '201910', 30.00);
INSERT INTO mydatetable VALUES ('1型号', '201911', 2.00);

GO
SELECT
PKNO,TotalCost,CurrMonth,CarType
FROM
MyDateTable
WHERE
CurrMonth <= ( CAST ( DATEPART( yyyy, GETDATE()) AS VARCHAR )
+ CAST ( DATEPART( mm, GETDATE()) AS VARCHAR ) )
AND
CurrMonth>
(
CAST ( DATEPART( yyyy, DATEADD( mm, -12, GETDATE())) AS VARCHAR ) +
CAST ( DATEPART( mm, DATEADD( mm, -12, GETDATE())) AS VARCHAR )
);

/* PKNO TotalCost CurrMonth CarType
1 2.00 201911 1型号
29 30.00 201910 4型号
60 64.00 201909 0型号
90 96.00 201908 0型号
121 123.00 201907 1型号
152 157.00 201906 2型号
182 182.00 201905 2型号
213 216.00 201904 3型号
243 248.00 201903 3型号
274 275.00 201902 4型号
302 303.00 201901 2型号
333 337.00 201812 3型号
*/

SELECT
sum(TotalCost) Cartype总费用,CarType,min(CAST ( DATEPART( yyyy, GETDATE()) AS VARCHAR ) 
+ CAST ( DATEPART( mm, GETDATE()) AS VARCHAR )) 当前月份

FROM
MyDateTable
WHERE
CurrMonth <= ( CAST ( DATEPART( yyyy, GETDATE()) AS VARCHAR )
+ CAST ( DATEPART( mm, GETDATE()) AS VARCHAR ) )
AND
CurrMonth>
(
--当前月份往前倒推12个月
CAST ( DATEPART( yyyy, DATEADD( mm, -12, GETDATE())) AS VARCHAR ) +
CAST ( DATEPART( mm, DATEADD( mm, -12, GETDATE())) AS VARCHAR )
)

GROUP BY CarType;

/* Cartype总费用 CarType 当前月份
160.00 0型号 201911
125.00 1型号 201911
642.00 2型号 201911
801.00 3型号 201911
305.00 4型号 201911 */

邀月 | 园豆：25475 (高人七级) | 2019-11-29 13:12

你好，感谢你的回答。这一部分计算是没有问题的，但是我需要计算以日期为参数的多条，比如说此表开始年份是2013年，截止到2019年有数据，就需计算中间6年也就是72个月的数据，共72条。

支持(0) 反对(0) 苏尚Lewis | 园豆：42 (初学一级) | 2019-12-05 08:38

@苏尚Lewis:
--1、创建初始表
IF OBJECT_ID('MyDateTable','U') IS NOT NULL
DROP TABLE MyDateTable

CREATE TABLE MyDateTable (
PKNo INT primary key identity(1,1),
CarType VARCHAR(20),
CurrMonth varCHAR(6),
TotalCost decimal(13, 2)
)
GO

--2、构造数据
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('1型号', '201312', '1.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('2型号', '201401', '2.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('3型号', '201402', '6.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('4型号', '201403', '8.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('5型号', '201404', '10.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('0型号', '201405', '18.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('1型号', '201406', '14.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('2型号', '201407', '17.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('3型号', '201408', '29.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('4型号', '201409', '33.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('5型号', '201410', '37.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('0型号', '201411', '53.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('1型号', '201412', '58.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('2型号', '201501', '56.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('3型号', '201502', '76.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('4型号', '201503', '82.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('5型号', '201504', '88.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('0型号', '201505', '112.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('1型号', '201506', '119.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('2型号', '201507', '126.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('3型号', '201508', '147.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('4型号', '201509', '155.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('5型号', '201510', '163.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('0型号', '201511', '195.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('1型号', '201512', '204.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('2型号', '201601', '213.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('3型号', '201602', '249.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('4型号', '201603', '252.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('5型号', '201604', '262.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('0型号', '201605', '302.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('1型号', '201606', '313.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('2型号', '201607', '324.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('3型号', '201608', '368.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('4型号', '201609', '380.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('5型号', '201610', '385.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('0型号', '201611', '433.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('1型号', '201612', '446.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('2型号', '201701', '459.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('3型号', '201702', '511.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('4型号', '201703', '525.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('5型号', '201704', '539.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('0型号', '201705', '588.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('1型号', '201706', '603.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('2型号', '201707', '618.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('3型号', '201708', '678.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('4型号', '201709', '694.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('5型号', '201710', '710.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('0型号', '201711', '774.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('1型号', '201712', '784.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('2型号', '201801', '801.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('3型号', '201802', '869.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('4型号', '201803', '887.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('5型号', '201804', '905.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('0型号', '201805', '977.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('1型号', '201806', '996.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('2型号', '201807', '1008.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('3型号', '201808', '1084.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('4型号', '201809', '1104.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('5型号', '201810', '1124.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('0型号', '201811', '1144.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('1型号', '201812', '1225.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('2型号', '201901', '1246.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('3型号', '201902', '1260.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('4型号', '201903', '1345.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('5型号', '201904', '1367.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('0型号', '201905', '1389.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('1型号', '201906', '1478.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('2型号', '201907', '1501.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('3型号', '201908', '1524.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('4型号', '201909', '1610.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('5型号', '201910', '1634.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('0型号', '201911', '1658.00');
INSERT INTO MyDateTable ([Cartype], [CurrMonth], [TotalCost]) VALUES ('1型号', '201912', '1755.00');

--3、查询明细数据
declare
@thisdate datetime,
@forMonth int

--指定日期
set @thisdate=Getdate()
--指定往前72个月
set @forMonth=72

SELECT
PKNO,TotalCost,CurrMonth,CarType
FROM
MyDateTable
WHERE
CurrMonth <= ( CAST ( DATEPART( yyyy, @thisdate) AS VARCHAR )
+ CAST ( DATEPART( mm, @thisdate) AS VARCHAR ) )
AND
CurrMonth>
(
CAST ( DATEPART( yyyy, DATEADD( mm, 0-@forMonth, @thisdate)) AS VARCHAR ) +
CAST ( DATEPART( mm, DATEADD( mm, 0-@forMonth, @thisdate)) AS VARCHAR )
);

--4、查询按型号的汇总数据
declare
@thisdate datetime,
@forMonth int

--指定日期
set @thisdate=Getdate()
--指定往前72个月
set @forMonth=72

SELECT
sum(TotalCost) Cartype总费用,CarType,min(CAST ( DATEPART( yyyy, @thisdate) AS VARCHAR )
+ CAST ( DATEPART( mm, @thisdate) AS VARCHAR )) 当前月份
FROM
MyDateTable
WHERE
CurrMonth <= ( CAST ( DATEPART( yyyy, @thisdate) AS VARCHAR )
+ CAST ( DATEPART( mm, @thisdate) AS VARCHAR ) )
AND
CurrMonth>
(
--当前月份往前倒推XX个月
CAST ( DATEPART( yyyy, DATEADD( mm, 0-@forMonth, @thisdate)) AS VARCHAR ) +
CAST ( DATEPART( mm, DATEADD( mm, 0-@forMonth, @thisdate)) AS VARCHAR )
)

GROUP BY CarType;

/*
Cartype总费用 CarType 当前月份
7643.00 0型号 201912
7995.00 1型号 201912
6371.00 2型号 201912
6801.00 3型号 201912
7075.00 4型号 201912
7224.00 5型号 201912
*/

支持(1) 反对(0) 邀月 | 园豆：25475 (高人七级) | 2019-12-05 09:42

清除回答草稿

您需要登录以后才能回答，未注册用户请先注册。

欢迎，请先 登录 或者 注册 。

SQL SERVER统计每个月往前推一年时间内的数据

欢迎，请先登录或者注册。