是什么类型的数据库?数据库版本是多少?并请提供表结构,你这SQL明显有问题,没有group by ,也没使用开窗函数,也没使用子查询,这样是算不了占比的。占比先要算出个单独的汇总值
---只显示有销售的产品
select a.pid,a.x/b.x 占比 from
(select cp.pid,sum(cp.price*xl.sale_amount) x
from 销量明细 xl,产品明细 cp
where xl.pid=cp.pid
group by cp.pid) a,
(select sum(cp.price*xl.sale_amount) x
from 销量明细 xl,产品明细 cp
where xl.pid=cp.pid) b;
---所有产品都要显示
select a.pid,a.x/b.x 占比 from
(select cp.pid,sum(cp.price*xl.sale_amount) x
from 产品明细 cp left join 销量明细 xl
on xl.pid=cp.pid
group by cp.pid) a,
(select sum(cp.price*xl.sale_amount) x
from 销量明细 xl,产品明细 cp
where xl.pid=cp.pid) b;
---mysql8以上,先聚合再用开窗
select a.pid,x/sum(x) over() 占比 from
(select cp.pid,sum(cp.price*xl.sale_amount) x
from 产品明细 cp left join 销量明细 xl
on xl.pid=cp.pid
group by cp.pid) a;
---mysql8以上,聚合和开窗一起用
select cp.pid,sum(cp.price*xl.sale_amount) /
sum(sum(cp.price*xl.sale_amount) ) over() 占比
from 产品明细 cp left join 销量明细 xl
on xl.pid=cp.pid
group by cp.pid
至于要过滤出10%的,再包一层写个where条件就行了