表A
a1 a2 a3 a4 a5A 200 1 02 2008-02-02B 300 -1 02 2008-02-06C 400 -1 02 2008-02-09D 100 1 03 2008-02-12D 200 -1 02 2008-03-22A 300 1 02 2008-02-02C 200 1 02 2008-01-14B 100 1 03 2007-09-01查询条件 a5 > 2008-02-01 and a5 < 2009-10-04要求结果为
a1 a2 a3 a4 a5 a6
(a2=a2*a3)
A 200 1 02 2008-02-02A 300 1 02 2008-02-02A 500 小计B 100 期初余额B -300 -1 02 2008-02-06B -200 小计 C 200 1 02 2008-01-14C -400 -1 02 2008-02-09 C -200 小计
D -200 -1 02 2008-02-02 D 100 1 03 2008-03-22 D -100 小计 0 合计
a1 a2 a3 a4 a5A 200 1 02 2008-02-02B 300 -1 02 2008-02-06C 400 -1 02 2008-02-09D 100 1 03 2008-02-12D 200 -1 02 2008-03-22A 300 1 02 2008-02-02C 200 1 02 2008-01-14B 100 1 03 2007-09-01查询条件 a5 > 2008-02-01 and a5 < 2009-10-04要求结果为
a1 a2 a3 a4 a5 a6
(a2=a2*a3)
A 200 1 02 2008-02-02A 300 1 02 2008-02-02A 500 小计B 100 期初余额B -300 -1 02 2008-02-06B -200 小计 C 200 1 02 2008-01-14C -400 -1 02 2008-02-09 C -200 小计
D -200 -1 02 2008-02-02 D 100 1 03 2008-03-22 D -100 小计 0 合计
union all select 'b',300,-1,'02','2008-02-06' from dual
union all select 'c',400,-1,'02','2008-02-09' from dual
union all select 'd',100,1,'03','2008-02-12' from dual
union all select 'd',200,-1,'02','2008-03-22' from dual
union all select 'a',300,1,'02','2008-02-02' from dual
union all select 'c',200,1,'02','2008-01-14' from dual
union all select 'b',100,1,'03','2007-09-01' from dual)select a1,a2*a3 a2,a3,a4,a5,null a6 from a
where a5>'2008-02-01' and a5<'2009-10-04'
union all
select a1,sum(a2*a3),null,null,null,'小计' from a
where a5>'2008-02-01' and a5<'2009-10-04'
group by a1
union all
select null,sum(a2*a3),null,null,null,'合计' from a
order by 1,5A1 A2 A3 A4 A5 A6
a 200 1 02 2008-02-02
a 300 1 02 2008-02-02
a 500 小计
b -300 -1 02 2008-02-06
b -300 小计
c -400 -1 02 2008-02-09
c -400 小计
d 100 1 03 2008-02-12
d -200 -1 02 2008-03-22
d -100 小计
0 合计
合计应该为-300再来一个
with a as (select 'a' a1,200 a2,1 a3,'02' a4,'2008-02-02' a5 from dual
union all select 'b',300,-1,'02','2008-02-06' from dual
union all select 'c',400,-1,'02','2008-02-09' from dual
union all select 'd',100,1,'03','2008-02-12' from dual
union all select 'd',200,-1,'02','2008-03-22' from dual
union all select 'a',300,1,'02','2008-02-02' from dual
union all select 'c',200,1,'02','2008-01-14' from dual
union all select 'b',100,1,'03','2007-09-01' from dual)select a1,a2,a3,a4,a5,decode(a1,null,'合计',a6)a6 from(
select a1,a2*a3 a2,a3,a4,a5,null a6 from a
where a5>'2008-02-01' and a5<'2009-10-04'
union all
select a1,sum(a2*a3),null,null,null,'小计' from a
where a5>'2008-02-01' and a5<'2009-10-04'
group by rollup(a1)
)
order by 1,5
with a1 as(select a.*,lead(a5)over(partition by a1 order by a5)ld from a)
select a1,a2,a3,a4,a5,
case when a1 is null then '合计' when a5<='2008-02-01' then '期初余额' else a6 end a6
from(
select a1,a2*a3 a2,a3,a4,a5,null a6 from a1
where (a5>'2008-02-01'or ld>'2008-02-01') and a5<'2009-10-04'
union all
select a1,sum(a2*a3),null,null,null,'小计' from a1
where (a5>'2008-02-01' or ld>'2008-02-01') and a5<'2009-10-04'
group by rollup(a1)
)
order by 1,5
union all select 'b',300,-1,'02','2008-02-06' from dual
union all select 'c',400,-1,'02','2008-02-09' from dual
union all select 'd',100,1,'03','2008-02-12' from dual
union all select 'd',200,-1,'02','2008-03-22' from dual
union all select 'a',300,1,'02','2008-02-02' from dual
union all select 'c',200,1,'02','2008-01-14' from dual
union all select 'b',100,1,'03','2007-09-01' from dual)
select a.a1,a.a2,to_char(a.a3),a.a4,a.a5 from a a where a.a5>'2008-02-01' and a.a5<'2009-10-04'
union all
select b.a1,sum(a2*a3) a2,'','',decode(a1,'','合计','小计') from a b where b.a5>'2008-02-01' and b.a5<'2009-10-04'
group by cube(a1)
order by a1
--result:a 200 1 02 2008-02-02
a 300 1 02 2008-02-02
a 500 小计
b 300 -1 02 2008-02-06
b -300 小计
c 400 -1 02 2008-02-09
c -400 小计
d 100 1 03 2008-02-12
d 200 -1 02 2008-03-22
d -100 小计
-300 合计
SQL> with a as ( select 'a' a1,200 a2,1 a3,'02' a4,'2008-02-02' a5 from dual
2 union all select 'b',300,-1,'02','2008-02-06' from dual
3 union all select 'c',400,-1,'02','2008-02-09' from dual
4 union all select 'd',100,1,'03','2008-02-12' from dual
5 union all select 'd',200,-1,'02','2008-03-22' from dual
6 union all select 'a',300,1,'02','2008-02-02' from dual
7 union all select 'c',200,1,'02','2008-01-14' from dual
8 union all select 'b',100,1,'03','2007-09-01' from dual)
9 select a1,sum(a2*a3),a3,a4,a5,
10 case when grouping(a1)+grouping(a2)+grouping(a3)+grouping(a4)+grouping(a5) = 4 then 'subsum'
11 when grouping(a1)+grouping(a2)+grouping(a3)+grouping(a4)+grouping(a5) = 5 then 'tolsum'
12 end a6
13 from a
14 group by rollup(a1,a2,a3,a4,a5)
15 having grouping(a1)+grouping(a2)+grouping(a3)+grouping(a4)+grouping(a5) = 0
16 or grouping(a1)+grouping(a2)+grouping(a3)+grouping(a4)+grouping(a5) = 4
17 or grouping(a1)+grouping(a2)+grouping(a3)+grouping(a4)+grouping(a5) = 5;A1 SUM(A2*A3) A3 A4 A5 A6
-- ---------- ---------- -- ---------- ------
a 200 1 02 2008-02-02
a 300 1 02 2008-02-02
a 500 subsum
b 100 1 03 2007-09-01
b -300 -1 02 2008-02-06
b -200 subsum
c 200 1 02 2008-01-14
c -400 -1 02 2008-02-09
c -200 subsum
d 100 1 03 2008-02-12
d -200 -1 02 2008-03-22
d -100 subsum
0 tolsum13 rows selectedSQL>
with a as (select 'a' a1,200 a2,1 a3,'02' a4,'2008-02-02' a5 from dual
union all select 'b',300,-1,'02','2008-02-06' from dual
union all select 'c',400,-1,'02','2008-02-09' from dual
union all select 'd',100,1,'03','2008-02-12' from dual
union all select 'd',200,-1,'02','2008-03-22' from dual
union all select 'a',300,1,'02','2008-02-02' from dual
union all select 'c',200,1,'02','2008-01-14' from dual
union all select 'b',100,1,'03','2007-09-01' from dual)select case when count(distinct a1)=1 then max(a1) end a1,
sum(a2*a3) a2,case when count(1)=1 then max(a3) end a3,
case when count(1)=1 then max(a4) end a4,
case when count(1)=1 then max(a5) end a5,
case when count(distinct a1)>1 then '总计'
when count(a1)=1 and max(a5)<='2008-02-01' then '期初余额'
when count(a1)=1 then null
else '小计' end a6
from a a1
where a5>'2008-02-01' and a5<'2009-10-04'
or a5<='2008-02-01' and not exists(
select 1 from a where a1=a1.a1 and a5>a1.a5
and a5<='2008-02-01')
group by grouping sets((a1),0,rownum)
order by a1,a5