if object_id('[tb1]') is not null drop table [tb1]
go
create table tb1 (年 int, 月 int, 日期 datetime, 产品类型 char(1), 销售金额 int)
insert into tb1
select '2014','1','2014-1-10','A','50' union all
select '2014','7','2014-7-10','A','100' union all
select '2014','7','2014-7-25','A','200' union all
select '2014','7','2014-7-14','B','150' union all
select '2014','8','2014-7-26','A','300' union all
select '2014','8','2014-8-1','B','400' union all
select '2014','12','2014-11-27','B','400'select (a.年),(a.月),(a.日期),b.产品类型, case when a.产品类型=b.产品类型 then a.销售金额 else 0 end as 销售金额,row_number() over (partition by 月,日期,b.产品类型 order by 月 desc) as rn from tb1 a cross join (select distinct 产品类型 from tb1) b--年 月 日期 产品类型 销售金额 rn
--2014 1 2014-01-10 00:00:00.000 A 50 1
--2014 1 2014-01-10 00:00:00.000 B 0 1
--2014 7 2014-07-10 00:00:00.000 A 100 1
--2014 7 2014-07-10 00:00:00.000 B 0 1
--2014 7 2014-07-14 00:00:00.000 A 0 1
--2014 7 2014-07-14 00:00:00.000 B 150 1
--2014 7 2014-07-25 00:00:00.000 A 200 1
--2014 7 2014-07-25 00:00:00.000 B 0 1
--2014 8 2014-07-26 00:00:00.000 A 300 1
--2014 8 2014-07-26 00:00:00.000 B 0 1
--2014 8 2014-08-01 00:00:00.000 A 0 1
--2014 8 2014-08-01 00:00:00.000 B 400 1
--2014 12 2014-11-27 00:00:00.000 A 0 1
--2014 12 2014-11-27 00:00:00.000 B 400 1
go
create table tb1 (年 int, 月 int, 日期 datetime, 产品类型 char(1), 销售金额 int)
insert into tb1
select '2014','1','2014-1-10','A','50' union all
select '2014','7','2014-7-10','A','100' union all
select '2014','7','2014-7-25','A','200' union all
select '2014','7','2014-7-14','B','150' union all
select '2014','8','2014-7-26','A','300' union all
select '2014','8','2014-8-1','B','400' union all
select '2014','12','2014-11-27','B','400'select (a.年),(a.月),(a.日期),b.产品类型, case when a.产品类型=b.产品类型 then a.销售金额 else 0 end as 销售金额,row_number() over (partition by 月,日期,b.产品类型 order by 月 desc) as rn from tb1 a cross join (select distinct 产品类型 from tb1) b--年 月 日期 产品类型 销售金额 rn
--2014 1 2014-01-10 00:00:00.000 A 50 1
--2014 1 2014-01-10 00:00:00.000 B 0 1
--2014 7 2014-07-10 00:00:00.000 A 100 1
--2014 7 2014-07-10 00:00:00.000 B 0 1
--2014 7 2014-07-14 00:00:00.000 A 0 1
--2014 7 2014-07-14 00:00:00.000 B 150 1
--2014 7 2014-07-25 00:00:00.000 A 200 1
--2014 7 2014-07-25 00:00:00.000 B 0 1
--2014 8 2014-07-26 00:00:00.000 A 300 1
--2014 8 2014-07-26 00:00:00.000 B 0 1
--2014 8 2014-08-01 00:00:00.000 A 0 1
--2014 8 2014-08-01 00:00:00.000 B 400 1
--2014 12 2014-11-27 00:00:00.000 A 0 1
--2014 12 2014-11-27 00:00:00.000 B 400 1
SELECT T0.年,
T0.月,
T0.日期,
T0.产品类型,
ISNULL(tb1.销售金额,0) 销售金额,
(SELECT ISNULL(SUM(T1.销售金额),0)
FROM tb1 T1
WHERE T1.年 = T0.年
AND T1.月 = T0.月
AND T1.产品类型 = T0.产品类型
AND T1.日期 <= T0.日期) 本月截止到当日销售金额
FROM (SELECT *
FROM (SELECT DISTINCT 年,月,日期 FROM tb1) T01
, (SELECT DISTINCT 产品类型 FROM tb1) T02
) T0
LEFT JOIN tb1
ON tb1.日期 = T0.日期
AND tb1.产品类型 = T0.产品类型
年 月 日期 产品类型 销售金额 本月截止到当日销售金额
----- --- ------------ -------- ----------- -----------
2014 1 2014-01-10 A 50 50
2014 1 2014-01-10 B 0 0
2014 7 2014-07-10 A 100 100
2014 7 2014-07-10 B 0 0
2014 7 2014-07-14 A 0 100
2014 7 2014-07-14 B 150 150
2014 7 2014-07-25 A 200 300
2014 7 2014-07-25 B 0 150
2014 8 2014-07-26 A 300 300
2014 8 2014-07-26 B 0 0
2014 8 2014-08-01 A 0 300
2014 8 2014-08-01 B 400 400
2014 12 2014-11-27 A 0 0
2014 12 2014-11-27 B 400 400