我在这里打败我，但是我正在给它一个很好的尝试（到目前为止已经尝试了5个小时）。 使用MariaDB。 我的问题是我缺乏SQL的语法知识

我的目标：显示受日期范围限制的每个类别的 销售产品总数 。

我认为我需要构建一个查询，该查询将输出|categories_name|SUM(products_quantity)| BETWEEN日期范围和GROUPed BY categories_name 。 （我可能是错的）

我需要的数据分布在5个表中： 

tbl_orders orders_id   date_purchased 1           1900-01-01 2           1900-01-02 3           1900-01-03  tbl_orders_products orders_id   products_id   products_quantity 1           100           5 1           200           2 2           100           1 3           100           1 3           300           2 3           400           1  tbl_products_to_categories products_id   categories_id 100           10 200           20 300           30 400           40  tbl_categories categories_id   parent_id 10              50 20              60 30              70 40              80  tbl_categories_description parent_id   categories_name 50          categoryA 60          categoryB 70          categoryC 80          categoryD

我已经尝试了很多SQL变体，以至于我什至都不知道该在哪发布SQL，以免我带领一个知识渊博的人走错了路。 如何（如果可能的话）如何检索想要的输出？ 我已经成功完成了INNER JOIN并使3表查询工作，但这使我感到困惑。 我应该在尝试尝试SQL之前就上大学SQL课吗？

      

===============>>#1 票数：0

这是所有表之间的简单连接，是一个分组的和。 

SELECT cd.categories_name, SUM(op.products_quantity) AS total FROM tbl_categories_description AS cd JOIN tbl_categories AS c ON c.parent_id = cd.parent_id JOIN tbl_products_to_categories AS pc ON pc.categories_id = c.categories_id JOIN tbl_orders_products AS op ON op.products_id = pc.products_id JOIN tbl_orders AS o ON o.orders_id = op.orders_id WHERE o.date_purchased BETWEEN '1900-01-01' AND '1900-01-02' GROUP BY cd.categories_name

如果要为没有产品或销售的类别获得零计数，则需要将所有联接更改为LEFT JOIN并将日期条件置于最后一个ON条件。 

SELECT cd.categories_name, IFNULL(SUM(p.products_quantity), 0) AS total FROM tbl_categories_description AS cd LEFT JOIN tbl_categories AS c ON c.parent_id = cd.parent_id LEFT JOIN tbl_products_to_categories AS pc ON pc.categories_id = c.categories_id LEFT JOIN tbl_orders_products AS op ON op.products_id = pc.products_id LEFT JOIN tbl_orders AS o ON o.orders_id = op.orders_id          AND o.date_purchased BETWEEN '1900-01-01' AND '1900-01-02' GROUP BY cd.categories_name

  ask by ttaylor translate from so

===============>>#2 票数：0

不太了解表tbl_categories和tbl_categories_description之间的tbl_categories_description 。

无论如何，以下查询将返回特定日期范围内每个类别的总产品。 

select   d.parent_id,   d.categories_name,   sum(op.products_quantity) as total_products from tbl_orders o join tbl_orders_products op on op.orders_id = o.orders_id join tbl_products_to_categories pc on pc.products_id = op.products_id join tbl_categories c on c.categories_id = pc.categories_id join tbl_categories_description d on d.parent_id = c.parent_id where o.date_purchased between '1900-01-02' and '1900-01-18' group by d.parent_id

  ask by ttaylor translate from so

本文未有回复，本站智能推荐：