我有一个现有的产品变型方案。
我想创建每个生产时间，数量和变化选项的组合。
我将通过访问产品的数量，生产时间，差异和差异选项来创建选择表单。
table_groups
+------------+ | id | title | +----+-------+ | 1 | rug | +----+-------+
table_days
+----+----------+------+ | id | group_id | day | +----+----------+------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 1 | 3 | +----+----------+------+
table_quantities
+----+----------+-----------+ | id | group_id | quantity | +----+----------+-----------+ | 1 | 1 | 100 | | 2 | 1 | 200 | | 3 | 1 | 300 | | 4 | 1 | 400 | +----+----------+-----------+
table_attributes
+----+----------+-----------+ | id | group_id | title | +----+----------+-----------+ | 1 | 1 | Color | | 2 | 1 | Size | +----+----------+-----------+
table_attribute_values
+----+----------+--------------+--------+ | id | group_id | attribute_id | title | +----+----------+--------------+--------+ | 1 | 1 | 1 | Red | | 2 | 1 | 1 | Yellow | | 3 | 1 | 1 | Black | | 4 | 1 | 2 | Small | | 5 | 1 | 2 | Medium | +----+----------+--------------+--------+
我准备了一个示例架构。但是，我没有得到想要的结果。
SQL小提琴
我做了很多事情：
SELECT GROUP_CONCAT(DISTINCT days_group) as days_list, GROUP_CONCAT(DISTINCT quantities_group SEPARATOR ',') as quantities_list, GROUP_CONCAT(DISTINCT attribute_values_group SEPARATOR ',') as attribute_values_list FROM table_groups LEFT JOIN ( SELECT days.day, days.group_id, GROUP_CONCAT(days.day) as days_group FROM table_days days GROUP BY days.id ) joindays ON joindays.group_id = table_groups.id LEFT JOIN ( SELECT quantities.quantity, quantities.group_id, GROUP_CONCAT(quantities.quantity) as quantities_group FROM table_quantities quantities GROUP BY quantities.id ) joinquantities ON joinquantities.group_id = table_groups.id LEFT JOIN table_attributes attributes ON attributes.group_id = table_groups.id LEFT JOIN ( SELECT attribute_id, group_id, GROUP_CONCAT(attribute_values.title) as attribute_values_group FROM table_attribute_values attribute_values GROUP BY attribute_values.attribute_id, attribute_values.id ) joinattributevalues ON joinattributevalues.attribute_id = attributes.id GROUP BY joinattributevalues.attribute_id;
查询结果：
+---------------+-----------+-----------------+-----------------------+ | group_id | days_list | quantities_list | attribute_values_list | +---------------+-----------+-----------------+-----------------------+ | 1 | 1,2,3 | 100,200,300,400 | Red,Yellow,Black | | 2 | 1,2,3 | 100,200,300,400 | Small,Medium | +---------------+-----------+-----------------+-----------------------+
我想要的正确结果应该如下。你能帮忙吗？
+-----------+---------------------+--------+ | group_id | combinations | price | +-----------+---------------------+--------+ | 1 | 1-100-Red-Small | | +-----------+---------------------+--------+ | 1 | 1-100-Red-Medium | | +-----------+---------------------+--------+ | 1 | 1-100-Yellow-Small | | +-----------+---------------------+--------+ | 1 | 1-100-Yellow-Medium | | +-----------+---------------------+--------+ | 1 | 1-100-Black-Small | | +-----------+---------------------+--------+ | 1 | 1-100-Black-Medium | | +-----------+---------------------+--------+ | 1 | 1-200-Red-Small | | +-----------+---------------------+--------+ | 1 | 1-200-Red-Medium | | +-----------+---------------------+--------+ | 1 | 1-200-Yellow-Small | | +-----------+---------------------+--------+ | 1 | 1-200-Yellow-Medium | | +-----------+---------------------+--------+ | 1 | 1-200-Black-Small | | +-----------+---------------------+--------+ | 1 | 1-200-Black-Medium | | +-----------+---------------------+--------+ | 1 | 1-300-Red-Small | | +-----------+---------------------+--------+ | 1 | 1-300-Red-Medium | | +-----------+---------------------+--------+ | 1 | 1-300-Yellow-Small | | +-----------+---------------------+--------+ | 1 | 1-300-Yellow-Medium | | +-----------+---------------------+--------+ | 1 | 1-300-Black-Small | | +-----------+---------------------+--------+ | 1 | 1-300-Black-Medium | | +-----------+---------------------+--------+ | 1 | 1-400-Red-Small | | +-----------+---------------------+--------+ | 1 | 1-400-Red-Medium | | +-----------+---------------------+--------+ | 1 | 1-400-Yellow-Small | | +-----------+---------------------+--------+ | 1 | 1-400-Yellow-Medium | | +-----------+---------------------+--------+ | 1 | 1-400-Black-Small | | +-----------+---------------------+--------+ | 1 | 1-400-Black-Medium | | +-----------+---------------------+--------+ | 1 | 2-100-Red-Small | | +-----------+---------------------+--------+ | 1 | 2-100-Red-Medium | | +-----------+---------------------+--------+ | 1 | 2-100-Yellow-Small | | +-----------+---------------------+--------+ | 1 | 2-100-Yellow-Medium | | +-----------+---------------------+--------+ | 1 | 2-100-Black-Small | | +-----------+---------------------+--------+ | 1 | 2-100-Black-Medium | | +-----------+---------------------+--------+ | 1 | 2-200-Red-Small | | +-----------+---------------------+--------+ | 1 | 2-200-Red-Medium | | +-----------+---------------------+--------+ | 1 | 2-200-Yellow-Small | | +-----------+---------------------+--------+ | 1 | 2-200-Yellow-Medium | | +-----------+---------------------+--------+ | 1 | 2-200-Black-Small | | +-----------+---------------------+--------+ | 1 | 2-200-Black-Medium | | +-----------+---------------------+--------+ | 1 | 2-300-Red-Small | | +-----------+---------------------+--------+ | 1 | 2-300-Red-Medium | | +-----------+---------------------+--------+ | 1 | 2-300-Yellow-Small | | +-----------+---------------------+--------+ | 1 | 2-300-Yellow-Medium | | +-----------+---------------------+--------+ | 1 | 2-300-Black-Small | | +-----------+---------------------+--------+ | 1 | 2-300-Black-Medium | | +-----------+---------------------+--------+ | 1 | 2-400-Red-Small | | +-----------+---------------------+--------+ | 1 | 2-400-Red-Medium | | +-----------+---------------------+--------+ | 1 | 2-400-Yellow-Small | | +-----------+---------------------+--------+ | 1 | 2-400-Yellow-Medium | | +-----------+---------------------+--------+ | 1 | 2-400-Black-Small | | +-----------+---------------------+--------+ | 1 | 2-400-Black-Medium | | +-----------+---------------------+--------+ | 1 | 3-100-Red-Small | | +-----------+---------------------+--------+ | 1 | 3-100-Red-Medium | | +-----------+---------------------+--------+ | 1 | 3-100-Yellow-Small | | +-----------+---------------------+--------+ | 1 | 3-100-Yellow-Medium | | +-----------+---------------------+--------+ | 1 | 3-100-Black-Small | | +-----------+---------------------+--------+ | 1 | 3-100-Black-Medium | | +-----------+---------------------+--------+ | 1 | 3-200-Red-Small | | +-----------+---------------------+--------+ | 1 | 3-200-Red-Medium | | +-----------+---------------------+--------+ | 1 | 3-200-Yellow-Small | | +-----------+---------------------+--------+ | 1 | 3-200-Yellow-Medium | | +-----------+---------------------+--------+ | 1 | 3-200-Black-Small | | +-----------+---------------------+--------+ | 1 | 3-200-Black-Medium | | +-----------+---------------------+--------+ | 1 | 3-300-Red-Small | | +-----------+---------------------+--------+ | 1 | 3-300-Red-Medium | | +-----------+---------------------+--------+ | 1 | 3-300-Yellow-Small | | +-----------+---------------------+--------+ | 1 | 3-300-Yellow-Medium | | +-----------+---------------------+--------+ | 1 | 3-300-Black-Small | | +-----------+---------------------+--------+ | 1 | 3-300-Black-Medium | | +-----------+---------------------+--------+ | 1 | 3-400-Red-Small | | +-----------+---------------------+--------+ | 1 | 3-400-Red-Medium | | +-----------+---------------------+--------+ | 1 | 3-400-Yellow-Small | | +-----------+---------------------+--------+ | 1 | 3-400-Yellow-Medium | | +-----------+---------------------+--------+ | 1 | 3-400-Black-Small | | +-----------+---------------------+--------+ | 1 | 3-400-Black-Medium | | +-----------+---------------------+--------+
注意： 组，属性和属性值的数量没有限制。示例结果可能是这样的：
Attributes: +-------+------+-------+--------+ | Color | Size | Model | Gender | +-------+------+-------+--------+ Combinations: +------------------------------+ | 1-100-Red-Small-Model 1-Male | +------------------------------+ | 1-100-Red-Small-Model 2-Male | +------------------------------+
不需要使用SQL查询来完成。我们还可以使用Laravel查询生成器方法来执行此操作。
在此先感谢您的帮助。
检查样本SQL Fiddle
这应该做。在mysql 5.7中将不起作用，因为直到后来才包含递归CTE，但这将使您每个group_id拥有可变数量的属性和attribute_values。小提琴在这里。
with recursive allAtts as ( /* Get our attribute list, and format it if we want; concat(a.title, ':', v.title) looks quite nice */ SELECT att.group_id, att.id, CONCAT(v.title) as attDesc, dense_rank() over (partition by att.group_id order by att.id) as attRank FROM table_attributes att INNER JOIN table_attribute_values v ON v.group_id = att.group_id AND v.attribute_id = att.id ), cte as ( /* Recursively build our attribute list, assuming ranks are sequential and we properly linked our group_ids */ select group_id, id, attDesc, attRank from allAtts WHERE attRank = 1 union all select allAtts.group_id, allAtts.id, concat_ws('-', cte.attDesc, allAtts.attDesc) as attDesc, allAtts.attRank from cte join allAtts ON allAtts.attRank = cte.attRank +1 AND cte.group_id = allAtts.group_id ) /* Our actual select statement, which RIGHT JOINs against the table_groups so we don't lose entries w/o attributes */ select grp.id, concat_ws('-', d.day, qty.quantity, cte.attDesc) as combinations from cte inner join (select group_id, max(attRank) as attID from cte group by group_id) m on cte.group_id = m.group_id and m.attID = cte.attrank RIGHT JOIN table_groups grp ON grp.id = cte.group_id LEFT JOIN table_days d on grp.id = d.group_id LEFT JOIN table_quantities qty on grp.id = qty.group_id;
