mysql - 如何动态选择列并在mysql中汇总所有大小

Question

我在使列动态化时遇到了一个问题，并且想要计算数量。我正在静态获取所有尺寸，但我想使其动态化。首先，我将分享我的代码:-

SELECT order_products.product_name,
max(CASE 
WHEN 
order_product_sizes.size = 'S' THEN  order_product_sizes.qty    
END) AS S,
max(CASE 
WHEN 
order_product_sizes.size = 'M' THEN  order_product_sizes.qty    
END) AS M,
max(CASE 
WHEN 
order_product_sizes.size = 'XL' THEN  order_product_sizes.qty    
END) AS XL,
max(CASE 
WHEN 
order_product_sizes.size = '2XL' THEN  order_product_sizes.qty    
END) AS '2XL',
max(CASE 
WHEN 
order_product_sizes.size = '3XL' THEN  order_product_sizes.qty    
END) AS '3XL',
max(CASE 
WHEN 
order_product_sizes.size = '4XL' THEN  order_product_sizes.qty    
END) AS '4XL',
from order_products join order_product_sizes
on order_product_sizes.order_product_id = order_products.id
group by order_product_sizes.order_product_id

我还想要总计列中所有尺寸的总和。提前致谢

Answer 1

您可以尝试以下 -

SET @sql = NULL;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(size = ''',
          size,
          ''', qty, NULL)) AS ',
          size
        )
      ) INTO @sql
    FROM order_products_sizes;

    SET @sql = CONCAT('SELECT order_products.product_name, ', @sql, ' ,sum(qty) as total
                       FROM order_products join order_products_sizes
    on order_products_sizes.order_product_id = order_products.id
    group by order_products_sizes.order_product_id');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;