sql的问题

[南山行者]

有两个表t1，t2，t1是分类，t2是具体对象，
先做成下面的样子：
分类1，10，10
分类2，20，30
分类3，40，70
分类4，20，90
就是第三类累加，目前SQL是这样子
select
t1.typeid
,sum(t1.total)/(select sum(total)from t1) as total1
,sum(sum(t1.total)/(select sum(total)from t1))over(partition by t1.typeid order by t1.typeid ) as total2
from t1,t2
where t1.id=t2.id(+)
group by t1.typeid
但是其中累加字段计算方法感觉没有效率，想把查询SQL用前一个字段的别名total1来代替,但报错
,sum(total1)over(partition by t1.typeid order by t1.typeid ) as total2
各位有什么好的办法吗

[chengccy]

这里SQL 完全不能理解,没有引用T2表的任何字段,为什么要关联T2; 既然查询了T1表为什么还要用标量子查询查询T1表... 麻烦枚举下,T1,T2表的例子数据,和需要展现的表格数据,以及实现逻辑;

[mayanzs]

with t1 as ( select 'A' id , '类型1' name from dual union all select 'B', '类型2' from dual union all select 'C','类型3' from dual ), t2 as (select 'A' id,'1001' gid,100 total from dual union all select 'A','1002',200 from dual union all select 'A','1003',300 from dual union all select 'B','2001',400 from dual union all select 'B','2002',500 from dual union all select 'B','2003',600 from dual union all select 'C','3001',700 from dual union all select 'C','3002',800 from dual union all select 'C','3003',900 from dual), t3 as (select t1.id,t1.name,round(100*ratio_report(sum(t2.total) over(),2) s1 from t2 left join t1 on t2.id=t1.id group by t1.id,t1.name order by t1.id) select id,name,s1||'%' s1,sum(s1) over (order by id rows between unbounded preceding and current row)||'%' s2 from t3;

[南山行者]

抱歉，我写错了，应该是 select t1.typeid ,sum(t2.total)/(select sum(total)from t2) as total1 ,sum(sum(t2.total)/(select sum(total)from t2))over(partition by t1.typeid order by t1.typeid ) as total2 from t1,t2 where t1.id=t2.id(+) group by t1.typeid t1的表结构 id name A 类型1 B 类型2 C 类型3 t2的表结构 id gid total A 1001 100 A 1002 200 A 1003 300 B 2001 400 B 2002 500 B 2003 600 C 3001 700 C 3002 800 C 3003 900 想要的结果就是每个分类站的百分比,如下 A 10% 10% B 30% 40% C 60% 100% 关键是第三行是对上一行的累加，这个怎么算呢

[chengccy]

这样?

with tmp as (
select 'A' id , '类型1' name from dual union all  
select 'B', '类型2' from dual union all  
select 'C','类型3' from dual   
),
t2 as 
(select 'A' id,'1001' gid,100 total from dual union all  
select 'A','1002',200 from dual union all  
select 'A','1003',300 from dual union all  
select 'B','2001',400 from dual union all  
select 'B','2002',500 from dual union all  
select 'B','2003',600 from dual union all  
select 'C','3001',700 from dual union all  
select 'C','3002',800 from dual union all  
select 'C','3003',900 from dual)
SELECT ID, ROUND(WEIGHT) || '%', ROUND(SUM(WEIGHT) OVER(ORDER BY ID)) || '%'
FROM   (SELECT ID, TOTAL / SUM(TOTAL) OVER() * 100 AS WEIGHT
         FROM   (SELECT A.ID, SUM(B.TOTAL) AS TOTAL
                  FROM   TMP A
                  LEFT   JOIN T2 B
                  ON     A.ID = B.ID
                  GROUP  BY A.ID))