SELECT e , p , v , v / SUM(v) OVER(PARTITION BY e) FROM table
with tab(e,p,v) as ( select 'e1','p1','v1' union all select 'e1','p2','v2' union all select 'e1','p3','v3' union all select 'e2','p4','v4' union all select 'e2','p5','v5' union all select 'e2','p6','v6' )select a.e,a.p,a.v,a.v+'/('+stuff((select '+'+b.v from tab as b where b.e=a.e for XML path('')),1,1,'')+')' as ratio from tab as a/* e p v ratio --------------------------------- e1 p1 v1 v1/(v1+v2+v3) e1 p2 v2 v2/(v1+v2+v3) e1 p3 v3 v3/(v1+v2+v3) e2 p4 v4 v4/(v4+v5+v6) e2 p5 v5 v5/(v4+v5+v6) e2 p6 v6 v6/(v4+v5+v6) --------------------------------- */
(
select 'e1','p1','v1' union all
select 'e1','p2','v2' union all
select 'e1','p3','v3' union all
select 'e2','p4','v4' union all
select 'e2','p5','v5' union all
select 'e2','p6','v6'
)select a.e,a.p,a.v,a.v+'/('+stuff((select '+'+b.v from tab as b
where b.e=a.e for XML path('')),1,1,'')+')' as ratio from tab as a/*
e p v ratio
---------------------------------
e1 p1 v1 v1/(v1+v2+v3)
e1 p2 v2 v2/(v1+v2+v3)
e1 p3 v3 v3/(v1+v2+v3)
e2 p4 v4 v4/(v4+v5+v6)
e2 p5 v5 v5/(v4+v5+v6)
e2 p6 v6 v6/(v4+v5+v6)
---------------------------------
*/
where后面加些什么条件呢,能具体说下吗,谢谢!