情景-应确定所有有效类别/产品组及其对其中所有产品的有效规格.
- 产品有不同的规格/功能
- 产品始终挂在产品组树中最低的层次 struct 级别.
- 规格可以附加到不同的级别,在这种情况下,可以假设它们只附加到最高(非常一般)和最低级别(非常特殊的尺寸、压力规格等).
由于产品是在最低级别链接的,在我看来,将路径中最低级别的路径中较高级别的所有规范组合在一起将是最简单的.
product_groups
id | parent_id |
---|---|
1 | 空 |
2 | 1 |
3 | 2 |
4 | 3 |
5 | 空 |
6 | 5 |
7 | 6 |
8 | 7 |
specification_to_group
id | product_group_id | 规范ID |
---|---|---|
1 | 1 | 1 |
2 | 1 | 2 |
3 | 4 | 10 |
4 | 4 | 11 |
5 | 4 | 12 |
6 | 5 | 1 |
7 | 5 | 3 |
8 | 7 | 12 |
result
product_group_id | 规范ID |
---|---|
4 | 1 |
4 | 2 |
4 | 10 |
4 | 11 |
4 | 12 |
8 | 1 |
8 | 3 |
8 | 12 |
一种方法是try 使用帮助表,并且不那么通用.
SELECT
lst.id AS lst,
up1.id AS up1,
up2.id AS up2,
up3.id AS up3,
up4.id AS up4
FROM
product_groups lst
LEFT JOIN
product_groups up1 ON lst.parent_id = up1.id
LEFT JOIN
product_groups up2 ON up1.parent_id = up2.id
LEFT JOIN
product_groups up3 ON up2.parent_id = up3.id
LEFT JOIN
product_groups up4 ON up3.parent_id = up4.id
WHERE
lst.id NOT IN
(SELECT DISTINCT parent_id
FROM product_groups
WHERE parent_id IS NOT NULL
)
SELECT g.lst, s.specification_id
FROM ht_tree g
LEFT JOIN specification_to_group s ON g.lst = s.product_group_id
UNION SELECT g.lst, s.specification_id
FROM ht_tree g
LEFT JOIN specification_to_group s ON g.up1 = s.product_group_id
UNION SELECT g.lst, s.specification_id
FROM ht_tree g
LEFT JOIN specification_to_group s ON g.up2 = s.product_group_id
UNION SELECT g.lst, s.specification_id
FROM ht_tree g
LEFT JOIN specification_to_group s ON g.up3 = s.product_group_id
UNION
SELECT g.lst, s.specification_id
FROM ht_tree g
LEFT JOIN specification_to_group s ON g.up4 = s.product_group_id;
;
由于没有用于创建表的权限,也没有其他选项来持久化已经准备好的数据,我认为应该是CTE
.