获得这样的结果
year_month product_code data_type qty 3month_qty
201401 C111 AAA 23 0
201401 D112 BBB 66 0
201402 C111 AAA 21 23
201402 D112 BBB 60 66
201403 C111 AAA 23 44
201403 D112 BBB 67 126
201404 C111 AAA 23 67
201405 C111 AAA 25 90
201405 D112 BBB 62 193
year_month product_code data_type qty 3month_qty
201401 C111 AAA 23 0
201401 D112 BBB 66 0
201402 C111 AAA 21 23
201402 D112 BBB 60 66
201403 C111 AAA 23 44
201403 D112 BBB 67 126
201404 C111 AAA 23 67
201405 C111 AAA 25 90
201405 D112 BBB 62 193
declare @table table (year_month varchar(6),product_code varchar(100),data_type varchar(100),qty int)insert into @table
select 201401,'C111','AAA',23
union all select 201401,'D112','BBB',66
union all select 201402,'C111','AAA',21
union all select 201402,'D112','BBB',60
union all select 201403,'C111','AAA',23
union all select 201403,'D112','BBB',27
union all select 201404,'C111','AAA',23
union all select 201405,'C111','AAA',25
union all select 201405,'D112','BBB',62
;
with cte as
(
select convert(datetime,year_month+'01')as year_month,product_code,data_type,qty from @table
)
select convert(varchar(6),getdate(),112) as year_month,product_code,data_type,
isnull((select sum(qty) from cte b where b.year_month between dateadd(month,-4,a.year_month) and dateadd(month,-1,a.year_month)
and a.product_code=b.product_code and a.data_type=b.data_type),0) as [3month_qty] from cte a第一次写代码勿喷,谢谢