select * from tb
union all
select 月份,left(编码,6) 编码,sum(数量) 数量 from tb group by 月份,left(编码,6)
union all
select 月份,left(编码,4) 编码,sum(数量) 数量 from tb group by 月份,left(编码,4)
order by 月份,编码
union all
select 月份,left(编码,6) 编码,sum(数量) 数量 from tb group by 月份,left(编码,6)
union all
select 月份,left(编码,4) 编码,sum(数量) 数量 from tb group by 月份,left(编码,4)
order by 月份,编码
insert into tb values(1, '17200101', 5 )
insert into tb values(1, '17200102', 4 )
insert into tb values(1, '17200201', 3 )
insert into tb values(1, '17200202', 2 )
insert into tb values(2, '17200101', 1 )
insert into tb values(2, '17200102', 2 )
insert into tb values(2, '17200201', 3 )
insert into tb values(2, '17200202', 4 )
goselect * from tb
union all
select 月份,left(编码,6) 编码,sum(数量) 数量 from tb group by 月份,left(编码,6)
union all
select 月份,left(编码,4) 编码,sum(数量) 数量 from tb group by 月份,left(编码,4)
order by 月份,编码drop table tb/*
月份 编码 数量
----------- ---------- -----------
1 1720 14
1 172001 9
1 17200101 5
1 17200102 4
1 172002 5
1 17200201 3
1 17200202 2
2 1720 10
2 172001 3
2 17200101 1
2 17200102 2
2 172002 7
2 17200201 3
2 17200202 4(所影响的行数为 14 行)
*/
create table test1
(
月份 int,
编码 varchar(8),
数量 int
)
insert into test1 select 1, '17200101', 5
insert into test1 select 1, '17200102', 4
insert into test1 select 1, '17200201', 3
insert into test1 select 1, '17200202', 2
insert into test1 select 2, '17200101', 1
insert into test1 select 2, '17200102', 2
insert into test1 select 2, '17200201', 3
insert into test1 select 2, '17200202', 4
select b.月份,a.编码,sum(b.数量)'数量' from
(select left(编码,4)'编码' from test1
union
select left(编码,6)'编码' from test1
union
select left(编码,8)'编码' from test1)a
left join (select 月份,编码,数量=(select sum(数量) from test1 where 编码=t.编码 and 月份<=t.月份) from test1 t) b
on b.编码 like a.编码+'%'
group by b.月份,a.编码
order by b.月份
insert into tb values(1, '17200101', 5 )
insert into tb values(1, '17200102', 4 )
insert into tb values(1, '17200201', 3 )
insert into tb values(1, '17200202', 2 )
insert into tb values(2, '17200101', 1 )
insert into tb values(2, '17200102', 2 )
insert into tb values(2, '17200201', 3 )
insert into tb values(2, '17200202', 4 )
goselect 月份,编码, 数量 = (select sum(数量) from
(
select * from tb
union all
select 月份,left(编码,6) 编码,sum(数量) 数量 from tb group by 月份,left(编码,6)
union all
select 月份,left(编码,4) 编码,sum(数量) 数量 from tb group by 月份,left(编码,4)
) m
where 月份 <= t.月份 and 编码 = t.编码)
from
(
select * from tb
union all
select 月份,left(编码,6) 编码,sum(数量) 数量 from tb group by 月份,left(编码,6)
union all
select 月份,left(编码,4) 编码,sum(数量) 数量 from tb group by 月份,left(编码,4)
) t
order by 月份,编码
drop table tb/*
月份 编码 数量
----------- ---------- -----------
1 1720 14
1 172001 9
1 17200101 5
1 17200102 4
1 172002 5
1 17200201 3
1 17200202 2
2 1720 24
2 172001 12
2 17200101 6
2 17200102 6
2 172002 12
2 17200201 6
2 17200202 6(所影响的行数为 14 行)
*/
create table tb(yue int, type varchar(10), qty int)
insert into tb values(1, '17200101', 5 )
insert into tb values(1, '17200102', 4 )
insert into tb values(1, '17200201', 3 )
insert into tb values(1, '17200202', 2 )
insert into tb values(2, '17200101', 1 )
insert into tb values(2, '17200102', 2 )
insert into tb values(2, '17200201', 3 )
insert into tb values(2, '17200202', 4 )select yue,type,(select sum(qty) from tb where yue<=A.yue and type=A.type) as qty
into #
from tb Aselect * from #
union all
select yue,left(type,4) ,sum(qty) from # group by yue,left(type,4)
union all
select yue,left(type,6) ,sum(qty) from # group by yue,left(type,6)
order by yue,type
/*
yue type qty
---------------------------------------
1 1720 14
1 172001 9
1 17200101 5
1 17200102 4
1 172002 5
1 17200201 3
1 17200202 2
2 1720 24
2 172001 12
2 17200101 6
2 17200102 6
2 172002 12
2 17200201 6
2 17200202 6*/
drop table tb,#