mysql - MySQL Inner加入最近7天找到最畅销的产品

sqlfiddle.com/#!9/ecede/1
表格和示例数据：

CREATE TABLE orders(
    order_id INT NOT NULL,
    created_at DATE NOT NULL,
    country CHAR(10),
    total_net_revenue INT NOT NULL,
    total_gross_revenue INT NOT NULL,
    total_quantity INT NOT NULL
);

INSERT INTO orders VALUES
    (101,'2018-03-08',"China",150,250,20),
    (102,'2018-03-08',"China",140,280,67),
    (103,'2018-03-08',"China",150,190,15),
    (111,'2018-02-09',"China",150,190,15),
    (104,'2018-03-07',"China",150,190,15);

CREATE TABLE products_inventory(
    product_id INT NOT NULL,
    product_name CHAR(10),
    brand_name CHAR(10),
    purchase_price INT NOT NULL,
    selling_price INT NOT NULL,
    units_remaining_in_inventory INT NOT NULL
);

INSERT INTO products_inventory VALUES
    (97,"3ds","Nintendo",100,250,40),
    (98,"Switch","Nintendo",140,280,102),
    (99,"Mini","Nintendo",40,190,30),
    (131,"Fail","Nintendo",40,190,1310);

CREATE TABLE items_in_order(
    order_id INT NOT NULL,
    country CHAR(10),
    brand CHAR(10),
    product_name CHAR(10),
    product_ID INT NOT NULL,
    net_revenue INT NOT NULL,
    gross_revenue INT NOT NULL,
    quantity INT NOT NULL
);

INSERT INTO items_in_order VALUES
    (101,"China","Nintendo","3ds",97,150,250,20),
    (102,"China","Nintendo","Switch",98,140,280,67),
    (103,"China","Nintendo","Mini",99,150,190,15),
    (111,"China","Nintendo","Fail",131,150,190,15),
    (104,"China","Nintendo","3ds",97,150,250,20);

查询：

SELECT i1.product_name, i1.product_id, i1.brand,
SUM(i1.net_revenue) AS net_revenue_last7days,
(p2.selling_price - p2.purchase_price) AS item_margin,
remaining_stock

FROM items_in_order i1

INNER JOIN(
  SELECT o1.order_id,o1.country,SUM(o1.total_net_revenue) AS net_revenue,
  SUM(o1.total_gross_revenue) AS gross_revenue
  FROM orders o1
  WHERE (created_at >= NOW() - INTERVAL 7 DAY) AND o1.country = "China"
  GROUP BY 1,2
  ) o2
  ON o2.order_id = i1.order_id AND o2.country = i1.country AND o2.net_revenue = i1.net_revenue
  AND o2.gross_revenue = i1.gross_revenue

INNER JOIN(
  SELECT product_id, product_name, brand_name AS brand, purchase_price, selling_price,
  SUM(units_remaining_in_inventory) AS remaining_stock
  FROM products_inventory p1
  GROUP BY 1,2,3
  ) p2
  ON i1.product_id = p2.product_id AND i1.product_name = p2.product_name AND i1.brand = p2.brand

WHERE i1.country = "China" AND i1.brand = "Nintendo"
GROUP BY 1,2,3
ORDER BY 4 DESC
LIMIT 10

我试图得到每个品牌在过去7天的收入总和，并加入表：订单和物品。
我的内部连接有问题吗？它应该显示3ds过去7天的净收入为300。
其他信息我正在努力实现：
任天堂过去7天在中国的单品最高收入
显示每个项目的当前库存单位
显示每个项目的边距

最佳答案

第一个JOIN的这一部分阻止了订单104被包括在内：

AND o2.gross_revenue = i1.gross_revenue

订单104的orders表中的总收入是190，但250表中的总收入是items_in_order。
如果从JOIN中删除它，就会得到net_revenue_last7days = 300。
http://sqlfiddle.com/#!9/ecede/10

关于mysql - MySQL Inner加入最近7天找到最畅销的产品，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49183211/