我有三张表:
产品:
IDproduct Name reference stock shop
1 reloj 1234 2 1
2 pulsera 5678 3 1
3 anillo 91011 2 2
4 colgante 121314 1 2商店
IDshop
1
2 订单
IDorder Ordersid_shop id_product rate
1 1 2 3
2 1 3 5
3 2 4 2
4 1 4 2
5 2 1 4
6 2 5 5
7 1 1 2
8 1 1 3我想获取查询:
IDproduct Idshop MediumRate
1 1 (3+5+2+4+2+3)/6=3.16
2 1 3.16
3 2 (2+4+5)/3=3.66
4 2 3.66但是当我提出这个问题时:
SELECT iDorder, rate, ROUND((SUM(rate)/SUM(if(rate> '0', 1, 0))),1) as MediumRate, IDshop, IDproduct, shop
FROM Shops
INNER JOIN Products ON shop= IDshop
INNER JOIN Orders ON Ordersid_shop = IDshop
ORDER by IDproduct我得到了这个:
IDproduct Idshop MediumRate
1 1 3.1只有一个寄存器
发布于 2019-07-13 03:45:05
此Select语句
Select iDorder, rate, ROUND((SUM(rate)/SUM(if(rate> '0', 1, 0))),1) as MediumRate, IDshop, IDproduct, shop
From orders
inner join shops on Ordersid_shop = IDshop
inner join products on id_product = IDproduct
Group by IDproduct
order by IDproduct;得到你
iDorder rate MediumRate IDshop IDproduct shop
5 4 3 2 1 1
1 3 3 1 2 1
2 5 5 1 3 2
3 2 2 2 4 2你必须从订单开始,然后在内部加入其余的
https://stackoverflow.com/questions/57011925
复制相似问题