mysql - SQL - 如何显示产品的选项，无论它们是否有？

Question

假设 aproducts可以有 0 个或更多options- 使用下表实现：

products
 - id
 - name

options
 - id
 - name

product_options
 - id
 - product_id
 - option_id

进一步假设以下产品具有以下选项：

• 产品 1 = 选项 1、选项 2、选项 3
• 产品 2 = 选项 2、选项 3、选项 4
• 产品 3 = 选项 3、选项 4、选项 5

我怎样才能查询这个，以便我得到这样的结果：

• 产品 1，选项 1，选项 2，选项 3，NULL，NULL
• 产品 2，NULL，选项 2，选项 3，选项 4，NULL
• 产品 3，NULL，NULL，选项 3，选项 4，选项 5

我的选项实际上是一棵嵌套树。他们有一个类别表（也是一个嵌套树）的外键。最终，我需要能够执行此查询并按类别对结果进行分组。但是，我可能应该首先了解如何解决我的问题的这个更简单的版本。

更新1：我事先不知道选项可能是什么。此外，产品可能具有的选项数量没有限制。

标签： mysqlsqlpivot-tableentity-attribute-value

Answer 1

如果您有未知数量的选项，您可以使用存储过程来动态创建可用于透视表的查询。像这样的东西：

CREATE PROCEDURE display_options()
BEGIN
  SET @query = 'SELECT p.id, ';
  SET @query = CONCAT(@query, (SELECT GROUP_CONCAT(CONCAT('MAX(CASE WHEN o.name = ''', name, ''' THEN o.name END) AS `', name, '`')) FROM options ORDER BY id));
  SET @query = CONCAT_WS(' ', @query, 
                         'FROM products p',
                         'JOIN product_options po ON po.product_id = p.id',
                         'JOIN options o ON o.id = po.option_id',
                         'GROUP BY p.id');
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

此过程将生成这样的查询（与@GordonLinoff 对您问题中的示例数据的回答基本相同）：

SELECT p.name, 
       MAX(CASE WHEN o.name = 'Option 1' THEN o.name END) AS `Option 1`,
       MAX(CASE WHEN o.name = 'Option 2' THEN o.name END) AS `Option 2`,
       MAX(CASE WHEN o.name = 'Option 3' THEN o.name END) AS `Option 3`,
       MAX(CASE WHEN o.name = 'Option 4' THEN o.name END) AS `Option 4`,
       MAX(CASE WHEN o.name = 'Option 5' THEN o.name END) AS `Option 5` 
FROM products p 
JOIN product_options po ON po.product_id = p.id 
JOIN options o ON o.id = po.option_id 
GROUP BY p.name

然后可以准备并执行它以给出如下结果：

name        Option 1    Option 2    Option 3    Option 4    Option 5
Product 1   Option 1    Option 2    Option 3        
Product 2               Option 2    Option 3    Option 4    
Product 3                           Option 3    Option 4    Option 5

dbfiddle 上的演示