首先我的SQL以及查出这样的数据了;
select col_1,co1_2,sum(col_2) from table group by col_1,col_2;
产品1,a销售,50
产品1,b销售,20
产品2,c销售,30
产品2,d销售,10我想再按产品求和,想查各个销售人员销售每种产品的比例,想要结果如下:
产品1,a销售,50,70
产品1,b销售,20,70
产品2,c销售,30,40
产品2,d销售,10,4070=50+20,40=30+10
先算出总和,我就可以算出比例了,这样SQL如何实现呢?
select col_1,co1_2,sum(col_2) from table group by col_1,col_2;
产品1,a销售,50
产品1,b销售,20
产品2,c销售,30
产品2,d销售,10我想再按产品求和,想查各个销售人员销售每种产品的比例,想要结果如下:
产品1,a销售,50,70
产品1,b销售,20,70
产品2,c销售,30,40
产品2,d销售,10,4070=50+20,40=30+10
先算出总和,我就可以算出比例了,这样SQL如何实现呢?
select a.col_1,a.col_2,a.sum1,b.sum2
from (select col_1,co1_2,sum(col_2) as sum1 from table group by col_1,col_2)a,
(select col_1,sum(col_2) as sum2 from table group by col_1)b
where a.col_1=b.col_1
select t.col1,t.col2,t.xj,t2.zj from
(select col1,col2,sum(col2) xj from table group by col1,col2) t,
(select col1,sum(col2) zj from table group by col1) t2
where t.col1=t2.col1;
select col_1, col_2, col_2 / t.c scale
from tableA,
(select col_1, co1_2, sum(col_2) c from tableA group by col_1, col_2) t
where col_1 = t.col_1
and col_2 = t.col_2;