我正在尝试查询 MySQL。我有 2 个表，数据如下所示:

类别历史结构

+----------------+-----------------+----------+----+-----------+------------+------------+
| category       | parent_category | type     | id | parent_id | from_date  | to_date    |
+----------------+-----------------+----------+----+-----------+------------+------------+
| Top level      |                 | category | 1  | 0         | 01.01.2013 | 01.01.2015 |
+----------------+-----------------+----------+----+-----------+------------+------------+
| Category 1     | Top level       | category | 2  | 1         | 01.01.2013 | 01.01.2015 |
+----------------+-----------------+----------+----+-----------+------------+------------+
| Category 2     | Top level       | category | 3  | 1         | 01.01.2013 | 01.01.2015 |
+----------------+-----------------+----------+----+-----------+------------+------------+
| Sub category 1 | Category 1      | category | 4  | 2         | 01.01.2013 | 01.01.2015 |
+----------------+-----------------+----------+----+-----------+------------+------------+
| Sub category 2 | Category 1      | category | 5  | 2         | 01.01.2013 | 01.03.2013 |
+----------------+-----------------+----------+----+-----------+------------+------------+
| Sub category 2 | Category 2      | category | 5  | 3         | 02.03.2013 | 01.01.2015 |
+----------------+-----------------+----------+----+-----------+------------+------------+
| Product 1      | Sub category 2  | product  | 6  | 5         | 01.01.2013 | 01.01.2015 |
+----------------+-----------------+----------+----+-----------+------------+------------+

产品销量

+----+-----------+------+------------+
| id | product   | sell | date       |
+----+-----------+------+------------+
| 6  | Product 1 | 2    | 01.02.2013 |
+----+-----------+------+------------+
| 6  | Product 1 | 1    | 01.05.2013 |
+----+-----------+------+------------+
| 6  | Product 1 | 3    | 01.06.2013 |
+----+-----------+------+------------+

我需要销售日期范围为 2013-01-01 - 2015-01-01 的商品，按类别分组。 尝试创建一个按类别输出销售的查询，问题是“子类别 2”更改了 Parent_Category/parent_id 并且“产品 1”的结果必须在 2 行上

结果

+-----------+------------+----------------+-----------+------------+------------+------+
| Top level | Category   | Sub category   | Product   | from_date  | to_date    | sell |
+-----------+------------+----------------+-----------+------------+------------+------+
| Top level | Category 1 | Sub category 2 | Product 1 | 01.01.2013 | 01.03.2013 | 2    |
+-----------+------------+----------------+-----------+------------+------------+------+
| Top level | Category 2 | Sub category 2 | Product 1 | 02.03.2013 | 01.01.2015 | 4    |
+-----------+------------+----------------+-----------+------------+------------+------+

最佳答案

这是一个 sqlfiddle: http://sqlfiddle.com/#!2/cdb68/3 . 我把日期放在 mysql 格式中。我假设“01.06.2013”​​表示“2013 年 6 月 1 日”或“2013-06-01”。

SELECT 
'Top level',
cat.parent_category as category, 
subcat.parent_category as subcategory, 
ps.product,
cat.from_date, cat.to_date, 
SUM(ps.sell)


FROM product_sells AS ps
LEFT JOIN category_history_structure as subcat
ON (
  ps.id = subcat.id
  AND ps.date BETWEEN subcat.from_date AND subcat.to_date
  AND subcat.type = 'product'
  )
LEFT JOIN category_history_structure as cat
ON (
  subcat.parent_id = cat.id
  AND ps.date BETWEEN cat.from_date AND cat.to_date
  AND cat.type = 'category'
  )

GROUP BY category;

如果顶级类别也已更改，则可能会出现问题，但这可以通过另一个 LEFT JOIN 来解决。此外，我假设产品仅属于子类别，而不是直接属于类别。

关于mysql - 按日期范围历史生成完整类别结构，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/19489857/