MySQL |来自 3 个表的请求

Question

我有一张 table :

1. orders

id userid status placed
1  1      9      2014-05-02
2  1      8      2014-05-02 

2. products

id qty name
1  1   Samsung galaxy s4
2  1   Samsung galaxy s4

3. orders_products

id orderid productid qty
1  1       1         2 
2  2       2         2

我的请求如下所示:

SELECT P.name, SUM(OP.qty) count FROM `orders_products` OP, `products` P WHERE OP.productid = P.id GROUP BY P.id ORDER BY SUM(OP.qty) DESC LIMIT 10

如何显示订单状态 = 9 的结果并放置在两个日期之间？

谢谢!

Answer 1

您可以尝试这个，它是您想要实现的目标的另一种变体。

select
p.id,
p.name,
coalesce (sum(`tot`),0) as `count`
from products p
inner join
(
  select 
  sum(qty) as `tot`,
  productid
  from orders_products
  inner join `orders` on `orders`.`id` = orders_products.orderid
  where `orders`.status = 9
  group by productid
)t
on p.id = t.productid
group by p.id
order by `count`

DEMO