MySQL使用group_concat选择多个关系表到一行

Question

我有 5 个表:productCategory、products、productOptions、options、optionsGroup。我想做的是以某种方式将它们合并并分组为一个查询，以返回包含有关一种产品的所有信息的一行。例如，某种食物会有不同的大小和不同的味道；瓶子有不同的尺寸和气味。以下是表格:

产品类别

categoryId    categoryName
1             food
2             freshener

产品

productId    productCategoryRef    productName
1            1                     ....
2            1
3            2
4            2
FK references categoryId

产品选项

categoryRef  optionsRef
1            1
1            3
2            2
2            4
FK categoryRef references productCategory (categoryId)
FK optionsRef references options (optionsId)

选项

optionsId    optionsName
1            kg
2            ml
3            Flavor
4            Fragrance

选项组

groupId    groupOptionRef  groupName
1          1               3
2          1               7
3          1               14
4          2               50
5          2               250
6          3               Chicken
7          3               Beef
8          4               Alpine
9          4               Berries
FK groupOptionRef references options (optionId)

所以一个食品袋可以是3公斤、7公斤或14公斤，并且可以有两种不同的口味。如何以可读的格式将所有这些信息集中在一行中？这是我尝试过的:

SELECT 
  pc.*, p.*, 
  GROUP_CONCAT(DISTINCT o.optionsName) AS optionName, 
  GROUP_CONCAT(og.groupName) AS options 
FROM productCategories pc 
  JOIN products p ON p.productCategoryId=pc.categoryId 
  JOIN productOptions po ON po.categoryRef=pc.categoryId 
  JOIN options o ON o.optionsId=po.optionsRef 
  JOIN optionsGroup og ON og.groupOptionRef=o.optionsId 
WHERE p.productName=:itemName 
  GROUP BY p.productId

这给了我一行

....
optionName: 'kg,Flavor',
options: '3,7,14,Chicken,Beef'
....

这真是一团糟。是否有可能以某种方式将每个选项连接在一起，然后更容易地进行后处理，而不必猜测哪个部分属于公斤/ flavor ？

我知道这很容易，但稍后会有一个评论部分，它将引用这个我也想加入到这个查询中的productId。欢迎任何建议，如果需要，我愿意接受更好的数据库结构。

Answer 1

如果您不介意选项名称重复，您可以像这样对 concat 进行 group_concat:

GROUP_CONCAT(CONCAT(o.optionsName, ': ', og.groupName) ORDER BY o.optionsName, og.groupName) AS options 

如果你想要比这更结构化的东西，你必须进行子查询(按选项名称分组，然后group_concating它的组名称；然后在外部查询中不使用选项名称进行分组)；但在这一点上，如果您应该只在客户端处理格式化和分组结果，那绝对值得考虑。