请教个SQL

[sunlaji008]

with t1 as
(
select 'A' c1,200 c2 from dual
union all
select 'A' c1,100 c2 from dual
union all
select 'A' c1,200 c2 from dual
union all
select 'B' c1,100 c2 from dual
union all
select 'B' c1,100 c2 from dual
)

select c1,c2 from t1

我想这样显示：
c1 c2
A 200
A 100
A 200
汇总500
B 100
B 100
汇总200

怎么写？

[sunlaji008]

[Quote=引用 6 楼 的回复:]

SQL code
with t1 as
(
select 'A' c1,200 c2 from dual
union all
select 'A' c1,100 c2 from dual
union all
select 'A' c1,200 c2 from dual
union all
select 'B' c1,100 c2 from dual
……
[/Quote]
thank you all the same

[sunlaji008]

[Quote=引用 8 楼 的回复:]

好一顿凑，终于出来了。
SQL code
with t1 as
(select 'A' c1, 200 c2
from dual
union all
select 'A' c1, 100 c2
from dual
union all
select 'A' c1, 200 c2
from dual
union all
select 'B' c1……
[/Quote]

thanks a lot

[sunlaji008]

我朝，真心求助。。。。。

[q806294478]

with t1 as
(
  select 'A' c1,200 c2 from dual  
  union all
  select 'A' c1,100 c2 from dual  
  union all
  select 'A' c1,200 c2 from dual  
  union all
  select 'B' c1,100 c2 from dual  
  union all
  select 'B' c1,100 c2 from dual  
)
select nvl(c1,c3) c1,nvl(c2,c4) c2 from t1 full join
(select '汇总'||c1 c3,sum(c2) c4 from t1 group by c1) t
on c1=c3
order by c1
结果显示为：
C1    C2                     
----- ---------------------- 
A     200                    
A     100                    
A     200                    
B     100                    
B     100                    
汇总A 500                    
汇总B 200                    

7 rows selected

[q806294478]

能力有限，只能这样

[lithor]

好一顿凑，终于出来了。

with t1 as
 (select 'A' c1, 200 c2
    from dual
  union all
  select 'A' c1, 100 c2
    from dual
  union all
  select 'A' c1, 200 c2
    from dual
  union all
  select 'B' c1, 100 c2
    from dual
  union all
  select 'B' c1, 100 c2 from dual)
select decode(t3.rn, null, '汇总', t3.c1) as c1, t3.s as c2
  from (select t2.rn, t2.c1, sum(t2.c2) s
          from (select t1.*, rownum rn from t1) t2
         group by cube(t2.rn, t2.c1)) t3
 where t3.c1 is not null