顶级销售产品的SQL查询

Question

我试图提取过去7天的畅销产品（总销售额）的所有产品（限制5）。

我目前的数据库架构基本上是这样的：

- products 
- id 
- name 
- price 

- orders 
- id 
- purchased_at 

- orders_products 
- id 
- products_id 
- orders_id 
- quantity 
- paid_price 

我在寻找MySQL的句法。

在此先感谢！

Answer 1

这是INNER JOIN s的的quantity乘以paid_price的集合SUM()一个相对简单的序列：

SELECT 
    p.name, 
    /* aggregate SUM() of orders_products qty * price */ 
    SUM(op.quantity * op.paid_price) AS total_sale_price 
FROM 
    products 
    /* Join through orders_products (which gives the quantity) */ 
    INNER JOIN orders_products op ON p.id = op.products_id 
    /* ...to orders (which provides the date) */ 
    INNER JOIN orders o ON op.orders_id = o.id 
/* The last week... */ 
WHERE o.purchased_at >= (CURDATE() - INTERVAL 7 DAY) 
GROUP BY p.name 
/* In descending order of quantity, only the top 5 */ 
ORDER BY total_paid_price DESC LIMIT 5 

我建议花一些时间审查aggregate functions。