Mysql的子查询太慢，查询销售数量

问题描述：

ordre_id  int 
add_time  int

二：表order_goods

id   int 
goods_id  int 
order_id  int 
sale_number int

三：台产品

goods_id  int 
goods_name varchar

现在我想要查询商品销售总数和这个数字的订单，并且当商品没有销售数量时，笑用零表示。我使用子查询，但它太慢，SQL是这样的：

SELECT t.* 
FROM goods AS tg 
LEFT JOIN (
    SELECT SUM(og.goods_number) AS total_num, g.goods_id, g.goods_name 
    FROM order_info oi 
    LEFT JOIN order_goods AS og  ON oi.order_id=og.order_id 
    LEFT JOIN goods AS g   ON g.goods_id=og.goods_id 
    WHERE oi.add_time>1415635200 
) AS t   ON t.goods_id=tg.goods_id 
ORDER BY t.total_num DESC;

你可以有一些商品的想法？谢谢！

答

为什么不直接去掉外层查询部分和运行

select 
    SUM(og.goods_number) AS total_num, 
    g.goods_id, 
    g.goods_name 
from 
    order_info oi 
left join order_goods AS og on oi.order_id=og.order_id 
left join goods AS g on g.goods_id=og.goods_id 
right join goods as g2 on g2.goods_id=og.goods_id 
where oi.add_time > 1415635200

？你在桌子上设置了什么指数？

答

您不需要外部查询您可以改为使用GROUP BY。

select SUM(og.goods_number) AS total_num, 
     g.goods_id, 
     g.goods_name 
from order_info oi 
left join order_goods AS og 
on oi.order_id=og.order_id 
left join goods AS g 
on g.goods_id=og.goods_id 
where oi.add_time>1415635200 
group by g.goods_id, g.goods_name 
order by total_num desc

答

如果我看了你的问题，你需要总销售所有商品。试试这个：

Select g.goods_id, g.goods_name, IsNull(SUM(og.goods_number), 0) as total_num 
From goods g 
Left Join order_goods og 
    On og.goods_id = g.goods_id 
Left Join order_info oi 
    On oi.order_id = og.order_id 
WHERE oi.add_time>1415635200 
Group By g.goods_id, g.goods_name 
Order By total_num desc

我只是查询混淆实际。 “goods_number”列来自哪里？是什么意思是“sale_number”列？

Mysql的子查询太慢，查询销售数量

相关推荐